Automating Microsoft Outlook 97

Presented by Mike Gilbert

Mike Gilbert is a senior consultant with MCW Technologies, specializing in application development using Microsoft® Access, the Visual Basic® programming system, Microsoft SQL Server™ and Microsoft Office. He writes for several periodicals and is a contributing editor to Smart Access and Access/Office/VBA Advisor. Mike is coauthor of Microsoft Access 95 Developer’s Handbook, Access 97 Developer’s Handbook, and VBA Developer’s Handbook, all from Sybex. He is a trainer with Application Developer’s Training Company and a regular speaker at conferences such as Tech·Ed, Advisor Publication’s DevCon, and VB Teach. He spends what spare time he has enjoying the quiet southern life in Richmond, Virginia, with his wife, Karen, and their two cats, Chicago and Cairo. You can reach Mike on the Internet at mikeg2@mcwtech.com.

Phone: (804) 360-3486
Fax: (804) 360-3486
E-mail: mikeg2@mcwtech.com

Working with Outlook

This session and white paper cover creating integrated applications using the Automation interface of the Microsoft Outlook™ desktop information manager. Outlook superseded Schedule+ as the individual- and group-scheduling tool that ships with Microsoft Office. Like Schedule+, Outlook has an Automation interface that allows you to interact with it using a development tool that supports Automation. This session focuses on those applications that use Visual Basic for Applications as their development language.

The Sample Files

I have provided sample files in three distinct formats: Microsoft Excel 97, Microsoft Access 97, and ASCII text. The only exception to this is the sample time-reporting application mentioned at the end of the paper, which is only in Microsoft Access 97 format.

Outlook Features

In addition to its role as an electronic-mail client, Outlook allows users to store information on five general types of objects:

This session is not designed to teach you all about these objects or how to use Outlook to manage them. It assumes you know how to use the Outlook user interface and that you have a fairly good idea what the features are used for. This session will concentrate on teaching you how to manipulate Outlook objects using Automation.

To demonstrate the basics of using the Outlook Automation interface, I’ve created a sample application that uses Microsoft Access to control Microsoft Outlook. In case you don’t currently have a copy of Microsoft Access, I’ve included the source code for the application in separate text files. The application allows you to create journal entries and then load that schedule information back into a Microsoft Access database. The application is a simple time-reporting system that tracks the length of time spent on various tasks using the Outlook journal. While the example focuses heavily on journal entries, the techniques I describe can be used to access any other object.

To test this application you’ll need a copy of Outlook installed on your computer. To get the maximum benefit, you’ll also need to create some journal entries. You can do this by using the first component of this sample project or Outlook itself.

Programming Outlook

Automation is not the only way to program Outlook. You can also use Visual Basic Scripting Edition to control Outlook from within an Outlook form. Figure 1 shows the Outlook programming model.

Figure 1. The Outlook programming model

Outlook forms (the functionality for which is supplied by the same Microsoft Forms component shared by Word and Microsoft Excel) use Visual Basic Script to control Outlook objects. You can also use the Automation capabilities built into Visual Basic Script to control other Automation components. This session focuses solely on controlling Outlook objects from an Automation client.

The Outlook Object Model

The Outlook object model is unlike any of the other Office products, primarily because it does not follow the same document-centric metaphor. The data it manipulates is far less structured and, like its predecessor Schedule+, the object model can be difficult to learn and use. Furthermore, Outlook is designed to be an integral part of your electronic messaging system and as such must cope with various service providers, addressing schemes, storage mechanisms, and electronic-mail functions.

Figure 2 illustrates the Outlook object model, which may at first appear less complex than that of the other applications. It has an Application class at its root, but that’s where similarities end.

Figure 2. The Outlook object model

First, Outlook requires that you create a reference to what it calls a Namespace object. This represents one of the messaging service provider layers that Outlook depends on for data storage (although “MAPI” is the only type of namespace Outlook currently supports). When you install Outlook on a computer with no other messaging component, the setup program also installs the MAPI-based Microsoft Exchange messaging system.

