Programming Microsoft Outlook from Microsoft Access 97

Download self-extracting Microsoft Word version of this page. (48K; expands to 260K)

By David Shank

Contents

• Introduction
• Creating New Outlook Items
• Working with Outlook Folders Linked as Microsoft Access Tables
• Conclusion

Top

Introduction

You can access and manipulate the features of Microsoft Outlook from Microsoft Access 97 or any other 32-bit application that supports the Visual Basic for Applications programming language--including Visual Basic, any Microsoft Office 97 applications, or Visual C++. This paper focuses on accessing information stored in Outlook folders and how to manipulate Outlook objects from Microsoft Access 97. For more information about programming the Outlook object model, see "The Microsoft Outlook 97 Automation Server Programming Model."

The Microsoft Jet Exchange Indexed Sequential Access Method (ISAM) driver and the Microsoft Exchange and Outlook Wizard also provide access to information stored in Outlook folders from Microsoft Access 97. However, using the Exchange ISAM driver to connect to Outlook folders is useful primarily for reading information in existing folders. You cannot modify existing items, and adding new items in this manner is cumbersome. This paper briefly discusses using the Exchange ISAM driver to link Outlook folders and presents some ideas for using linked Outlook data and Automation to modify data in linked folders. For more detailed information about using the Exchange ISAM driver, see "Accessing Microsoft Exchange and Outlook Data Using Visual Basic."

Note   To run the sample code that uses the Exchange ISAM driver to link Outlook folders to Microsoft Access tables, make sure you have the most recent version of the Exchange ISAM driver and the Microsoft Exchange and Outlook Wizard. The driver and wizard are contained in Wzmapi80.exe, which you can download from "Spotlight on Interoperability: Importing and Linking Microsoft Exchange and Microsoft Outlook Data to Microsoft Access."

In the past, building sophisticated contact management or messaging functionality into your Microsoft Access applications required a significant amount of programming. Now, with programmatic access to the Outlook 97 object model, you can incorporate the power of Outlook with a few lines of code. For Microsoft Access 97 users, the most useful part of this new functionality is the ability to integrate data stored in a Microsoft Access database with data stored in Outlook folders.

Getting the Sample Database

The Microsoft Access 97 tables, queries, forms, and code samples discussed in this paper are available in accoutdb.mdb, which you can download from this page.

Getting Help

The Outlook Visual Basic Help file is not installed with Microsoft Office 97. If you want context-sensitive help for Outlook objects, methods, and properties from within the Object Browser or a module window, you must install the file yourself. Copy Vbaoutl.hlp and Vbaoutl.cnt from the Valupack\Morehelp folder of the Office 97 CD to the \Program Files\Microsoft Office\Office folder.

Getting Started

To gain access to Outlook objects from within Microsoft Access, use the References dialog box (Tools menu) to early-bind a reference to the Outlook 8.0 Object Library (Msoutl8.olb). Although early binding is the preferred way to reference the Outlook type library, you can also late-bind it by using the CreateObject or GetObject function.

You manipulate Outlook objects programmatically by setting a reference to the Outlook Application object and the Outlook NameSpace object. After you have set these references, you can create or access any Outlook object representing a mail message, appointment, contact, journal entry, and so on. Use the CreateItem method of the Application object to create new Outlook objects. Use the NameSpace object and its methods to gain access to existing items in your Outlook folders.

From Microsoft Access 97, you can also use the Microsoft Jet Exchange ISAM driver to create a linked table that contains data from existing Outlook folders. Creating linked tables is described in "Working with Outlook Folders Linked as Microsoft Access Tables" later in this paper. For more information about the Outlook object model, see "The Microsoft Outlook 97 Automation Server Programming Model" and Chapter 5, "Microsoft Outlook Objects," from the Microsoft Office 97/Visual Basic Programmer's Guide.

Outlook exposes a Folders collection that contains a Folder object for each major class of Outlook items. Each Folder contains a collection of items of the type contained by the specified folder. For example, Outlook maintains an Inbox folder that contains all the MailItem objects in your Outlook Inbox. Outlook items can be mail messages, appointments, contacts, journal entries, notes, and more.

The following code examples use global object variables to represent the Outlook Application and NameSpace objects, along with a custom procedure to ensure that these global variables are properly initialized. The global variables are dimensioned in the Declarations section of a Standard module as follows:

