Accessing Microsoft Exchange and Outlook Data Using Visual Basic

Microsoft Corporation

February 1997

Summary: Do you want to use Outlook® data in your Microsoft® Access applications? This article tells you how to use Data Access Objects (DAO) with Microsoft Visual Basic® for Applications in Access to link or import Microsoft Exchange or Microsoft Outlook data from message folders, public folders, address books, and other items. (17 printed pages)

Contents

Introduction
Installing the Microsoft Jet Exchange Installable ISAM Driver
Using DAO to Open, Link, and Import Microsoft Exchange and Outlook Data
Opening Microsoft Exchange or Outlook Folders
Linking Microsoft Exchange or Outlook Folders
Importing Microsoft Exchange or Outlook Folders
Adding Records
Microsoft Exchange and Outlook Table Formats

Introduction

To use Data Access Objects (DAO) methods and objects with Visual Basic for Applications in Access to link or import data from Exchange or the Outlook desktop information manager, you must have the Microsoft Jet Exchange installable indexed sequential access method (ISAM) (Msexch35.dll) installed. The Jet Exchange installable ISAM is a driver that allows you to access data that is stored remotely on a Microsoft Exchange Server or that is stored locally in offline folder (.ost), personal folder (.pst), or personal address book (.pab) files.

Note   If you have set up Outlook to use an offline folder file, the offline folder file is a file stored on your hard disk that "mirrors" the folder structure and data stored on your Microsoft Exchange Server. Data from your Microsoft Exchange Server is copied into the offline folder file whenever you synchronize folders (manually or at a scheduled time). When you start Outlook, it gives you the option to connect to your server or work offline. If you choose to connect, you are viewing data directly from the server; if you choose to work offline, you are viewing data from the offline folder.

For this reason, how you are logged on to Outlook determines whether the Microsoft Jet Exchange installable ISAM reads data from the offline folder file or from the Microsoft Exchange Server. For example, if you initially define a link to your Inbox folder while you are working offline in Outlook, the Microsoft Jet Exchange installable ISAM reads the data from your offline folder file. However, if you open the same database later while Outlook is connected to your Microsoft Exchange Server, the data will be read from the Inbox folder on the server.

Using the Microsoft Jet Exchange installable ISAM to access Exchange and Outlook data is primarily useful for reading information from message folders, public folders, address books, and other Outlook items. You can't modify existing items. And, though it is possible to write new items to Exchange and Outlook message stores, not all fields are available or updatable, which limits your ability to create some items. For more information on these issues, see the "Constraints" section later in this article. If you need more flexibility when working with Outlook objects from Microsoft Access, you should instead use Automation interfaces to work with Outlook. For more information, see www.microsoft.com/outlookdev/default.htm.

Installing the Microsoft Jet Exchange Installable ISAM Driver

Before you can use DAO to gain access to Microsoft Exchange Client or Outlook data, you must install the Microsoft Jet Exchange installable ISAM. Although you can install the Microsoft Jet Exchange installable ISAM using the Dataacc.exe file that is included in the Microsoft Office 97 Value Pack, that version of the driver does not register itself and has certain limitations. To install the most current version of the driver, download and run Wzmapi80.exe to install the Microsoft Exchange and Outlook Wizard. This installs and registers the most current version of Msexch35.dll so you can use it from DAO, and also installs the Microsoft Exchange and Outlook Wizard so you can import and link Microsoft Exchange and Outlook data using the Microsoft Access user interface.

Note   You must have a copy of Microsoft Access 97 installed before you can install the Microsoft Exchange and Outlook Wizard. The Setup program does not provide a way to install just the Microsoft Jet Exchange driver; you must install the wizard components as well.

Using DAO to Open, Link, and Import Microsoft Exchange and Outlook Data

The procedures for importing and linking Microsoft Exchange and Outlook data with DAO are similar to the procedures used for importing and linking the other external data sources, such as Microsoft FoxPro® and Microsoft Excel data. Special considerations and examples are provided in the following sections.

Connection Information

When specifying the connection string for Microsoft Exchange and Outlook data, use the following parameters separated by semicolons.

