Viewing Outlook Inbox Items in an Access Form

Outlook has its own explorers for examining items in its folders. Nevertheless, you might find it useful to use an Access-based explorer for Outlook items. First of all, this is easy to do. Second, it offers an application better control over functionality and allows users to work in your application while using Outlook data. (The built-in Outlook explorers require users to exit your application while using Outlook data.) Third, when you build your own custom forms for exploring Outlook items, you can readily filter Outlook data and even merge it with data from other sources.

The form depicted in Figure 9-6 shows a custom explorer built in Access for Outlook items with the string "Access mail explorer" in the Subject field of any item in the Outlook Personal Folders collection. This collection contains all personal folders available to an Outlook user, including the Inbox folder. When I ran this form, my Inbox had three items, but only two of them contained the string "Access mail explorer" in the Subject field. Notice the form's navigator buttons show that it's displaying the first of two records—these are the two items that match the search criterion.

Figure 9.6: An Access form used to browse filtered Outlook Inbox items.

The form's Properties dialog box shows the Record Source property setting is Null, but the fields have Control Source settings that correspond to Inbox fields. The top three text boxes refer to the From, Subject, and Received fields, and the bottom text box with a vertical scroll bar references the Contents field. To simplify the presentation, I made these design settings manually, but you can also program them. (See Chapter 5 and Chapter 6 for more information on programming controls.) When the controls point at fields but the form itself does not reference a particular record source, it's easy to use a single form to explore Inbox items with different filter specifications. At the very least, you must explicitly designate a record source for the form in order for it to show any data at all. The sample code we'll examine in a moment assigns an ADO recordset to the form's Recordset property.

The code sets the form's recordset and opens the form. Because the code sets the form's Recordset property and no default Record Source setting exists, you cannot use the form to browse data without first running the code. The code sample performs two functions. First, it defines a recordset, which will become the record source for the form. The recordset uses the ISAM driver for Outlook as discussed in the "Linking Access to Other Office Applications" section in this chapter. Second, the application's code opens the form and assigns the recordset to the form's Recordset property. The code for defining the recordset has three distinctive features. First, the recordset must have its CursorLocation property set to adUseClient if you plan to use it as input to a form. This is unnecessary if you just want to print results to the Immediate window. Second, the fields after the SELECT keyword in the recordset's source statement delimit FROM with square brackets ([ ]). This is because FROM is both a SQL keyword and an Outlook Inbox item field name. To specify the field name, you need the brackets. Third, the SQL string for the recordset includes a WHERE clause. The WHERE clause filters out all records that don't have the string "Access mail explorer" in the Subject field. Notice that the WHERE clause appears after the IN keyword, which is an element of the FROM clause for the SQL statement.

After defining the recordset, the procedure opens the form. Next, the procedure assigns the recordset to the form's Recordset property. To help you understand the values returned by the recordset, the application prints in the Immediate window the Name, ActualSize, and Value properties for each field within each row of the recordset. This simple demonstration resides in a standard module. In order to demonstrate the sample application, you need to open the form by running the ShowDemoMessages procedure instead of directly opening the frmInboxExplorer form from the Access database window. Return to the Access window to view the form after running the procedure in the Visual Basic Editor. In order to see any messages on the form, you will need to update the search string in the procedure ("Access mail explorer") to match that the string from one or more messages in your Outlook folders.

'Click the View Microsoft Access control on the
'VBE Standard toolbar immediately after running this to
'see the form  Sub ShowDemoMessages() 
Dim rst1 As ADODB.Recordset
   
'Instantiate recordset and open it on a subset
'of the columns and rows in the Outlook Inbox
'on the current computer
Set rst1 = New ADODB.Recordset
rst1.CursorLocation = adUseClient
rst1.Open "SELECT [From], Subject, Received, Contents " & _
    "FROM Inbox IN'C:\Windows\Temp\;'" & _
    "[Outlook 9.0;MAPILEVEL=Personal Folders|;]" & _
    "WHERE INSTR(Subject,'Access mail explorer')>0;", _
    CurrentProject.Connection, , , adCmdText
   