The following custom procedure called InitializeOutlook sets the global Application and NameSpace objects if they have not already been created:

Note   The GetNameSpace method can gain access to any recognized data source. Currently, the MAPI message store is the only data source supported by Outlook. The MAPI message store contains all of the items in all of the folders that currently exist in your copy of Outlook.

Each procedure in the sample code that needs to reference either the Application object or the NameSpace object includes the following code fragment. If the Application object variable has not been created, InitializeOutlook is called to create both it and the NameSpace object variables.

Top

Creating New Outlook Items

It is easy to programmatically create and display new Outlook items. The CreateItem method of the Outlook Application object takes a single argument of the OlItem type. The objects you can create with this method are: MailItem, AppointmentItem, ContactItem, TaskItem JournalItem, NoteItem, and PostItem. From within a Microsoft Access database, you can also create new items using data stored in the database. This section describes both of these ways to create new Outlook items by programmatically manipulating objects in the Outlook object model.

Creating Outlook Items from Any Program by Using Automation

The code samples in this section work in any application that supports Automation.

New Mail Messages The following code fragment calls the CreateMail procedure that contains arguments representing the recipient name, message subject, message body and attachment. CreateMail creates a new mail message using the CreateItem method, and it uses properties and methods of the MailItem object to complete the message and send it. You can use the object's Display method to show the message for further user input or the Send method to send the message directly.

In the CreateMail procedure, notice that the variable blnResolveSuccess indicates whether all the recipients could be identified. If blnResolveSuccess is False, then the mail message is displayed so the user can fix the problem, otherwise, the message is sent directly. (The use of variants for recipient and attachment arguments in CreateMail is discussed in the next section.)

Multiple Recipients and Attachments To include multiple recipients, you use the Add method of the Recipients collection for each recipient you want to include. For example, you can change the strRecip argument to a variant that accepts an array of values representing recipients for the mail message. You then use the Add method to add the members of the array to the Recipients collection. Similarly, you could change the strAttachments argument to a variant array containing strings representing the file path and file name for each attachment. You then use the Attachments collection's Add method to attach them to the mail message.

The complete CreateMail procedure and a sample calling routine named CallCreateMail are included in the sample database.

Other New Outlook Items The following generic procedure provides a kind of "One-Stop Shop" for creating new Outlook items of various types. The type of item created is determined by the argument passed to the procedure in intObjectType. After the item is created, it is displayed for further action by the user.

This procedure creates an object variable of the type specified in the intItemType argument, and uses the Application object's CreateItem method to set the object variable. After the new object is created, its Display method is called to make the object visible and ready for further action by the user.

After you have set a reference to the Outlook object type library, this one procedure can be used in your application to incorporate all Outlook items. If you ever added this functionality to Microsoft Access applications using the Schedule+ Type Library and OLE Messaging Object Library, you will appreciate the power that the Outlook object type library makes available to you.

Creating Outlook Items from Within Microsoft Access

The code samples discussed so far are simple, straightforward, and generic. They show how to take advantage of Outlook functionality using code that works from any application that supports Automation. But for the serious Microsoft Access developer, the real power of Outlook objects comes when you use data stored in your database to create Outlook objects.

Imagine that you are an employee of the Northwind Traders Company and you spend your day on the phone talking with customers. The Northwind Traders database gives you access to all the customer data you need, but you often have to re-enter information from the database when you switch to other applications. You would like to be able to use the features in Outlook without having to leave the Customers form in the Northwind database.

Now imagine that you get a call from Paula Wilson, your contact at Rattlesnake Canyon Grocery. In the past, you would record notes of your conversation and then take further action as needed. Now, however, you open the Customers form (which is in the sample database) and use the Find button on the toolbar to quickly go to the Rattlesnake Canyon Grocery record.

Recording Notes from a Customer Contact

Paula wants to talk with you about several issues, and you need to take careful notes to make sure you respond to each item discussed. To record notes of your conversation, click the Customer Call button on the Customers form. This creates a new Outlook Journal item you can use to record your conversation with Paula. Notice that the new Journal item is created using information from the current record on the Customers form.

The Customer Call button on the Customers form executes the CreateJournalEntry procedure. This procedure creates a new JournalItem object, using information from the current record of the Customers form to set several properties of the JournalItem object. An example of the new Journal item created during your call with Paula is shown below.

The CreateJournalEntry procedure first creates a new JournalItem object using the following code:

A With/End With block is used to set several properties of the JournalItem object.