Connecting to a Microsoft Exchange server folder

To gain access to messages in a Microsoft Exchange Client or Outlook folder, you must specify the path to the folder just above the folder you want use. You do this with the MAPILEVEL= connection string keyword. For example, to gain access to a folder named Barbara in the Important subfolder in the People folder in the mailbox Mailbox - Dave Jones, first specify the path to the Important folder, using the following connection string:

"Exchange 4.0;MAPILEVEL=Mailbox - Dave Jones|People\Important;"

Then use the SourceTableName property in your DAO code to specify the folder named Barbara:

tdf.SourceTableName = "Barbara"

You can specify any valid mailbox name to the left of the pipe symbol ( | ) in the connection string, but the mailbox name must be specified exactly as it appears in the left pane of the Microsoft Exchange Client or Outlook application window. Spaces and capitalization in the name must be preserved.

Connecting to a personal folder

In addition to specifying a Microsoft Exchange Server mailbox name to the left of the pipe symbol ( | ) in the connection string, you can specify the name of a local personal folder file (if you are using one) exactly as it appears in the left pane of the Microsoft Exchange Client or Outlook application window. Note that this name is not necessarily the same name as the .pst file itself and does not include the file extension. To determine how a personal folder name is mapped to the .pst file name where its information is stored, click Services on the Tools menu in the Microsoft Exchange Client or Outlook to display the Services dialog box. On the Services tab, click the name of a personal folder, and then click Properties. In the Personal Folders dialog box, the information in the Name box is the name to specify in your connection string. The information in the Path box specifies the path to the .pst file where mail data is stored.

For example, to gain access to a folder named ToDo in the Inbox folder in the personal folder named Local Folders, use the following connection string:

"Exchange 4.0;MAPILEVEL=Local Folders|Inbox;"

And then use the SourceTableName property in your DAO code to specify the folder named ToDo:

tdf.SourceTableName = "ToDo"

Supplying a profile name and password

If you have defined multiple profiles for your Microsoft Exchange or Outlook installation, or have defined a password that is required to use your profile, you can supply the profile name and password with the PROFILE= and PWD= keywords in the connection string. For example:

"Exchange 4.0;MAPILEVEL=Mailbox - _
    Dave Jones|People\Important;PROFILE=DaveJ;PWD=Cisco;"

To determine the names of profiles on a computer, double-click Mail And Fax in the Windows Control Panel, and then click Show Profiles. If you don't specify a profile name in your connection string, the default profile (displayed in the When Starting Microsoft Windows Messaging, Use This box) is used.

If you don't specify a password in your code, and your network doesn't use your network logon password for this purpose, you will be prompted to enter your password when connecting to your Exchange data.

Specifying the DATABASE= parameter

When specifying the connection string to open a folder in a Microsoft Exchange Client or Outlook mailbox, you must supply a "hard-coded" path and name of an existing Microsoft Access database using the DATABASE= parameter:

"DATABASE="C:\My Documents\Outlook Data.mdb;"

Or use the Name property to refer to the current database in code:

"DATABASE=" & dbs.Name & ";"

The Microsoft Messaging driver creates system tables in this database to store information about the structure of the Microsoft Exchange Client or Outlook folders, including any new fields you have appended to these folders. Although it is possible to specify a database other than the current database, it is preferable to specify the current database for this purpose. If you specify a separate database and that database is deleted, or if the system tables named ExchangeTablennn in any database you specify are deleted, all the information about the structure of the folders is lost and must be recreated.

Opening Microsoft Exchange or Outlook Folders

You can open a Microsoft Exchange or Outlook folder directly by using the OpenDatabase method and specifying the appropriate connection string. You must also specify an existing Microsoft Access database that will contain the system tables required to maintain the connection when doing so. Using the OpenDatabase method is useful if you simply want to read information and don't require a persistent connection saved in a database. If you need to create a persistent connection to a folder, you should link it as described in the "Linking Microsoft Exchange or Outlook Folders" section later in this article. The following code example opens a personal folder storage, opens each top-level folder, and prints information about the contents of the folder in the Debug Window (Microsoft Access) or Immediate Pane (Visual Basic and Visual Basic for Applications in Microsoft Word, Microsoft Excel, and Microsoft PowerPoint®).