Each messaging system implements persistent data storage using a hierarchical folder metaphor similar to disk subdirectories. The Outlook Namespace class contains a Folders collection (containing MAPIFolder objects) representing the top-level folder of each installed storage system. Each of these, in turn, contains a Folders collection with members for each subfolder (Inbox, Outbox, and so on). Every folder object has a Folders collection, allowing for infinite nesting of data storage.

Data in folders is represented by an Items collection. Each element of this collection can be one of a variety of object classes that represent such things as mail messages, appointments, journal entries, contacts, and tasks. It is this uncertainty about what a folder contains that makes programming with Outlook challenging.

Individual Item Classes

While a Microsoft Exchange folder can contain a wide variety of items, only a few types are treated differently by Outlook. The Outlook type library defines a set of classes to represent these objects. Each class implements properties and methods applicable to the objects. Figure 3 shows these classes. To get the complete picture, consult the Visual Basic for Applications Object Browser.

Figure 3. Outlook object classes

As you can see from the figure, Outlook creates some objects for you. For example, when you schedule a meeting, Outlook sends MeetingRequestItems to the attendees. These appear in the Inbox as distinct object classes. You can’t create them directly.

Outlook and Microsoft Exchange

Even if you don’t use Outlook to send and receive electronic mail, you’re still using Microsoft Exchange, Microsoft’s messaging product, to some degree. This is because Outlook relies on Microsoft Exchange, or more specifically MAPI (Messaging Application Programming Interface), for data storage. Microsoft Exchange uses a hierarchical collection of folders for data storage. Every folder can have subfolders, ad infinitum. Individual folder items are just blobs of data. It’s up to the applications that use them to make sense of the bits and bytes.

At a minimum, to use Outlook you’ll need to create a set of personal folders on your hard drive. Microsoft Exchange stores these in a PST (for personal message store) file. When you create your PST file, your set of personal folders will contain subfolders for electronic messaging (Inbox, Outbox, Sent Items, and Deleted Items). Outlook will create additional folders for its use (Calendar, Contacts, Journal, Notes, and Tasks). Part of creating Automation solutions with Outlook is navigating this folder hierarchy.

Finally, to access any of the folders managed by Outlook, you’ll need to tell Outlook what messaging system, known as a namespace, to use. You do this by calling the GetNamespace method of the Outlook Application object. Currently only one namespace, MAPI, is supported, but this method allows for future extensibility.

Conversing with Outlook

Before you can begin writing Automation code to control Outlook, you must add a reference to the Outlook type library to your project. The file name is Msoutl8.olb, and it is located in the Office 97 installation directory.

Beginning an Automation session with Outlook involves creating a new instance of the Application class, specifying a namespace to use, and logging on to the messaging system. I’ve wrapped these steps inside a function in basOutlook called adhGetOutlook, shown in Listing 1, that returns a pointer to the MAPI namespace after logging in.

Note   This function uses a Visual Basic for Applications user form to elicit user input. If you are adapting this code to another environment, you will need to substitute the Visual Basic for Applications–specific code with appropriate replacements.

Listing 1: The adhGetOutlook functions gets a reference to an Outlook Namespace

Function adhGetOutlook() As Outlook.NameSpace
' From Access 97 Developer's Handbook
' by Litwin, Getz, and Gilbert. (Sybex)
' Copyright 1997. All Rights Reserved.
'
' Returns a reference to the Outlook MAPI namespace
    
    Dim objOutlook As New Outlook.Application
    Dim objNamespace As Outlook.NameSpace
    Dim strProfile As String
    Dim strPassword As String
    
    ' Open the logon form
    frmChooseProfile.Show
     
    ' If the user didn't cancel, continue
    If frmChooseProfile.OK Then
        
        ' Get the profile and password to use
        strProfile = frmChooseProfile.cboProfile
        strPassword = frmChooseProfile.txtPassword
        Unload frmChooseProfile
        
        ' Get a reference to the MAPI workspace
        Set objNamespace = objOutlook.GetNamespace("MAPI")
        
        ' Log on, creating a new MAPI session,
        ' using the profile and password
        Call objNamespace.Logon(strProfile, _
         strPassword, False, True)
        
        ' Return a reference to the namespace
        Set adhGetOutlook = objNamespace
    End If
End Function