The Display method displays the new JournalItem object for further input by the user. Although this procedure shows how to set several JournalItem properties, keep in mind that there are several additional properties available. To see all the methods and properties of an object, use the Object Browser and Outlook Visual Basic Help.

Based on your notes from the telephone call, you realize that you have three additional tasks. You need to set up a meeting to resolve shipping problems. You need to send mail to the Sales Representative who handles Paula's account with the information about the new order. Finally, you need to add the name of the manager for the new store to your list of contacts.

Creating a New Appointment To set up the appointment to resolve shipping problems, click the Schedule Appt button on the Customers form. The Schedule Appt button calls the CreateAppointment procedure to create a new AppointmentItem object and displays the new item for further input from the user.

This procedure uses the Add method of the AppointmentItem's Recipients collection to add the contact name from the current record. It sets the AppointmentItem's Subject property using data from the ContactName and CompanyName fields in the current record. Finally, it calls the Display method to display the new Appointment.

You can use the Meeting Planner tab in the new Appointment to add the names of additional persons who need to attend the meeting.

Sending a Mail Message The second item to complete as a follow-up to your conversation with Paula is to send mail to the Sales Representative who handles Paula's account with information about the new order she wants to place.

Note   This example uses another form to gather information to create a new MailItem object. You can either display the new item or send it directly.

When you click the Send Mail button on the Customers form, the NewMailMessage form is opened to gather additional information used to create a new MailItem object. The Send Mail button executes the OpenMailForm procedure, which opens the NewMailMessage form and passes the current record's ContactName and CompanyName fields using the OpenArgs property:

The information passed in the OpenArgs property is used to set the value of the txtTo and txtSubject Text Box controls:

The To text box contains the name of the message recipients. You can add additional names using a semi-colon-delimited list of names in this control. You can specify one or more persons to receive copies of the message by adding a semi-colon-delimited list of names to the CC text box. In addition, you can specify one or more attachments by entering a semi-colon-delimited list of file names in the Attachments text box. When you click the Show Message button, code in the button's Click event procedure calls the SendNewMail procedure:

The SendNewMail procedure uses the Add method of the MailItem's Recipients collection to add one or more recipients to the mail message, and uses the Recipients collection's ResolveAll method to check for valid recipient names.

Next, if the user has entered files to be attached to the mail message, those files are added using the collection's Add method:

The Subject and Body properties are set using data supplied by the user:

The Importance property is set using the value of the ctlImportance option group. Finally, the message is either displayed or sent directly based on the value of the ctlViewMail option group:

Adding a Contact Item to the Outlook Contacts List Your final follow-up task is to add the name of the manager for the new store to your Outlook Contacts list. Linking the Outlook Contacts database to your Microsoft Access database is straightforward.

To add a new contact for the Rattlesnake Canyon Grocery, type the name of the new contact in the Contact Name box on the Customers form and click the Add to Contacts button. After you add the new contact, press ESC to return Paula Wilson as the contact name. The AddContact procedure checks to see whether the Customers form is currently on a new record and if so, prompts the user to save that record before proceeding.

Outlook stores contact information in a flat-file database. If you add new contacts to Outlook using Automation, make sure that you are not duplicating records. The AddContact procedure accomplishes this by searching for an existing record containing the same ContactName and CustomerID as the record you're trying to add. If the procedure finds that the contact record already exists, it gives you the option of not adding a duplicate record. The following code checks for duplicate records:

The first line of this code examines the NameSpace object's Items collection for a member object that matches the data in objform!ContactName.Value. (Note that Outlook requires that you reference the Value property explicitly.) If an existing entry is found, the user has the option of canceling the operation. To see how this works, try adding duplicate contacts to Outlook from the Customers form.

If an existing entry is not found in the Outlook Contacts list, the procedure adds it. The code continues and sets various properties of the ContactItem object; then it adds the object to Outlook:

Although this example sets a dozen properties of the ContactItem object, there are many more properties exposed by Outlook that allow you to store and track any data you may want. In addition, the Outlook object model exposes a UserProperties collection of custom-defined UserProperty objects that let you create and store your own custom properties.

Top

Working with Outlook Folders Linked as Microsoft Access Tables

If you have installed the Microsoft Jet Exchange ISAM driver, you can import Outlook data into a Microsoft Access table, or you can link a Microsoft Access table directly to an Outlook folder. When you import data from Outlook, you get a snapshot of the data as it existed at the time of the import. When you link to an Outlook folder, however, you always have a current view of the data in the folder. In both cases, existing data is read-only.