Sub OpenExchange()
    Dim wks As Workspace
    Dim dbs As Database
    Dim rst As Recordset
    Dim strConnect As String
    Dim i As Integer
    Set wks = CreateWorkspace("wks", "admin", "", dbUseJet) 
    strConnect = "Exchange 4.0;MAPILEVEL=Personal Folders|;"
    Set dbs = wks.OpenDatabase("c:\My Documents\Exchange.mdb", _
           0, 0, strConnect)
    On Error GoTo Open_Err
    'Open Each top-level folder and print first three records.
    For Each tbl In dbs.TableDefs
        Set rst = dbs.OpenRecordset(tbl.Name, _
                  dbOpenDynaset, 0, dbOptimistic)
        i = 1
        Debug.Print "Records in Folder: " & tbl.Name
        Debug.Print "----------------------------------"
        While Not rst.EOF And (i < 4)
            Debug.Print i, rst!Subject
            i = i + 1
            rst.MoveNext
        Wend
        rst.Close
Open_Err:
        If (Err) Then
            Debug.Print "*****" & tbl.Name, Err, Err.Description
            Err.Clear
        End If
    Next tbl
    Debug.Print "Done"
    dbs.Close
    wks.Close
End Sub

Note   This code example explicitly creates a new workspace using the OpenWorkspace method. This isn't required if you are using this code within Microsoft Access, which automatically creates a default workspace when it is running. This is required if you want to use this code in the Microsoft Visual Basic programming system, or using Visual Basic for Applications in Microsoft Word, Microsoft Excel, or the Microsoft PowerPoint presentation graphics program. Additionally, to use the Microsoft Jet Exchange installable ISAM from those programs, you must use the References dialog box (Tools menu in the Microsoft Office Visual Basic Editor, Project menu in Visual Basic) to establish a reference to Microsoft DAO 3.5 Object Library. A reference to this object library is established by default in Access 97. The code examples in the following sections assume that you are using them in Access and do not create a new workspace.

Linking Microsoft Exchange or Outlook Folders

You can gain access to a folder within a Microsoft Exchange Client or Outlook mailbox by creating a link to the folder within an Access database. When you link a folder, the folder is treated as a linked table. There are a number of constraints when working with linked folders. For more information, see "Constraints" and "Microsoft Exchange and Outlook Table Formats" later in this article. The following DAO code links a Microsoft Exchange or Outlook folder to an Access database.

Sub LinkExchangeFolder()
    Dim dbs As Database
    Dim tdf As TableDef
    Dim str As String
    Const conTableExists = 3012
    On Error GoTo Link_Err
    ' Set a reference to the current database.
    Set dbs = CurrentDb
    ' Build the connection string.
    str = "Exchange 4.0;MAPILEVEL=Mailbox - Dave Jones" _
         & "|People\Important;TABLETYPE=0;" _
         & "DATABASE=" & dbs.Name & ";" _
         & "PROFILE=DaveJ;PWD=Cisco;"
    ' Create a TableDef object. The name specified for the
    ' TableDef object is displayed as the name of the link
    ' in the Database window.
     Set tdf = dbs.CreateTableDef("Linked Exchange Folder")
    ' Set connection information and specify 
    ' a personal address book.
    tdf.Connect = str
    tdf.SourceTableName = "Barbara"
    ' Append the TableDef object to create the link.
    dbs.TableDefs.Append tdf
    ' Refresh the database window and clear 
    ' the database object variable.
    RefreshDatabaseWindow

Link_Exit:
    dbs.Close
    Set dbs = Nothing
Exit Sub

Link_Err:
    If Err = conTableExists Then
        MsgBox "The table you are trying to create already exists. "
        Resume Link_Exit
    Else
        MsgBox "Error: " & Err.Number & ": " & Err.Description
    End If
End Sub

You can link an address book using the same code by specifying TABLETYPE=1 in the connection string, and then specifying the name of the address book with the SourceTableName property:

