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.
Both Outlook and Access have built-in features for sharing data. To import Outlook data into Access, do the following:
- Choose Get External Data from the Access File menu.
- Select Import.
- Specify Outlook in the Files Of Type control.
- 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:
- Open the Contacts window in Outlook.
- Choose Import And Export from the File menu to launch the Export Wizard.
- Select Export To A File and click Next.
- Choose Microsoft Access and click Next.
- 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
- 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 TransferContactsEnd 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.
'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"
Do While Not rst.EOF
Set objContactFolder = itms.Add("IPM.Contact")
.CustomerID = Nz(rst!ContactID)
.FirstName = Nz(rst!FirstName)
.LastName = Nz(rst!LastName)
.Department = Nz(rst!Department)
.Birthday = Nz(rst!Birthday)
Set objContactFolder = Nothing
rst.Update "Transferred", -1
Set cnn = Nothing
Set appOutlook = Nothing
Set ns = Nothing
Set fldContacts = Nothing
Set itms = Nothing
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 email@example.com.
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.