Exporting Outlook 2007 Contacts to Access 2003
Summary: Learn how to export user-defined fields (properties) from Microsoft Office Outlook 2007 items to Office Access 2003. (4 printed pages)
Applies to: Microsoft Office Outlook 2007, Microsoft Office Access 2003
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)
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
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
On the File menu in Outlook, click Import and Export.
In the Import and Export Wizard dialog box, select Export to a file, and then click Next.
Select Microsoft Access, and then click Next.
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
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
tblContactsthat has the following fields:
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.
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
On the Tools menu, click Trust Center.
Click Macro Security.
Select Warnings for all macros.
To run the code sample as an Outlook macro
In Outlook 2007, click Alt+F11 to start the Visual Basic Editor.
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.
In the Project Explorer, expand the Project1 node.
Expand the Microsoft Office Outlook Objects node.
On the Tools menu, click References.
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.
Click OK to close the References dialog box.
To create the macro, copy and paste the preceding code sample into the Code window.
Press F5 to run the macro.
Close the Visual Basic Editor.
To reset macro security after running this sample
On the Tools menu, click Trust Center.
Click Macro Settings.
Select Warnings for signed macros; all unsigned macros are disabled.
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.