'Open frmInboxExplorer and assign rst1 to it
DoCmd.OpenForm "frmInboxExplorer"
Set Application.Forms("frmInboxExplorer").Recordset = rst1
   
'Print the name, actual size, and value for each row
'in the recordset; delimit rows from one another
Dim i As Integer
Do Until rst1.EOF
   
    For i = 0 To rst1.Fields.Count - 1
        Debug.Print rst1.Fields(i).Name, _
            rst1.Fields(i).ActualSize, _
            rst1.Fields(i).Value
    Next i
    
    Debug.Print "-----"
    rst1.MoveNext
   
Loop
   
'Cleanup objects
rst1.Close
Set rst1 = Nothing
   
End Sub 

Enumerating Items in the Contacts Folder

The following procedure uses Automation to manipulate the Outlook Contacts folder to enumerate all its items. You can set up a sample Contacts folder with a few entries to evaluate this and subsequent samples. This book's companion content also includes some sample contact information for populating a Contacts folder (see the AddContacts procedure discussed in the "Adding Multiple Items to the Contacts Folder" section of this chapter). When manipulating or even enumerating items in Outlook, it is convenient to have Outlook open so that there is no delay in the effect of a manipulation, such as adding or deleting a contact item in the Contacts folder.

Microsoft Outlook Security Prompts and Express ClickYes

Several Outlook Automation samples in this chapter directly manipulate the contents of your Outlook contact items. Because such manipulation might be the result of a virus attack, Outlook by default prompts you through a popup dialog box whenever a user attempts to manipulate a contact item or other type of Outlook item. The prompt lets you authorize the use of a single item or Outlook items generally for a designated interval of up to 10 minutes.

Microsoft offers workarounds to these built-in security rules for Microsoft Exchange administrators, but sophisticated individual users and small businesses not running Microsoft Exchange might be frustrated by the security prompt that requires a click every time a VBA macro attempts to manipulate an Outlook item. Often times, the user simply has to click Yes on the security prompt. ExpressSoft Inc. offers a free utility program (Express ClickYes) that runs in the background and automatically clicks Yes in response to an Outlook security prompt. This program is suitable for Outlook users who have legitimate Automation needs, but do not run Microsoft Exchange (computers running Outlook in Internet Mode Only are candidates for this utility). The download URL for the Express ClickYes utility is http://www.express-soft.com/mailmate/clickyes.html.

After downloading the utility, a computer operator can turn the Express ClickYes utility off and on. This allows an operator to benefit from the built-in Outlook security prompt except for when a custom VBA macro can explicitly benefit from an automatic response to the security prompt. I tested all the Outlook Automation samples in this chapter with the Express ClickYes utility running in the background.

Sub ListContacts()
Dim myOlApp As Outlook.Application
Dim myNameSpace As NameSpace
Dim myContacts As Items
Dim myItem As ContactItem
   
'Create an instance of Outlook.
'Reference its MAPI NameSpace.
'Reference MAPI's Contact folder.
Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myContacts = _
    myNameSpace.GetDefaultFolder(olFolderContacts).Items
   
'Enumerate items in Contact folder and
'print selected fields
For Each myItem In myContacts
    Debug.Print myItem.FirstName, myItem.LastName, _
        myItem.Email1Address
Next
   
'Clean up objects
Set myOlApp = Nothing
   
End Sub

The procedure starts by declaring four variables: one for the Outlook application, one for its NameSpace object, one for the collection of items in the Contacts folder, and one for enumerating those items. It takes three Set statements to display the items in the Contacts folder. The last of these statements uses the GetDefaultFolder method to return the Contacts folder, and it uses the Items property to return the individual items from within the Contacts folder. The enumeration takes place within a For…Each loop. The items in the Contact folder have a series of properties that identify information about contacts. The sample uses three of these properties to print the first name, last name, and first e-mail address for each entry in the Contacts folder. These property names differ from the field names for the return sets from the Outlook ISAM driver, even when both types of names refer to the same data elements.

