Software

How do I... Transfer Access data to an Outlook contact?


How you use data often determines where you store it, which can create a bit of trouble down the line. For instance, you might store contact information in an Access database. If you want the same data available in Outlook, you could find yourself entering it again.Fortunately, such double entry isn't necessary. In fact, many custom solutions transfer data from Outlook to Access and vice versa. Sharing data reduces data entry and the opportunities for typos and other erroneous values. Let's look at how to manually transfer the data. Then, we'll see how to do it automatically. Note: Our example works with Access 2003 and Outlook 2003.

Note: This information is also available as a PDF download.

Transfer manually

Both Outlook and Access have built-in features for sharing data. To import Outlook data into Access, do the following:

  1. Choose Get External Data from the Access File menu.
  2. Select Import.
  3. Specify Outlook in the Files Of Type control.
  4. Click import.

You can also create a link to an Outlook contact, although Outlook must be the system's default mail client. Temporarily changing the default is an option, if you know you'll remember, but that solution is impractical.

To export contacts from Outlook into an Access database, do the following:

  1. Open the Contacts window in Outlook.
  2. Choose Import And Export from the File menu to launch the Export Wizard.
  3. Select Export To A File and click Next.
  4. Choose Microsoft Access and click Next.
  5. Enter a path and filename (or use Browse) and click Next.

The problem with the export route is that you can't append the contact information to an existing database. You can only replace it or create a new one.

Where to get references and field names

To programmatically send Access data to an Outlook contact folder, you must work your way through Outlook's object model and set the appropriate references. There's a good illustration online at MSDN.

The NameSpace object represents a root object for referencing the actual data source. In this case, the MAPIFolder object represents an Outlook folder, which can contain MAPIFolder objects or Outlook Item objects.

To access the right folder, use the following intrinsic constants: olFolderContacts, olFolderCalendar, olFolderDeletedItems, olFolderDrafts, olFolderInbox, olFolderJournal, olFolderNotes, olFolderOutbox, olFolderSentMail, and olFolderTasks. In this case, you need the Contact folder's intrinsic constant, olFolderContacts. That folder contains names, phone numbers, home addresses, and so on.

Each folder contains a number of Item objects. When using code, you must use the predefined Item name. You can see a list in Outlook's Contacts form. Click the All Fields tab and then choose the appropriate category to view predefined Item names, as shown in Figure A. Not every field name exactly matches the object's predefined name, so be careful. (Remove the space characters.)
Figure A: Get predefined names from Outlook.

Automating the transfer

You need three things to automate the process of transferring contact data from Access to Outlook:

  • Contact data to transfer
  • A user interface method for initiating the transfer
  • Code that actually transfers the data
Figure B shows a simple contact table named Contacts. The example includes only a few fields, and each field corresponds to a predefined Contact item in Outlook:
  • ContactID -- AutoNumber field
  • LastName -- Text field
  • FirstName -- Text field
  • Department -- Text field
  • Birthday -- Date/Time field
  • Transferred --Yes/No field
Figure B: You can transfer Access data to an Outlook contact.

The simple form in Figure C offers a convenient way to transfer the data. With a real-world database, you'll probably use a more purposeful form. For example, you might allow users to select specific contacts, or the form might ask for confirmation before actually transferring the data. To keep the code focused on just the transferring task, this form does neither.
Figure C: Use this simple form to transfer contact data to Outlook.

Open a blank form and insert a command button. Name the button cmdTransfer and enter the following event procedure into the form's module:

Private Sub cmdTransfer_Click()

Call TransferContacts

End Sub

