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