The procedure begins by opening a dialog, shown in Figure 4, which prompts the user for a Microsoft Exchange profile and the password associated with it. Microsoft Exchange profiles are used to define combinations of data storage and electronic messaging systems for use during a Microsoft Exchange session. Once the user has made a selection, the adhGetOutlook function calls GetNamespace, passing “MAPI” as the namespace type, and then calls the namespace’s Logon method. Logon takes four arguments:

Figure 4. Selecting a Microsoft Exchange profile prior to working with Outlook

Shown below are several examples of calling the Logon method:

' Supply all the information, suppress logon dialog
objNamespace.Logon "MyProfile", "password", False, True

' Use the standard logon dialog
objNamespace.Logon "", "", True, True

' Outlook is running, piggyback on its session
objNamespace.Logon

Working with Outlook Objects

Before discussing a more or less complete application, it makes sense to spend some time looking at some Outlook objects. To help facilitate this discussion I’ve written a procedure that will loop through all of the objects in an Outlook folder and print their Subject properties. Listing 2 shows this procedure.

Listing 2: The ListItems procedure prints the contents of an Outlook folder

Private mobjOutlook As Outlook.NameSpace

Sub ListItems(lngFolder As Long)
    Dim objFolder As Outlook.MAPIFolder
    Dim objItem As Object
    
    ' Get a pointer to Outlook and the folder
    If GetOutlook() Then
        Set objFolder = mobjOutlook.GetDefaultFolder(lngFolder)
        
        ' Loop through each item, and print the subject
        For Each objItem In objFolder.Items
            Debug.Print objItem.Subject, _
             objItem.Attachments.Count & " attachments"
        Next
        
        ' Release all memory
        Set objItem = Nothing
        Set objFolder = Nothing
    End If
End Sub

Private Function GetOutlook() As Boolean
' This function makes sure we have a valid,
' module-level reference to Outlook's namespace

    ' If the current pointer is nothing,
    ' Call adhGetOutlook
    If mobjOutlook Is Nothing Then
        Set mobjOutlook = adhGetOutlook()
    End If
    
    ' Return True or False
    GetOutlook = Not (mobjOutlook Is Nothing)
End Function

Listing 2 also shows a helper function called GetOutlook, which maintains a module-level pointer to an Outlook Namespace object. The function and variable are used so you don’t have to log in to Outlook each time you want to run the sample code. Each of the sample procedures calls GetOutlook to ensure that the reference is valid, only continuing if the function returns True.

ListItems accepts a single parameter-a type of Outlook folder. You can use any of the constants in the OlDefaultFolders Enum class. For your convenience I’ve listed these in Table 1.

Table 1: Outlook folder types

Constant Value Folder
OlFolderCalendar 9 Calendar
OlFolderContacts 10 Contacts
OlFolderDeletedItems 3 Deleted Items
OlFolderInbox 6 Inbox
OlFolderJournal 11 Journal
OlFolderNotes 12 Notes
OlFolderOutbox 4 Outbox
OlFolderSentMail 5 Sent Mail
OlFolderTasks 13 Tasks

Figure 5 shows an example of calling ListItems from the Immediate window.

Figure 5. Calling ListItems from the Immediate window

You’ll notice that the procedure, after calling GetOutlook, calls the GetDefaultFolder method of the Outlook Namespace object, passing the folder type. This returns a pointer to the requested MapiFolder object. If you want to use one of the predefined Outlook folders, this is the easiest approach. The alternative would be to use the Namespace’s Folders collection and search for the folder you want. You would need to use this approach to work with items in a custom, nonstandard folder.

ListItems uses a For Each loop to iterate through the Items collection of the MapiFolder object. One important thing to point out is that you must use the generic Object data type for the variable in the For Each loop. Due to a quirk in the Outlook Automation interface, if you attempt to declare the object as a specific class, Visual Basic for Applications will generate a “Type mismatch” error when you attempt to make the assignment. This only appears to affect For Each loops. Direct assignments using methods like Add seem to work fine.

Creating New Items

You can create new Outlook items in two ways. First, you can call the CreateItem method of the Outlook Application class. Second, you can call the Add method of an Outlook MAPIFolder object. In both cases the result is an object pointer you can use to set various properties. You then call the object’s Save or Send method. Note, however, that if you use CreateItem, Outlook decides where to save the item. All of the sample procedures in this paper that create new objects use the Add method of an Outlook folder.