Adding an Item to the Contacts Folder

You can also build Access-based solutions that manipulate the contents of the Contacts folder. The first of the next three procedures, AddOneContact, inserts a new contact into the folder. It uses string constants to define the first name, last name, and e-mail address for a contact, but you can easily modify the procedure to pass these as arguments. The next two procedures, RemoveOneEmail and DeleteAContact, do just that. The RemoveOneEmail procedure passes an e-mail address to the DeleteAContact procedure, finds a contact item with a matching e-mail address, and then deletes it.

Sub AddOneContact()
Dim myOlApp As Outlook.Application
Dim myItem As ContactItem
   
'Create an instance of Outlook
Set myOlApp = CreateObject("Outlook.Application")
   
'Create an item for the Contacts folder.
'Populate the item with values.
'Save the item.
Set myItem = myOlApp.CreateItem(olContactItem)
With myItem
    .FirstName = "foo"
    .LastName = "bar"
    .Email1Address = "foobar@yourcompany.com"
    .Save
End With
   
'Clean up objects
Set myItem = Nothing
Set myOlApp = Nothing
   
End Sub
   
Sub RemoveOneEmail() 
    
DeleteAContact ("foobar@yourcompany.com") 
   
End Sub
   
Sub DeleteAContact(strEmail)
Dim myOlApp As Outlook.Application
Dim myNameSpace As NameSpace
Dim myContacts As Items
Dim myItem As ContactItem
   
'Create an instance of Outlook.
'Reference its MAPI Namespace.
'Reference MAPI's Contact folder.
Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myContacts = _
    myNameSpace.GetDefaultFolder(olFolderContacts).Items
   
'Enumerate to search for item to delete
For Each myItem In myContacts
    If myItem.Email1Address = strEmail Then
        myItem.Delete
        Exit Sub
    End If
Next
   
'No entry found
MsgBox "No entry found with email of " & strEmail, vbCritical, _
    "Programming Microsoft Access 2003"
   
'Clean up objects
Set myOlApp = Nothing
   
End Sub

The AddOneContact procedure requires just two objects—the Outlook Application object and a ContactItem object to represent an item in the Contacts folder. The procedure creates a reference to the Application object with the CreateObject function. This reference supports the CreateItem method, which creates an empty instance of an item for any specified folder. You designate the type of folder for the item by using an argument that you pass to the CreateItem method. You can choose from more than 140 properties to specify the characteristics of a contact. The sample assigns string constants for the FirstName, LastName, and Email1Address properties. (Yes, each contact can have more than one e-mail address.) Then, the sample invokes the Save method to store the new entry in the Contacts folder.

Deleting an Item from the Contacts Folder

The DeleteAContact procedure accepts a string argument that is the value of the Email1Address property of the contact item to delete. The procedure enumerates members of the Contacts folder until it finds one with an Email1Address property that matches the passed argument. When the procedure finds a match, it removes the item by invoking the Delete method and exits to eliminate further searching. If the procedure enumerates the entire contents of the Contacts folder without discovering a match, control passes to a message box statement, which reports that no entries match the e-mail address passed to it.

Adding Multiple Items to the Contacts Folder

One common task performed with a database manager such as Access is adding multiple contact items to the Contacts folder. These contacts can come from any source, such as the Contacts folder on another computer, addresses entered over the Internet, or even an old Access contact file. The AddContacts procedure that follows uses one approach to updating an Outlook Contacts folder with the contact information in an Access table:

Sub AddContacts()
Dim myOlApp As Outlook.Application
Dim myItem As ContactItem
Dim rst1 As New Recordset
   
'Open the Contacts folder in Outlook
Set myOlApp = CreateObject("Outlook.Application")
   
'Open the table with the new contacts
With rst1
    .ActiveConnection = CurrentProject.Connection
    .Open "oe4pab"
