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:

1. Unzip the accarch145.zip file to a folder of your choice.


2. Copy the Contacts.mdb database to the default Office folder (in Office 2003, this is Microsoft OfficeOffice11).


3. 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.)


4. In the main Outlook window, select Tools|Macro|Security and set macro security to Low.


5. If you had to change the security level, close and reopen Outlook so the new security level will take effect.


6. Open the Outlook VBA project by selecting Tools|Macro|Visual Basic Editor (hot key:  Alt-F11) from the main Outlook window.


7. 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.


8. 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

9. Select Tools|References, and set a reference to the Access 11.0 object library (or whatever version of Access is running).


10. Close the Visual Basic window, and then Outlook; be sure to click Yes when asked if you want to save the VBA project.


11. 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:

1. 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.


2. Open one of the test contact items.


3. In the main Outlook window, select Tools|Macro|Macros, select the OpenAccessContact macro, and click Run.


4. 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.