VBA to export Contacts to Access

[MartinGM]

As has happened to many others, I cannot get the linking of an Access table to Outlook Contacts to work - Office 2010. I've have tried dozens of workarounds, to no avail.

This isn't a big deal as I can export the Contacts manually from Outlookk, albeit slightly tediously:

File . . . Options . . . Advanced . . . Export . . . Export to a file . . . choose Microsoft Access 97-2003 (that's the only Access option) . . . Select Contacts . . . specify the file/location . . . ignore the warning that the file already exists . . . Finish.

Outlook chooses to name the resulting Access table "Contacts", which is what I want.

I would, though, like to write a Macro to do this . . . the steps are precisely the same every time I do it, and I'd have fewer errors !

I know Excel VBA very well but this willl be my first venture into Outlook VBA and I don't know where to start (as there is no Macro recorder to give me clues).

Simply put, I would like to write code to Export my Outlook Contacts as an Access table to a specific file in a specific location.

Any help or pointers gratefully received.

Martin

[OBP]

Some time back I answered getting Contacts from Outlook here
http://www.vbaexpress.com/forum/show...tlook+contacts

[MartinGM]

Thanks for that - sadly the link therein is dead, but I think I know what it said.

As I said, linking doesn't work for me - I've tried really hard (at least 12 workarounds !) to do that but it alwys comes up with an unspecific error. And anyway because of a multple location issue I do actually need to export from Outlook to an Access table: I can then synchronise the table between loctions.

I was hoping to get some advice on how to write the code, please.

Thanks

Martin

[OBP]

Sorry I am not familiar with Outlook VBA code.
But I used the Forum's Advanced Search and found this one

http://www.vbaexpress.com/forum/show...able+to+Access

I also found this for Access

https://stackoverflow.com/questions/...ogrammatically

You could also try this one

https://www.experts-exchange.com/que...s-outlook.html

[MartinGM]

Thank you.
I am getting on quite well with this - the usual few snags !
I shall continue this thread in the Access forum as writing the Import code in Access is much easier than writing the Export code in Outlook !

[MartinGM]

Finally solved.

Thanks for the links, which led me to a solution which I have tweaked and have copied below in case anyone else needs to do this. Everything is simple . . . if you know how !

It was far easier to write the code in Access than in Outlook. Below is the Access Module which does the importing - I hope it is self-explanatory:

Two other things are needed:

1. You need to know the required field names in Outlook Contacts - I found them here: https://msdn.microsoft.com/en-us/vba...ent-properties
2. In the VBA window you need to add a reference: Tools . . . References . . . Microsoft Outlook <version number> Object Library

Sub ImportContactsFromOutlook()

'To import selected fields from Contacts in Outlook and pllace in Access table "Contacts"

'Set up DAO objects (uses existing "Contacts" table)
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Contacts")

' Set up Outlook objects.
    Dim ol As New Outlook.Application
    Dim olns As Outlook.Namespace
    Dim cf As Outlook.MAPIFolder
    Dim c As Outlook.ContactItem
    Dim objItems As Outlook.Items
    Dim Prop As Outlook.UserProperty
   
'Clear the existing Contacts table                                     'Otherwise the imported records are appended to the existing records
    strTableName = "Contacts"                                         'Name the Table to be cleared
    DoCmd.SetWarnings False                                          'Stop warnings about deletions
    DoCmd.RunSQL "DELETE *.* FROM " & strTableName   'Do the deletion
    DoCmd.SetWarnings True                                           'Turn warnings on again
    DoCmd.Close acTable, "Contacts", acSaveYes               'Close the cleared Table

'Import the required fields froom Outlook

    Set olns = ol.GetNamespace("MAPI")
    Set cf = olns.GetDefaultFolder(olFolderContacts)
    Set objItems = cf.Items
    iNumContacts = objItems.Count
    If iNumContacts <> 0 Then
    For i = 1 To iNumContacts
        If TypeName(objItems(i)) = "ContactItem" Then
            Set c = objItems(i)
            rst.AddNew
                ' Custom Outlook properties look like this:
                ' rst!AccessFieldName = c.UserProperties("OutlookPropertyName")
            rst!FirstName = c.FirstName
            rst!LastName = c.LastName
            rst!HomeStreet = c.HomeAddressStreet
            rst!HomeCity = c.HomeAddressCity
            rst!HomeState = c.HomeAddressState
            rst!HomePostalCode = c.HomeAddressPostalCode
            rst!HomeCountryRegion = c.HomeAddressCountry
            rst!Categories = c.Categories
            rst!HomePhone = c.HomeTelephoneNumber
            rst!MobilePhone = c.MobileTelephoneNumber
            rst.Update
        End If
      Next i

      rst.Close
     
DoCmd.Close acTable, "Contacts", acSaveYes         'Close the populated Table
     
   Else
    MsgBox "No contacts to export."
   End If

End Sub