End With
   
'Create a contact item for adding contacts and
'loop through the table records to add them to the folder
AssistantWorkingOn
Do Until rst1.EOF
    Set myItem = myOlApp.CreateItem(olContactItem)
    With myItem
        .FirstName = IIf(IsNull(rst1.Fields(0)), _
            "", rst1.Fields(0))
        .LastName = rst1.Fields(1)
        .Email1Address = rst1.Fields(2)
        .Save
    End With
    rst1.MoveNext
Loop
AssistantIdleOn
   
'Clean up objects
Set myItem = Nothing
Set myOlApp = Nothing
   
End Sub

The procedure sets a reference to the Outlook application and then opens a recordset based on the oe4pab table. This is the local table in the Access Tables folder. The table contains just 35 entries, but the procedure can accommodate a much longer list of addresses. For this reason, the procedure calls another procedure that turns on the Assistant with a working animation and leaves it on until Access and Outlook finish updating the Outlook Contacts folder with the entries in the oe4pab table. In between the two calls to turn Assistant animation on and off, a Do loop iterates through all the records in the oe4pab table. The loop creates a new ContactItem object on each pass, and then it assigns the records for that pass to the item and saves the item.

Deleting Multiple Items from the Contacts Folder

The following procedure, RemoveEmails, is an adaptation of the DeleteAContact procedure shown earlier in the section. RemoveEmails deletes multiple records from a Contacts folder. It removes one item at a time by successively calling DeleteAContact with different e-mail addresses. The sample uses the addresses in the oe4pab table as the source for the arguments. This procedure offers two advantages: it is easy to write, and it reuses the DeleteAContact procedure.

Sub RemoveEmails()

Dim rst1 As New Recordset
   
'Open the table with the new contacts
    With rst1
        .ActiveConnection = CurrentProject.Connection
        .Open "oe4pab"
    End With
   
'Loop through the table records to move them to the folder
    AssistantWorkingOn
    Do Until rst1.EOF
        DeleteAContact (rst1.Fields(2))
        rst1.MoveNext
    Loop
    AssistantIdleOn
   
'Clean up objects
rst1.Close
Set rst1 = Nothing
   
End Sub

While this procedure can get the job done, it has at least two deficiencies. First, it searches through the Contacts folder for each item that it wants to remove. This gets increasingly costly as the number of items to delete grows, the number of items in the Contacts folder grows, or both. Second, if there is no match for an item, the procedure pauses with a message box that requires the user to click a button to continue. If numerous entries in the list of items to delete are already missing from the Contacts folder, having to click a button for each item not present in the folder can become tedious. One solution to these weaknesses is to replace the call to DeleteAContact with a call to DeleteAContact2, shown here:

Sub DeleteAContact2(strEmail) 
On Error GoTo delete2Trap
Dim myOlApp As Outlook.Application
Dim myNameSpace As NameSpace
Dim myContacts As Items
Dim myItem As ContactItem
Dim strFilter As String
   
'Create an instance of Outlook.
'Reference its MAPI NameSpace.
'Reference MAPI's Contact folder.
    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    Set myContacts = _
        myNameSpace.GetDefaultFolder(olFolderContacts).Items
    