With a linked folder you can add and delete records, but it is often easier to add and delete records using Automation and the Outlook object model. To edit existing records, you must access the record using Automation through the Outlook object model.

Linking to an Outlook Folder

The sample database contains a Contacts form that depends on the existence of a Contacts table linked to your Outlook Contacts folder. The Contacts form works correctly only if you have installed the Microsoft Jet Exchange ISAM driver, as described in the Introduction earlier in this paper.

When you first open the sample database, the Contacts table does not exist. If you click the Browse Contacts button on the Customers form, the Contacts form is opened, and code in the form's Open event procedure calls the LinkExchangeFolder procedure to link your Outlook Contacts folder to a Microsoft Access table named Contacts.

The LinkExchangeFolder procedure is designed to link to any Outlook folder. You pass the name of the folder you want to link as the only argument to the procedure. If the procedure succeeds, it adds a table to your database that contains a record for every item in the specified folder.

Before you can link to the Contacts folder, you must create a connection string. The LinkExchangeFolder builds the connection string using the following code:

Notice that this connection string includes a reference to a custom procedure named GetMailBoxName. This custom procedure returns the name of the Outlook Mailbox on your computer that contains the Contacts folder. A custom procedure is used because the mailbox name is different on every computer.

The LinkExchangeFolder procedure then creates a TableDef object and sets its Connect property by using the connection string, and sets its SourceTableName property by using the name passed to the procedure, in this case Contacts.

Finally, the procedure appends the new TableDef object to the database TableDefs collection using the following code:

After the Contacts folder is linked to a Microsoft Access table, the code in the Contacts form's Open event sets the form's RecordSource property to the qrySortedContacts query that selects and sorts the records from the Contacts folder.

Editing Existing Contact Items

After you have linked an Outlook folder as a Microsoft Access table, you can use it just like you use any other Microsoft Access table, with one important exception: The data from a linked Outlook folder is read-only. This section describes one way to use linked Outlook folders together with Automation of Outlook objects so that you can edit data that appears in the read-only records of a table linked to an Outlook folder.

To illustrate the techniques, you must first save the contact information for Paula Wilson at Rattlesnake Canyon Grocery. Using the Customers form in the sample database, go to the record for Rattlesnake Canyon Grocery, and click the Add to Contacts button to add the record to your Outlook contacts folder. Then click the Browse Contacts button on the Customers form and go to the record for Rattlesnake Canyon Grocery in the Linked Outlook Contacts form, which is shown in the following illustration:

Now imagine that Paula called to tell you that she now has an e-mail address and the company has a Web page. You want to add this information to your contact record. After you locate the record you want to edit, you can display the Outlook contact information by clicking the Edit Record button. If you change any of the information that appears in the fields displayed in the Linked Outlook Contacts form, you need to move off the current record and then return to it to see the changes. Depending on how your Outlook message store is configured, you may see "#NAME" in the Post Office Box field on this form. For more information on message store configurations, see "Accessing Microsoft Exchange and Outlook Data Using Visual Basic."

When you click the Edit Record button, the real work of finding the record in the Outlook Contacts folder and displaying it for further editing is done by the GetItemFromName procedure. This procedure accepts one required argument and two optional arguments. The information passed in the arguments is used to find the specific record in the Outlook Contacts folder. In the sample database, the arguments consist of the first three fields on the Linked Outlook Contacts form.

At the heart of the GetItemFromName procedure, the Restrict method is used to filter the contacts in the Outlook Contacts folder to the specific item that matches the criteria passed to the procedure. The procedure builds the criteria string and then applies the Restrict method using the following code:

Notice that if criteria used as the argument to the Restrict method returns more than one matching record, then the GetItemFromName procedure returns False. You can add additional code to parse the collection of items returned by the Restrict method to further narrow your search, but that technique is beyond the scope of this paper.

Top

Conclusion

This paper covers a broad range of information. To clarify the concepts, work through the sample code and experiment with the sample database. This should give you the information you need to incorporate the power of Outlook into your Microsoft Access database.

For more information about Outlook, see Outlook Visual Basic Help, the Microsoft Office 97/Visual Basic Programmers Guide, and the Outlook developer page at http://www.microsoft.com/outlookdev/.