Mail Messages

The MailItem class represents electronic-mail messages. The class has properties that you would expect for mail messages, such as Subject, Body (in fact, all Outlook objects have Subject and Body properties), To, Cc, SentOn, and so on. You can use a variation of the ListItems procedure to access any of these properties.

Probably of more interest are the properties and methods that deal with sending and receiving mail. Listing 3 shows a procedure that creates a new mail message and sends it. Note that the procedure calls the Add method of the Outbox folder’s Items collection to create the new mail item. (You must pass Add the type of item to create; olMailItem in this case.) After obtaining a reference to the mail item, the procedure calls the Add method of its Recipients collection to create a recipient and the Add method of the Attachments collection to attach a file.

Listing 3: Creating and sending a mail message

Sub SendMail()
    Dim objFolder As Outlook.MAPIFolder
    Dim objItem As Outlook.MailItem
    Dim strAddress As String
    
    ' Get a pointer to Outlook and the folder
    If GetOutlook() Then
        Set objFolder = mobjOutlook. _
         GetDefaultFolder(olFolderOutbox)
        
        ' Create a new mail message
        Set objItem = objFolder.Items.Add(olMailItem)
        With objItem
            ' Add a recipient (Note: If recipient is
            ' in the address book just use the display name,
            ' otherwise include the complete address)
            strAddress = mobjOutlook.CurrentUser.Address
            With .Recipients.Add(strAddress)
                .Type = olTo
                If Not .Resolve Then
                    MsgBox "Cannot resolve address '" & _
                     strAddress & "'", vbExclamation
                    Exit Sub
                End If
            End With
            
            ' Add an attached file (make sure
            ' the path is valid!!)
            With .Attachments.Add("C:\TEMP\Test.doc")
                .DisplayName = "Test Document"
            End With
            
            ' Set subject, text, importance
            .Subject = InputBox("Enter subject:")
            .Body = InputBox("Enter body:")
            .Importance = olImportanceHigh
            
            ' Send it!
            .Send
        End With
        
        ' Release all memory
        Set objItem = Nothing
        Set objFolder = Nothing
    End If
End Sub

When sending any type of Outlook item, you must resolve all of the recipients’ addresses. If you don’t, and Outlook can’t do it on its own, you’ll get a run-time error on the Send method. Note how the sample procedure handles this. It calls the Resolve method for the newly added recipient.

Replying to a mail message is as easy. Each MailItem object has a Reply method that creates a new MailItem object with the subject, body, and recipients already established. Listing 4 shows a subroutine that replies to a mail message based on its subject.

Listing 4: Replying to an e-mail message

Sub ReplyToMail()
    Dim objFolder As Outlook.MAPIFolder
    Dim objItem As Object
    Dim strSubject As String
    
    ' Get a pointer to Outlook and the folder
    If GetOutlook() Then
        Set objFolder = mobjOutlook. _
         GetDefaultFolder(olFolderInbox)
        
        ' Get message subject to search for
        strSubject = InputBox( _
         "Enter subject of mail message to reply to:", _
         "Enter Subject", objFolder.Items.Item( _
         objFolder.Items.Count).Subject)
         
        ' Locate the mail message
        Set objItem = objFolder.Items. _
         Find("[Subject] = '" & strSubject & "'")
         
        ' Generate a reply
        If Not objItem Is Nothing Then
            With objItem.Reply
                .Body = InputBox( _
                 "Enter any additional text:") & _
                 .Body
                .Send
            End With
        End If
        
        ' Release all memory
        Set objItem = Nothing
        Set objFolder = Nothing
    End If
End Sub

You’ll notice that the procedure uses the Find method of the Inbox’s Items collection to locate the correct mail message to reply to. The Find method is the only practical way to locate items in an Outlook folder because they do not have a useful property that uniquely identifies them. The allowable criteria for Find are the same as those for the Restrict method and are listed at the end of this paper. Once you’ve located an item that matches the criteria, you can search for additional matching items using the FindNext method.

Appointments and Meetings