'Find target item and remove it
    strFilter = "[Email1Address] = """ & strEmail & """"
    Set myItem = myContacts.Find(strFilter)
    myItem.Delete
   
delete2Exit:
    Exit Sub
    
delete2Trap:
    If Err.Number = 91 Then
'If item is not there, keep on going
        Resume Next
    Else
'Otherwise, pause with a message box
        MsgBox Err.Number & ": " & vbCrLf & _
            Err.Description, vbCritical, _
            "Programming Microsoft Access 2003"
        Resume Next
    End If
   
'Clean up objects
Set myOlApp = Nothing
   
End Sub

This procedure expedites the search for an item to delete by using the Find method. It constructs a criterion for the Find method based on the e-mail address passed to it. After finding an item with a matching e-mail address, the procedure applies the Delete method to that item. The speed advantage from using the Find method grows along with the number of e-mail addresses in the Contact folders and the total number of items in the Contacts folder. The procedure also traps failures of the Find method to return an item, which happens when there is no matching item for an e-mail address in the Contacts folder. In this situation, DeleteAContact2 silently returns control to the calling routine so it can search again for a new e-mail address without operator intervention.

Merging Access Contacts with an Outlook Message

The Outlook mail item is the primary device for holding a message. Individual mail items have the properties Subject, Body, and Recipients. The Body property is a string representing the body of a message. If you prefer, you can designate the body of a message through its HTMLBody property, which represents the HTML content for a message. Mail items have numerous other properties that allow you to control the content of individual e-mail messages. Furthermore, a mail item has a variety of methods that correspond to what folks do with messages. The next sample demonstrates the invocation of the Send method. However, mail items include many additional methods, such as Reply, ReplyAll, Copy, and Delete.

The e-mail merge sample combines a subset of records from an Access table with a standard message for all members of the subset. An ADO recordset makes the table's contents available for inclusion in individual messages. A WHERE clause in the SQL string for the recordset allows the selection of a subset of records from a much lengthier record source. The mail item's body is a string, so you can vary its content based on the values in the Access table. For example, the sample shows how to personalize a message by addressing individuals by their first name. This name comes from the Access table. After setting a message's properties, the procedure sends the message and advances to the next row in the recordset.

Sub MergeEmail()
Dim myOlApp As Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myFolders As Outlook.Folders
Dim myFolder As Outlook.MAPIFolder
Dim myMailItem As Outlook.MailItem
Dim str1 As String
Dim rst1 As Recordset
   
'Set reference to Outlook, NameSpace object,
'and the first-level folders collection
Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myFolders = myNameSpace.Folders.Item(1).Folders
   
'Set a reference to the PMAMail folder and
'select a subset from a table containing contacts
Set myFolder = _
    myFolders.Item("PMADemo").Folders.Item("PMAMail")
Set rst1 = New ADODB.Recordset
rst1.Open _
    "SELECT * FROM WebBasedList WHERE " & _
    "StateOrProvince='NY' OR " & _
    "StateOrProvince='NJ' OR " & _
    "StateOrProvince='CT'", _
    CurrentProject.Connection
    
'Loop through rst1 and assign the FirstName
'field to the body of the message
AssistantWorkingOn
Do Until rst1.EOF
    Set myMailItem = myFolder.Items.Add(olMailItem)
    str1 = "Dear " & rst1.Fields("FirstName")
    str1 = str1 & "," & vbCrLf & vbCrLf
    str1 = str1 & "On October 1, 2002, CAB, Inc. "
    str1 = str1 & "will be holding a seminar at the "
    str1 = str1 & "Hilton just this side of heaven.  "
    str1 = str1 & "You are cordially invited to attend "
    str1 = str1 & "the complimentary continental "
    str1 = str1 & "breakfast, with healthy servings of "
    str1 = str1 & "manna and water from 7:30 to 8:30 AM.  "
    str1 = str1 & "An enlightening multimedia "
    str1 = str1 & "presentation and demo follows "
    str1 = str1 & "from 8:30 to 11:30 AM." & vbCrLf
    str1 = str1 & vbCrLf & "Rick Dobson" & vbCrLf
    str1 = str1 & "President" & vbCrLf & "CAB, Inc."
    str1 = str1 & vbCrLf & vbCrLf & "PS: Please "
    str1 = str1 & "RSVP via a reply to this "
    str1 = str1 & "message.  Thanks."
    myMailItem.Body = str1
    myMailItem.Subject = "Seminar Invitation"
    myMailItem.Recipients.Add rst1.Fields("EmailAddress")
    myMailItem.Send
    rst1.MoveNext
Loop
AssistantIdleOn
   
'Clean up objects
rst1.Close
Set rst1 = Nothing
Set myOlApp = Nothing
   
End Sub