Matching Outlook and Access Contacts
How to synchronize Access and Outlook contact data.
Access Archon #145
Introduction
If you store contact data both in an Access table and an Outlook Contacts folder, it would be handy to be able to quickly switch from the Access contact record to the Outlook contact item, or vice versa. In this article I show how to do this, using VBA procedures running from buttons.
Synchronizing Access and Outlook Contact Data
Ideally, Office would have built-in tools for doing Access-Outlook contact synchronizing, but anyone who has tried linking an Outlook Contacts folder to an Access table knows how problematic this is. To see if there might have been some improvement since I last tested this functionality (in Office XP), I tested in Office 2003 by linking to my Outlook Contacts folder, using File|Get External Data|Link Tables, selecting the Outlook file type, and then the default Contacts folder. The linked table contains many (but not all) of the built-in ContactItem fields. Unfortunately, it lacks the ID fields that you would probably want to use as unique IDs for identifying contacts, such as Customer ID, Government ID Number, and Organizational ID Number.
Perhaps you could live with that, as long as you could update contacts in either Access or Outlook, and depend on the changes appearing in the other application. But this is not the case; I edited a contact’s name in the Access table, then switched to Outlook, and found that the name had not been changed. When I edited a name in Outlook, however, the change was reflected in the Access table.
Because of the lack of some important built-in fields (not to mention any custom fields you may have added to a custom Outlook contact form), and the failure to carry over changes in the Access table to the matching Outlook contacts, linking to an Outlook Contacts folder still doesn’t do the job of two-way synchronizing. (I hope to address this need in a future add-in.) But at least, it is helpful to be able to quickly jump from the Access record to the matching Outlook contact (or vice versa), in order to do any needed changes manually.
The sample database, Contacts.mdb, has a contacts table (tblContacts), and a form bound to it, frmContacts. For purposes of matching contacts, the Access ContactID field matches the Outlook Customer ID field, a built-in field that is not displayed in a control on the standard Outlook contact form, but can be seen and modified on the All Fields tab, with All Contact Fields selected. I use this field for linking in order to support standard contact items; you could display this field (or another field used for linking to Access records) in a control on a custom Outlook contact form.
Figure A. The Customer ID field on a standard Contact item
To test quick contact switching, follow the steps below:
- Unzip the accarch145.zip file to a folder of your choice.
- Copy the Contacts.mdb database to the default Office folder (in Office 2003, this is Microsoft OfficeOffice11).
- Open the Contacts database and run the mcrCreateTestContacts macro to create a set of test contacts in the default local Contacts folder. (These contacts have the category “Test Contacts”, to make them easy to delete later on.)
- In the main Outlook window, select Tools|Macro|Security and set macro security to Low.
- If you had to change the security level, close and reopen Outlook so the new security level will take effect.
- Open the Outlook VBA project by selecting Tools|Macro|Visual Basic Editor (hot key: Alt-F11) from the main Outlook window.
- In the Visual Basic window, select File|Import File, browse to the folder where you unzipped the accarch145.zip file, select the Contacts.bas file and click Open.
- There will now be a Contacts folder under the Modules folder in the project tree, as shown in Figure B.
Figure B. The Contacts module in the Outlook VBA project tree
- Select Tools|References, and set a reference to the Access 11.0 object library (or whatever version of Access is running).
- Close the Visual Basic window, and then Outlook; be sure to click Yes when asked if you want to save the VBA project.
- Reopen Outlook
To open a matching Outlook contact from Access, first select a record on frmContacts, then click the Open Outlook Contact button to open the Outlook contact whose Customer ID matches the Access contact’s Contact ID. Figure C shows the Access contact record, and Figure D shows the matching Outlook contact item.
Figure C. An Access contact record with a button to open the matching Outlook contact item
Figure D. An Outlook contact item that matches an Access contact record
To open the matching Access contact record from an Outlook contact, follow the steps below:
- If you have not already done so, create a set of contacts that match the records in tblContacts by running the mcrCreateTestContacts macro in the Contacts.mdb datatase.
- Open one of the test contact items.
- In the main Outlook window, select Tools|Macro|Macros, select the OpenAccessContact macro, and click Run.
- The Contacts database opens to frmContacts, with the matching record selected.
Access VBA Code (frmContacts)
Private Sub cmdOutlookContact_Click()
On Error GoTo ErrorHandler
Dim appOutlook As Outlook.Application
Dim nms As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder
Dim itm As Object
Dim con As Outlook.ContactItem
Dim lngContactID As Long
lngContactID = Nz(Me![ContactID])
Set appOutlook = GetObject(, “Outlook.Application”)
Set nms = appOutlook.GetNamespace(“MAPI”)
Set fld = nms.GetDefaultFolder(olFolderContacts)
Set con = fld.Items.Find(“[CustomerID] = ” & lngContactID)
If Not TypeName(con) = “Nothing” Then
con.Display
Else
MsgBox “Contact with Customer ID = ” & lngContactID & ” not found”
End If
ErrorHandlerExit:
Set appOutlook = Nothing
Exit Sub
ErrorHandler:
‘Outlook is not running; open Outlook with CreateObject
If Err.Number = 429 Then
Set appOutlook = CreateObject(“Outlook.Application”)
Resume Next
Else
MsgBox “Error No: ” & Err.Number & “; Description: “
Resume ErrorHandlerExit
End If
End Sub
Outlook VBA Code
Public Sub OpenAccessContact()
On Error GoTo ErrorHandler
Dim lngContactID As Long
Dim ins As Outlook.Inspector
Dim appAccess As Access.Application
Dim dbe As DAO.DBEngine
Dim strPrompt As String
Dim strAccessPath As String
Dim strDBName As String
Set ins = Application.ActiveInspector
Debug.Print “Current item class: ” & ins.CurrentItem.Class
If ins.CurrentItem.Class olContact Then
‘Current item is not a contact item
strPrompt = “No contact item open; canceling”
MsgBox strPrompt, vbExclamation
GoTo ErrorHandlerExit
End If
lngContactID = CLng(ins.CurrentItem.CustomerID)
‘Pick up path to Access database directory from Access SysCmd function
Set appAccess = Application.CreateObject(“Access.Application”)
strAccessPath = appAccess.SysCmd(acSysCmdAccessDir)
Debug.Print “Access path: ” & strAccessPath
Set dbe = Application.CreateObject(“DAO.DBEngine.36”)
strDBName = strAccessPath & “Contacts.mdb”
Debug.Print “DBName: ” & strDBName
appAccess.OpenCurrentDatabase strDBName
appAccess.Visible = True
‘Open form to specific contact
appAccess.DoCmd.OpenForm FormName:=”frmContacts”, _
wherecondition:=”[ContactID] = ” & lngContactID
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
References
The code in the sample database needs the following references (in addition to the default references):
Microsoft DAO 3.6 Object Library
Microsoft Scripting Runtime
Microsoft Outlook 11.0 Object Library
The code in the Outlook VBA project needs the following reference:
Microsoft Access 11.0 Object Library
If you import code or objects into a database of your own, you may need to set one or more of these references. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch145.zip, which is the last entry in the table of Access Archon columns for Access Watch.
Document Name | Document Type | Place in |
Contacts.mdb | Access 2000 database (can also be used in higher versions of Access) | Wherever you want |
Contacts.bas | Saved VBA module | Import into Outlook VBA project |