Exporting Outlook 2007 Contacts to Access 2003

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn how to export user-defined fields (properties) from Microsoft Office Outlook 2007 items to Office Access 2003. (4 printed pages)

Microsoft Corporation

February 2009

Applies to: Microsoft Office Outlook 2007, Microsoft Office Access 2003

Contents

• Overview

• Exporting Outlook Contacts to Access

• Running the Macro

• Conclusion

• Additional Resources

Overview

Your Microsoft Office Outlook 2007 Contacts folder contains information about all your contacts. By default, your Contacts folder includes, but is not limited to, the following properties for your contacts:

• Full name (including Title, First, Middle, Last, and Suffix)

• Company

• Job title

• File as

• E-mail

• Display as

• Web page address

• Phone numbers (including Business, Home, Fax, and Mobile)

• Addresses (including Business and Home)

You may want to prepare custom reports to analyze the contacts in your Contacts folder, or use the query interface of Microsoft Office Access 2003 to see particular information about your contacts. One solution is to create an Access database of your contacts to manage information about your contacts. Instead of entering the information manually, you can export your contacts information from your Outlook Contacts folder to Access. After you have exported your contacts to Access, you can use Access to manage your contacts (for example, to create forms to display or enter data). Because the Import and Export Wizard in Outlook does not allow you to import or export information in user-defined fields (or properties), you must create a programming solution to do so. This article describes how to write a macro to export all contact information from Outlook to Access.

The macro involves setting up Data Access Objects (DAOs), setting up Outlook objects, and then reading information from your Contacts folder into a table named tblContacts.

You can export default (not user-defined) items from Outlook to Access by using the following procedure.

To export default items from Outlook to Access

1. On the File menu in Outlook, click Import and Export.

2. In the Import and Export Wizard dialog box, select Export to a file, and then click Next.

3. Select Microsoft Access, and then click Next.

4. Follow the remaining steps in the Import and Export Wizard.

In a few clicks, you can import or link your contracts from any Outlook folder into an Access data table.

Exporting Outlook Contacts to Access

The following Microsoft Visual Basic for Applications (VBA) code sample exports current Outlook contact information into an Access table. The Access table will contain fields of contact properties that match the default Outlook properties, as well as user-defined properties such as EyeColor and Height. The code sample makes the following assumptions:

• You have set a reference to the Microsoft Outlook 12.0 Object Library and the Microsoft DAO 3.6 Object Library.

• Keys and subkeys will not be defined in the new Access table.

• The field types are text, in both Access and Outlook.

• The Access fields are set to Allow Zero-Length Values.

• Error-checking code that is necessary for a production application is not provided.

• You have created a table named tblContacts that has the following fields:

  • FirstName

  • LastName

  • Address

  • City

  • State

  • Zip_Code

Sub ImportContactsFromOutlook()

    ' This code is based in Microsoft Access.

    ' Set up DAO objects (by using the existing "tblContacts" table).
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("tblContacts")


    ' 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

    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
                rst!FirstName = c.FirstName
                rst!LastName = c.LastName
                rst!Address = c.BusinessAddressStreet
                rst!City = c.BusinessAddressCity
                rst!State = c.BusinessAddressState
                rst!Zip_Code = c.BusinessAddressPostalCode

    ' Custom Outlook properties would look like this:
    ' rst!AccessFieldName = c.UserProperties("OutlookPropertyName")
    ' For example, a custom field “EyeColor” would be entered as: 
    ' rst!AccessEyeColor = c.UserProperties("EyeColor")

                rst.Update
            End If
        Next i
        rst.Close
        MsgBox "Finished."
    Else
        MsgBox "No contacts to export."
    End If

End Sub

Running the Macro

The default macro security setting is Warnings for signed macros; all unsigned macros are disabled. This setting allows a macro to run without any security prompts if the macro is digitally signed by a trusted publisher. All unsigned macros are disabled without notification. Therefore, unless your code sample is signed by a trusted publisher, you will not be able to run the code sample with this security setting. For more information on trusted publishers, see Add, remove, or view a trusted publisher.

The code sample in this article is not digitally signed. Therefore, you should set macro security to the Warnings for all macros option to run just this code sample. After changing to this option, restart Outlook. The Warnings for all macros option initially disables all macros. The first time you attempt to run a macro or start the Visual Basic Editor, you will see the Microsoft Office Outlook Security Notice dialog box. Click Enable Macros.

Important
Because the Warnings for signed macros; all unsigned macros are disabled security setting provides a higher level of security for Outlook than the Warnings for all macros setting, you should set your macro security to Warnings for signed macros; all unsigned macros are disabled after you run this code sample. Restart Outlook for this setting to take effect.

To change the macro security setting

1. On the Tools menu, click Trust Center.

2. Click Macro Security.

3. Select Warnings for all macros.

4. Click OK.

5. Restart Outlook.

To run the code sample as an Outlook macro

1. In Outlook 2007, click Alt+F11 to start the Visual Basic Editor.

2. If this is the first time you have started the Visual Basic Editor in the current Outlook session, you will see the Microsoft Office Outlook Security Notice dialog box. Click Enable Macros.

3. In the Project Explorer, expand the Project1 node.

4. Expand the Microsoft Office Outlook Objects node.

5. Double-click ThisOutlookSession.

6. On the Tools menu, click References.

7. In the References dialog box, ensure that Microsoft DAO 3.6 Object Library, Microsoft Outlook 12.0 Object Library, and Visual Basic for Applications are selected.

8. Click OK to close the References dialog box.

9. To create the macro, copy and paste the preceding code sample into the Code window.

10. Press F5 to run the macro.

11. Close the Visual Basic Editor.

To reset macro security after running this sample

1. On the Tools menu, click Trust Center.

2. Click Macro Settings.

3. Select Warnings for signed macros; all unsigned macros are disabled.

4. Click OK.

5. Restart Outlook.

Conclusion

This macro offers a starting point to use the Microsoft Visual Basic Editor to import or export user-defined properties in Outlook items. You can use this code, with modifications, as a basis for importing or exporting other types of user-defined properties in Outlook items, such as appointments, notes, tasks, and so on.

Additional Resources

For more information, see the following additional resources.

• Outlook Developer Portal

• Office Developer Center