(In Design view, click the Code button to launch the form's module.) Don't worry that TransferContacts() doesn't exist yet. Save the form.

Now you're ready to add TransferContacts(), the user-defined function that uses Access data to create new contacts in Outlook. In the Visual Basic Editor (VBE), choose Module from the Insert menu to open a blank module. Then, enter the code in Listing A and save the module.
Listing A
Function TransferContacts()

'Transfer contact records from Contacts to Outlook.

Dim rst As New ADODB.Recordset

Dim cnn As New ADODB.Connection

Dim appOutlook As New Outlook.Application

Dim ns As Outlook.NameSpace

Dim fldContacts As Outlook.MAPIFolder

Dim itms As Outlook.Items

Dim objContactFolder As Object

On Error GoTo ErrHandler

Set cnn = CurrentProject.Connection

Set appOutlook = CreateObject("Outlook.Application")

Set ns = appOutlook.GetNamespace("MAPI")

Set fldContacts = ns.GetDefaultFolder(olFolderContacts)

Set itms = fldContacts.Items

'Prevent duplicate contacts in Outlook.

rst.Open "SELECT * FROM Contacts WHERE Transferred = 0", _

cnn, adOpenKeyset, adLockOptimistic

'Prevent error when recordset is empty, meaning there are no

'new contact records to transfer.

If rst.RecordCount = 0 Then

MsgBox "There are no new contact records to transfer", _

vbOKOnly, "Transfer stopped"

Exit Function

End If

rst.MoveFirst

Do While Not rst.EOF

Set objContactFolder = itms.Add("IPM.Contact")

With objContactFolder

.CustomerID = Nz(rst!ContactID)

.FirstName = Nz(rst!FirstName)

.LastName = Nz(rst!LastName)

.Department = Nz(rst!Department)

.Birthday = Nz(rst!Birthday)

.Close olSave

End With

Set objContactFolder = Nothing

rst.Update "Transferred", -1

rst.MoveNext

Loop

Set cnn = Nothing

Set appOutlook = Nothing

Set ns = Nothing

Set fldContacts = Nothing

Set itms = Nothing

Exit Function

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, _

vbOKOnly, "Error"

End Function

While in the VBE, be sure to reference the Outlook object library as follows:

1. Choose References from the Tools menu.

2. Check the Microsoft Outlook 11.0 Object Library, as shown in Figure D.

3. Click OK.

Figure D: Be sure to reference the Outlook object library inside Access.

Putting it to work

When you're ready to transfer Access data to Outlook, simply click the Transfer Contacts To Outlook button in the example form. It won't matter if Outlook is open or closed.

First, the code sets the appropriate references and populates a Recordset object with values from the Contacts table. To prevent duplicate contacts in Outlook, the WHERE Transferred = 0 clause retrieves only those records that haven't been previously transferred.

You could check each Access contact against Outlook's existing contacts, but it's simpler to track transferred records in Access. Without this check, the code could create duplicate contact records in Outlook. However, it won't prevent duplicates if a user enters the same contact information manually in Outlook. If the SELECT statement retrieves no records, the code displays the message shown in Figure E and quits.
Figure E: Sometimes, there are no records.

When there are records to transfer, the With block transfers those values to the appropriate Contacts folder, which in this case is IPM.Contact (the default). If you have custom folders, you'll want to update the folder name. You can quickly get folder names by right-clicking the Contact folder in the Folders window, choosing Properties, and viewing the When Posting To This Folder item on the General tab. The folder name you need is not the name you see at the top of this tab.

Notice that the recordset field names match the predefined Outlook names. That won't always be the case, but it does help in documenting the transfer. If you have time to plan, use Outlook's predefined names as your field names in Access. In addition, be sure to account for Null values to avoid errors from Outlook. The simplest solution is to use the Nz() function. After creating the new contact in Outlook, the Update method flags each record in the recordset as transferred (-1). The next time you transfer data, the SELECT statement won't retrieve those records.

As is, there's no indication to the user that records successfully transferred. You might want to display an informational message just so your users will know the transfer was successful. To include the number of records transferred, use the Record object's RecordCount property. In addition, the function includes very little error handling. Test the function thoroughly and account for all possible errors when applying this code to your own work.

Of special interest is the way the code transfers the contacts' ContactID value, which is the result of an Access AutoNumber data type. When creating new contacts in Outlook this way, it's a good idea to control the value Outlook uses to identify the new contact. That way, you can more easily synchronize the new contacts with your Access database.

When using this solution, remember that it doesn't create a permanent link between Access and Outlook. If a user updates a contact in Access or Outlook, neither application will update the other.

Send 'em over!

Knowing the object model of any Office application is the single most important issue when automating data transfer. When transferring data from Access to Outlook, you must know Outlook's object model. In this case, you must reference the MAPIFolder object and identify the receiving folder. The second most critical issue is matching Access field names to a corresponding item. The example code contains the critical references and points you in the right direction. All you have to do is customize it to your working environment.


Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
fabio.fantuzzi
fabio.fantuzzi

What about the same function but from 2002 versions of Access and Outlook?

natalie
natalie

Would this work to update existing contacts in Outlook if they've been changed in Access?

ssharkins
ssharkins

Some of the code you'll need is there, but you'll have to find the appropriate record first. That really can be a challenge.