Accessing Microsoft Exchange and Microsoft Outlook Data Using Microsoft Visual Basic

Microsoft Corporation

March 1997

Introduction

To use Data Access Objects (DAO) methods and objects with Microsoft® Visual Basic® for Applications in Microsoft Access in order to link or import data from Microsoft Exchange or the Microsoft Outlook™ desktop information manager, you must have the Jet Exchange installable 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 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 Jet Exchange installable ISAM (indexed sequential access method) reads data from the offline folder file or from the Exchange Server. For example, if you initially define a link to your Inbox folder while you are working offline in Outlook, the 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 Exchange Server, the data will be read from the Inbox folder on the server.

Using the 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 any existing items. And, while 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 Access, you should use Automation interfaces to work with Outlook instead. For more information, see the Outlook section of the Microsoft Office Developer Forum Web site (http://www.microsoft.com/outlookdev/).

Installing the Microsoft Jet Exchange Installable ISAM Driver

Before you can use DAO to gain access to Exchange Client or Outlook data, you must install the Jet Exchange installable ISAM. While you can install the Microsoft Jet Exchange installable ISAM using the Dataacc.exe file that is included in the 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 from the article entitled "Spotlight on Interoperability: Importing and Linking Microsoft Exchange and Microsoft Outlook Data to Microsoft Access" on the Microsoft Office Developer Forum Web site at (http://www.microsoft.com/AccessDev/Articles/ExchWiz.htm). This will install and register the most current version of Msexch35.dll so that you can use it from DAO, and also installs the Microsoft Exchange and Outlook Wizard so that you can import and link Exchange and Outlook data using the 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 only 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 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 Exchange and Outlook data, use the parameters, separated by semicolons, as specified in Table 1.

Table 1. Connection Parameters

Connecting to a Microsoft Exchange Server Folder

To gain access to messages in an 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 connection string keyword, MAPILEVEL=. 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 Exchange Client or Outlook application window. Any spaces and capitalization in the name must be preserved.

Connecting to a Personal Folder

In addition to specifying an 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 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 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 dialog box is the name to specify in your connection string. The information in the Path dialog 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 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 keywords PROFILE= and PWD= 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, open the Control Panel and double-click Mail And Fax, 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 dialog 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 an 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 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 an Exchange or Outlook folder directly by using the OpenDatabase method and specifying the appropriate connection string. You must also specify an existing Access database that will contain the system tables required to maintain the connection when doing so. The OpenDatabase method is useful if you want to simply 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, 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 the 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 Access, which automatically creates a default workspace when it is running. This is required if you want to use this code in the Visual Basic programming system, or using Visual Basic for Applications in Word, Excel, or the PowerPoint. Additionally, to use the Jet Exchange installable ISAM from those programs, you must use the References dialog box (in the Tools menu in the Microsoft Office Visual Basic Editor, and in the 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 an 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 an 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, you 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 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 of the fields of an 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 can't select which fields are available to you in opened and linked folders; a subset of the total number of Exchange Client or Outlook fields are available, and they are preprogrammed in the Jet Exchange installable ISAM. For information on which fields are available, see "Microsoft Exchange and Outlook Table Formats" later in this article. For linked tables, however, you can rearrange or hide the fields in the Datasheet View of the table to customize its appearance.

Adding records

You can't modify any 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 later in this article. Additionally, you can't use the Seek method, because the Jet Exchange installable ISAM does not support indexing.

If items in the linked 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, Outlook uses the default mail form to display your data. The Message Classes for the standard Outlook folders are listed in Table 2.

Table 2. Message Classes

Microsoft Exchange and Outlook Table Formats

The Jet Exchange driver makes available the table formats listed in the follow sections. You can't modify any 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 can't 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 Table 3.

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

Table 3 lists the fields in the order they are displayed in a linked message folder.

Table3. Message Table Format

Contacts Table Format

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

Table 4. Contacts Table Format

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 can't modify any 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 can't be modified. Table 5 lists the fields in the order in which they appear in a linked address book.

Table 5. Address Book Format

Important   The structure of the tables previously listed may not correspond exactly to the tables available on your Exchange Server offline folder (.ost), personal folder (.pst), or personal address book (.pab) files. The Jet Exchange installable ISAM does not determine the structure of linked tables, and they may vary depending on the version of Exchange or Outlook that you are using. To check the structure of your tables, link a table and then run code like the following:

Function ListFormat()
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Set dbs = CurrentDb
Set tdf = dbs.TableDefs!tblMyTable
For Each fld In tdf.Fields
    Debug.Print "Name: " & fld.Name & vbTab &_
    " Type: " & fld.Type & vbTab &_
    " Updatable: " & fld.DataUpdatable
Next fld
End Function