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/showthread.php?24278-Solved-Import-Contacts-from-Microsoft-Outlook&highlight=outlook+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/showthread.php?20958-outlook-gt-Access-vba-question&highlight=export+Outlook+Contacts+table+to+Access

I also found this for Access

https://stackoverflow.com/questions/1492374/create-an-external-table-in-access-with-outlook-contacts-programmatically

You could also try this one

https://www.experts-exchange.com/questions/28407117/ms-access-macro-vba-to-update-contacts-table-from-ms-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:


You need to know the required field names in Outlook Contacts - I found them here: https://msdn.microsoft.com/en-us/vba/outlook-vba/articles/outlook-fields-and-equivalent-properties
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