tdf.SourceTableName = "Personal Address Book"

If you want to add records to a linked folder or address book, use the same methods used with native Access tables. For example, the following code opens the current database and adds a new record to a previously linked address book named tblMyAddressBook:

Function AddRecord()
    Dim dbs As Database
    Dim rst As Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblMyAddressBook", dbOpenDynaset)
    With rst
        AddNew
        ![First] = "Nancy"
        ![Last] = "Davolio"
        ![Phone] = "555-5555"
        ![Home Phone] = "555-1212"
        .Update
    End With
    dbs.Close
    rst.Close
End Function

Importing Microsoft Exchange or Outlook Folders

To import Microsoft Exchange or Outlook folders, your code first needs to create a new table that contains the fields you want to import. For information on the fields available to you, see "Microsoft Exchange and Outlook Table Formats" later in this article. Once you've created the new table, establish a temporary link to the folder you want to import from, and then run an append query to add records to the new table.

Sub ImportExchangeData()
    Dim dbs As Database
    Dim tdfImport As TableDef
    Dim tdfTemp As TableDef
    Dim fldImportance As Field
    Dim fldPriority As Field
    Dim fldSubject As Field
    Dim fldFrom As Field
    Dim fldTo As Field
    Dim fldCC As Field
    Dim fldSize As Field
    Dim fldBody As Field
    Dim fldReceived As Field
    Dim strConnect As String
    Const conTableExists = 3012
    On Error GoTo Import_Err
    'Set a reference to the current database.
    Set dbs = CurrentDb
    'Create a TableDef object to contain the definition the new table.
    Set tdfImport = dbs.CreateTableDef("tblImportMessages")
    With tdfImport
        'Create the fields you want to include in the new table.
        Set fldImportance = .CreateField("Importance", dbLong, 4)
        Set fldPriority =.CreateField("Priority", dbLong, 4)
        Set fldSubject =.CreateField("Subject", dbText)
        Set fldFrom =.CreateField("From", dbText)
        Set fldTo =.CreateField("To", dbText)
        Set fldCC =.CreateField("CC", dbText)
        Set fldSize =.CreateField("Message Size", dbLong, 4)
        Set fldBody =.CreateField("Body", dbMemo)
        Set fldReceived =.CreateField("Received", dbDate)
    End With
    'Add the fields you want to include in the new table by appending
    'them to the Fields collection.
    With tdfImport.Fields
        .Append fldImportance
        .Append fldPriority
        .Append fldSubject
        .Append fldFrom
        .Append fldTo
        .Append fldCC
        .Append fldSize
        .Append fldBody
        .Append fldReceived
    End With
    'Create the new table by appending it to the 
    ' database's TableDefs collection.
    dbs.TableDefs.Append tdfImport
    'Build the connection string.
    strConnect = "Exchange 4.0;MAPILEVEL=Personal Folders" _
        & "|Inbox;TABLETYPE=0;" _
        & "DATABASE=dbs.Name;" _
        & "PROFILE=DaveJ;PWD=Cisco;"
    'Create a TableDef object that will be used to temporarily link
    'the folder.
    Set tdfTemp = dbs.CreateTableDef("tblTemp")
    'Set Connection Information.
    tdfTemp.Connect = strConnect
    tdfTemp.SourceTableName = "Barbara" 
    'Append the TableDef object to create the link.
        dbs.TableDefs.Append tdfTemp
    'Create the SQL statements that define the append query.
    strSQL = "INSERT INTO tblImportMessages"
    strSQL = strSQL & _
            "( Importance, Priority, Subject, "
    strSQL = strSQL & _
            "[From], To, CC, [Message Size], Body, Received ) "
    strSQL = strSQL & _
            "SELECT tblTemp.Importance, tblTemp.Priority, "
    strSQL = strSQL & _
            "tblTemp.[Normalized Subject], tblTemp.From, tblTemp.To, "
 strSQL = strSQL & _
            "tblTemp.CC, tblTemp.[Message Size], tblTemp.Body, "
 strSQL = strSQL & _
            "tblTemp.Received "
 strSQL = strSQL & "FROM tblTemp;"
    'Execute the append query.
    dbs.Execute strSQL
    'Delete the link and refresh the database window.
    dbs.TableDefs.Delete "tblTemp"
    RefreshDatabaseWindow
