CODEWITHSUNDEEP
excelvbaoutlooklate-binding
slightly drifting
slightly drifting

Reputation: 332

Excel to Outlook Contact, Object doesn't support this property or method

This code deletes all contacts in a subfolder then shoots an updated sheet from excel into the proper contact input values for outlook.

I am getting the error: RUN TIME ERROR 438 - OBJECT DOESN'T SUPPORT THIS PROPERTY OR METHOD at the line:

.FullName = Range("D" & i).Value

So I am obviously not doing this right. Am I using the wrong operation? Am I not referencing an object to the correct library? I can see the values from excel loading into each item, it is just not going into outlook. Where am I going wrong?

This is using late-binding because of multiple outlook versions, so referencing object libraries are not an option. 

Sub XL2OLContacts()
    Dim olApp As Object 'using late binding to ensure compatibility for all office versions
    Dim olItem As Object
    Dim olFolder As Object
    Dim olConItems As Object


    Set olApp = CreateObject("Outlook.Application") 'opens outlook
    Set olNamespace = olApp.GetNamespace("MAPI") 'setting MAPI location for contacts
    Set activefolder = olNamespace.Folders 'making default user contacts active folder



    n = 1 'counter starting
    Do Until activefolder.Item(n) = (Environ$("Username")) & "@###.com" 'this says USERNAME@###.com will be the default user profile for contact location
        n = n + 1
    Loop

    Set myfolder = activefolder.Item(n) 'default folder active
    Set myfolder2 = myfolder.Folders("Contacts").Folders("Call Observation List") 'setting contacts subfolder to var now

    Do
        For Each ContactItem In myfolder2.Items
            ContactItem.Delete
        Next ContactItem
    Loop Until myfolder2.Items.Count = 0 'otherwise it would only delete a handful each time it ran for some reason

    n = 1
    Do Until activefolder.Item(n) = (Environ$("Username")) & "@###.com"
        n = n + 1
    Loop

    Set myfolder = activefolder.Item(n)
    Set myfolder2 = myfolder.Folders("Contacts").Folders("Call Observation List")

    lastrow = Sheets("CSV Page").Range("A" & Sheets("CSV Page").Rows.Count).End(xlUp).Row


    For i = 1 To lastrow
        Sheets("CSV Page").Activate
            If ActiveSheet.Range("C" & i).Value = "" Then
            Set olConItem = olApp.CreateItem(olContactItem)
            With olConItem
                .FullName = Range("D" & i).Value
                .EmailAddress = Range("F" & i).Value
                .HomePhone = Range("L" & i).Value
                .MobilePhone = Range("N" & i).Value
                .JobTitle = Range("Z" & i).Value
                .Notes = Range("AC" & i).Value
                .Save
            End With
        End If
        Application.StatusBar = "Updating Contacts: " & Format(i / lastrow, "Percent") & " Complete"
    Next i
End Sub

Upvotes: 0

Views: 1620

Answers (1)

Rory
Rory

Reputation: 34035

If you late bind your code, the constants from the Outlook library, like olContactItem are not defined. Since you don't have Option Explicit at the top of your code, VBA assumes you want to create a new variable called olContactItem, with a default initial value of 0.

This means that you are actually creating a MailItem since that's what olApp.CreateItem(0) would do. Add this line to the start of the code:

Const olContactItem as Long = 2

Upvotes: 2

Related Questions