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