' Clear the database object variable and exit sub.
Import_Exit:
    dbs.Close
    Set dbs = Nothing
    Exit Sub
Import_Err:
    If Err = conTableExists Then
        MsgBox "The table you are trying to create already exists. "
        Resume Import_Exit
    Else
        MsgBox "Error: " & Err.Number & ": " & Err.Description
    End If
End Sub

If you want to import all fields of a Microsoft Exchange or Outlook table at once, you can use code to run a query like the following:

Function SimpleImport()
    Dim dbs As Database
    Dim strConnect As String
    Dim strSQL As String
    Set dbs = CurrentDB
    'Build the connection string.
    strConnect = _
       "Exchange 4.0;MAPILEVEL=Mailbox - KiraR|Inbox;DATABASE=" _
       & dbs.Name & ";"
    'Build the SQL statement.
    strSQL = _
        "SELECT * INTO ImportTable FROM [" & strConnect & "].MyMessages;"
    'Run the query.
    dbs.Execute strSQL
    'Refresh the database window and clear database object variable.
    RefreshDatabaseWindow
    dbs.Close
    dbs = Nothing
End Function

Constraints

You cannot select the fields that are available to you in opened and linked folders; a subset of the total number of Microsoft Exchange Client or Outlook fields is available, and these fields are preprogrammed in the Microsoft Jet Exchange installable ISAM. For information on which fields are available, see "Microsoft Exchange and Outlook Table Formats" later in this article. However, for linked tables, you can rearrange or hide the fields in Datasheet view of the table to customize its appearance.

Adding Records

You cannot modify records in a linked folder or address book. You can delete records and add new records to a linked folder or address book. However, when adding new records, only certain fields will accept data. For information on which fields will accept data when adding new records, see the tables in the "Microsoft Exchange and Outlook Table Formats" section below. Additionally, you cannot use the Seek method, because the Microsoft Jet Exchange installable ISAM does not support indexing.

If items in the linked Microsoft Outlook folder use a special form, you must enter the correct form type in the Message Class field when you add new records to the table. If you do not specify a Message Class, Microsoft Outlook uses the default mail form to display your data. The Message Classes for the standard Microsoft Outlook folders are:

Microsoft Exchange and Outlook Table Formats

The Microsoft Jet Exchange driver makes available the table formats listed in the following sections. You can't modify records in a linked folder. When adding new records, you can only enter data in the fields marked as Updatable in the tables that follow. Once you save a new record, it cannot be modified.

Message Table Format

The Message Table format, specified as TABLETYPE=0 in the Connect property in code, applies to Exchange and Outlook Message and Public folders, and to Outlook Calendar (Appointment), Contacts, Journal, Notes, and Tasks folders. Contacts folders include additional fields in a slightly different order as listed in the Contacts Table Format table following this one.

Note   When opening Public Folders, custom fields that are defined for the folder are not available.

The following table lists the fields in the order they are displayed in a linked message folder.

Contacts Table Format

A Contacts table is specified as TABLETYPE=0 in the Connect property in code, but it contains a slightly different list of fields than other Message Table Format tables.

Address Book Format

The Address Book format, specified as TABLETYPE=1 in the Connect property in code, applies to Exchange and Outlook Address Book folders. You cannot modify records in a linked address book. When adding new records, you can only enter data in the fields marked as Updatable in the tables that follow. Once you save a new record, it cannot be modified. The following table lists the fields in the order in which they appear in a linked address book.

Important   The structure of the tables listed in the preceding tables may not correspond exactly to the tables available on your Microsoft Exchange Server, offline folder (.ost), personal folder (.pst), or personal address book (.pab) files. The Microsoft Jet Exchange installable ISAM does not determine the structure of linked tables, and they may vary depending on the version of Microsoft Exchange or Microsoft Outlook you are using.