One of the greatest strengths of Outlook is its use as an individual- or group-scheduling tool. You can easily access this functionality through Automation using the AppointmentItem class. Listing 5 shows an example of how to create a new appointment. You simply call the Add method of the Calendar folder’s Items collection, and set properties of resulting AppointmentItem object (don’t forget to call the Save method, too).

Listing 5: Creating a new appointment

Sub NewAppointment()
    Dim objFolder As Outlook.MAPIFolder
    Dim objItem As Outlook.AppointmentItem
    
    ' Get a pointer to Outlook and the folder
    If GetOutlook() Then
        Set objFolder = mobjOutlook. _
         GetDefaultFolder(olFolderCalendar)
        
        ' Create a new appointment
        Set objItem = objFolder.Items.Add(olAppointmentItem)
        With objItem
            .Subject = "Dentist"
            .Start = Date + 7.5
            .Duration = 60      ' Could also specify End property
            .ReminderMinutesBeforeStart = 30
            .ReminderPlaySound = True
            .ReminderSet = True
            
            ' Don't forget to save it!
            .Save
        End With
        
        ' Release all memory
        Set objItem = Nothing
        Set objFolder = Nothing
    End If
End Sub

Note that the appointment’s start time is given as a Variant date and the duration is in minutes (a long integer). You can also specify the End property instead of a duration. AppointmentItems also have a variety of properties that relate to reminders.

Scheduling a meeting is exactly like creating an appointment except for three things. First, you must tell Outlook the AppointmentItem is really a meeting by setting its MeetingStatus property. Second, you must add recipients to the appointment, just like with a mail item. Third, you must call the Send method to send out meeting invitations. Listing 6 shows how to create a meeting with a single recipient.

Listing 6: Scheduling a meeting

Sub ScheduleMeeting()
    Dim objFolder As Outlook.MAPIFolder
    Dim objItem As Outlook.AppointmentItem
    Dim strAddress As String
    
    ' Get a pointer to Outlook and the folder
    If GetOutlook() Then
        Set objFolder = mobjOutlook. _
         GetDefaultFolder(olFolderCalendar)
        
        ' Create a new appointment
        Set objItem = objFolder.Items.Add(olAppointmentItem)
        With objItem
            .Subject = "Steering Committee Meeting"
            .Start = Date + 8.5
            .Duration = 240
            
            ' Make it a meeting
            .MeetingStatus = olMeeting
            
            ' Add recipients (attendees)
            strAddress = mobjOutlook.CurrentUser.Address
            With .Recipients.Add(strAddress)
                .Type = olTo
                If Not .Resolve Then
                    MsgBox "Cannot resolve address '" & _
                     strAddress & "'", vbExclamation
                    Exit Sub
                End If
            End With
            
            ' Save and send
            .Save
            .Send
        End With
         
        ' Release all memory
        Set objItem = Nothing
        Set objFolder = Nothing
    End If
End Sub

Once a meeting has been created, you can review the MeetingResponseStatus of each recipient to see who has responded.

Viewing Free and Busy Times

Often, when scheduling meetings, it is helpful to see who is free to meet. You can access this information through the FreeBusy method of a Recipient object. The syntax for FreeBusy is:

ObjRecipient.FreeBusy(Start, MinPerChar [, CompleteFormat])

FreeBusy returns a text string in which each character represents the person’s status for a given time interval, given in minutes by the MinPerChar argument. You pass the start time in the first argument, and Outlook determines the free and busy time for one month from that date. The overall size of the string is determined by the specified time interval.

By default, the string returned contains a series of zeros and ones, where 0 represents free time and 1 represents busy time. If you pass True as the optional third argument, the characters will be either 0, 1, 2, 3, or 4, representing free, tentative, out of office, or busy times, respectively.

Therefore, to determine whether someone is a available for a two-hour meeting on May 19 at 1:00 P.M., you could use code like this:

If Instr(objRecipient.FreeBusy(#5/19/97 1:00 PM#, 120), _
 "0") = 1 Then

    ' The person is available
End If

Contact Management

In addition to e-mail and scheduling, Outlook is an adequate contact management application. You can manage contacts programmatically using the ContactItem class. Despite being a simple class, ContactItem has more properties than any other Outlook class due to the many pieces of information that Outlook tracks. Listing 7 shows a procedure that creates a new contact.

Listing 7: Creating a new contact

Sub AddContact()
    Dim objFolder As Outlook.MAPIFolder
    Dim objItem As Outlook.ContactItem
    
    ' Get a pointer to Outlook and the folder
    If GetOutlook() Then
        Set objFolder = mobjOutlook. _
         GetDefaultFolder(olFolderContacts)
        
        ' Create a new contact
        Set objItem = objFolder.Items.Add(olContactItem)
        With objItem
            .FirstName = "Bill"
            .LastName = "Gates"
            ' Note: FileAs doesn't get set automatically!
            .FileAs = "Gates, Bill"
            .CompanyName = "Microsoft Corporation"
            .CompanyMainTelephoneNumber = "(425) 882-8080"
            .Email1Address = "billg@microsoft.com"
            .Spouse = "Melinda"
            .Children = "Jennifer"
            
            ' Add a user defined property
            With .UserProperties.Add("Net Worth", olCurrency)
                .Value = 24000000000#
            End With
            
            ' Save!
            .Save
        End With
         
        ' Release all memory
        Set objItem = Nothing
        Set objFolder = Nothing
    End If
End Sub

This is not a very exciting procedure. In fact, you’ve already seen most of the techniques it employs. What’s new, however, is the use of the UserProperties collection. Each Outlook object can contain one or more user-defined properties. The UserProperties collection maintains these. As you can see, you can add as many of your own as you want. Each user-defined property becomes a new field that you can use to search and sort on.

Tasks and Notes

Tasks and notes are additional Outlook object types you can use to store information. Since the mechanics of manipulating them are no different from mail items and appointments (in fact, tasks and notes are much simpler objects), I’ve chosen not to include lengthy code samples here. You can use the Visual Basic for Applications Object Browser to explore their properties and methods.

Logging Off

When you are done using Outlook objects you should log off from Outlook and Microsoft Exchange using the Namespace object’s Logoff method. The sample includes a subroutine that does this:

Sub Logoff()
    ' If we're logged on, log off!
    If Not mobjOutlook Is Nothing Then
        mobjOutlook.Logoff
        Set mobjOutlook = Nothing
    End If
End Sub

A Sample Time-reporting Application

To demonstrate the power of the Outlook Automation interface I have created a more full-featured application-a time-reporting system that uses the Outlook Journal. Not only does it demonstrate how to use journal items, it should give you some idea of the things you can use Outlook for.

Creating New Journal Entries

The frmOutlookWriteJournal form, shown in Figure 6, lets you create journal entries by selecting a date on the calendar ActiveXTM control, entering subject, start time, and end time information, and clicking the Log button.

Figure 6. The frmOutlookWriteJournal form lets you create new journal entries

Before you click the Log button, however, you must click the Connect button to begin the Automation session. Code behind this button calls adhGetOutlook and stores the resultant Namespace object in a module-level variable. It does this so you can use the same namespace to create multiple journal entries without repeatedly starting and stopping Outlook. After Outlook starts, the button becomes disabled.

Code behind the Log button, shown in Listing 8, uses the Namespace object to create a new journal entry by first obtaining a reference to the Journal folder. It does this by calling the namespace’s GetDefaultFolder method with the olFolderJournal constant. While you could use the Folders collection to navigate the hierarchy manually, using GetDefaultFolder is easier when you need to access any of the messaging or Outlook folders.

Listing 8: Adding new journal items

Private Sub cmdLog_Click()
    Dim objOLJournal As Outlook.MAPIFolder

    ' Make sure I have a valid reference
    If mobjOLNamespace Is Nothing Then
        Me!cmdConnect.Enabled = True
        Me!cmdConnect.SetFocus
        Me!cmdOK.Enabled = False
    Else
        ' Get a reference to the "Journal" folder
        Set objOLJournal = mobjOLNamespace. _
         GetDefaultFolder(olFolderJournal)

        ' Create a new journal item, set its
        ' properties, and save it
        With objOLJournal.Items.Add(olJournalItem)
            .Start = CVDate(Me!calMain.Value & " " & Me!txtStart)
            .End = CVDate(Me!calMain.Value & " " & Me!txtEnd)
            .Subject = Me!cboSubject
            .Type = "Task"
            .Save
        End With
    End If
End Sub

Once a reference to the Journal folder has been obtained, the procedure calls the Add method of the folder’s Items collection. This creates a new journal entry. The procedure uses a With block to set various properties before calling the new object’s Save method.

Loading Journal Information

Once you’ve created a few journal entries, you can use the frmOutlookReadJournal form shown in Figure 7 to load them into a Microsoft Access table. The contents of this table, tblJournalEntries in the sample database, is displayed on the form as well as summarized on a report, rptTime. You can open the report by clicking the Report button.

Figure 7. The frmOutlookReadJournal form loads and displays journal entries

Reading journal entries from Outlook is quite similar to creating them. First, I call adhGetOutlook, then I create a reference to the Journal folder. Finally I use a For Each loop to iterate through each item in the folder. Listing 9 shows a portion of the code that accomplishes this. The rstJournal variable in the listing refers to a DAO recordset based on the tblJournalEntries table, and objFilteredItems is declared as an Outlook.Items variable.

Listing 9: Reading journal items into a Microsoft Access database

' Get a reference to the "Journal" folder
Set objOLJournal = objOLNamespace. _
 GetDefaultFolder(olFolderJournal)

' Filter the items in the folder
Set objFilteredItems = _
 adhFilterItems(objOLJournal.Items)

' Process the items in the folder, adding them
' to the journal entries table
With rstJournal
    For Each outItem In objFilteredItems
        .AddNew
        !EntryID = outItem.EntryID
        !Type = outItem.Type
        !Subject = outItem.Subject
        !Start = outItem.Start
        !End = outItem.End
        .Update
    Next
End With

In addition to simply reading the items from the folder, you can optionally apply a filter to them using the Items collection’s Restrict method. Listing 10 shows the adhFilterItems function that accomplishes this. It builds a filter string based on controls on the form. Applying the Restrict method does not change the existing Items collection but instead returns a new Items collection representing items that match the filter expression. It is this new collection that is returned as a result of calling adhFilterItems.

Listing 10: Filtering the Items collection to only those that match given criteria

Private Function adhFilterItems(objItemsToFilter As _
 Outlook.Items) As Outlook.Items

    Dim strFilter As String
    Dim strQuote As String * 1

    ' Get a double quote for delimiters
    strQuote = Chr$(34)

    ' First check date range
    If IsDate(Me!txtFilterStart1) And _
     IsDate(Me!txtFilterStart2) Then

        strFilter = "[Start] >= " & strQuote & _
         Me!txtFilterStart1 & strQuote & " AND " & _
         "[Start] <= " & strQuote & _
         Me!txtFilterStart2 & strQuote
    End If

    ' Then check Subject
    If Not IsNull(Me!cboFilterSubject) Then
        If Len(strFilter) Then
            strFilter = strFilter & " AND "
        End If
        strFilter = "[Subject] = " & strQuote & _
         Me!cboFilterSubject & strQuote
    End If

    ' If I have a filter, apply it to the Items collection,
    ' otherwise just return the original collection
    If Len(strFilter) Then
        Set adhFilterItems = objItemsToFilter. _
         Restrict(strFilter)
    Else
        Set adhFilterItems = objItemsToFilter
    End If
End Function

The Restrict method (and its relative, the Find method) is useful when you have many items in a particular folder. Reading each item one at a time to locate specific ones can take a lot of time. Filter expressions used with the Restrict and Find methods must obey these rules:

Summary

The Outlook object model is extremely complex and feature rich. This session was meant to give you an overview of the capabilities of the Outlook Automation interface along with simple and practical code examples. While this paper did not cover every Outlook class, property, or method, it did touch upon most of the elements required to write an integrated application.

© 1997 Microsoft Corporation. All rights reserved.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

Microsoft and Visual Basic are registered trademarks and ActiveX and Outlook are trademarks of Microsoft Corporation.

Other product or company names mentioned herein may be the trademarks of their respective owners.

Portions of the content of this session were excerpted from the following books, with permission of the publisher:

Access 97 Developer’s Handbook
Paul Litwin, Ken Getz, and Mike Gilbert
© 1997, Sybex, Inc.

VBA Developer’s Handbook
Ken Getz and Mike Gilbert
© 1997, Sybex, Inc.