Working with Outlook from Access

Outlook comes with a standard set of folders, including folders for its calendar, contacts, deleted items, drafts, e-mail Inbox, journal, notes, e-mail Outbox, sent e-mail, and tasks . Users can also add custom folders and can nest folders within one another. Users work with items within their folders ”adding, deleting, viewing, and performing other functions.

The initial version of Outlook shipped with programmatic support only through Microsoft Visual Basic Scripting Edition (VBScript). Outlook 2003 supports scripting with either VBA or VBScript. For compatibility with the rest of the book, this section focuses on scripting Outlook from Access using VBA. In addition, most of the samples use the Contacts folder to provide a familiar context.

This section starts with two samples that build on the Outlook ISAM driver sample you saw in the earlier section, "Linking Access to Other Office Applications." Using the ISAM driver offers a quick, convenient way to look at data in different Outlook folders. The two ISAM driver samples we'll examine momentarily manipulate the Inbox and Contacts folders. The sample that processes the Contacts folder demonstrates how easy it is to consolidate Outlook contents across multiple computers. However, this technique might not be obvious at first because the Outlook ISAM driver returns information for the local computer only.

You can establish an object reference to an instance of Outlook in Access with the CreateObject function. Before you can reference a particular folder, you typically must apply the GetNameSpace method to the Application object. The NameSpace object is an abstract root object that exists between the Application object and individual folders. The method ( GetNameSpace ) takes a single argument, which must be MAPI (Mail Application Programming Interface). You apply the GetDefaultFolder method to the NameSpace object to get the default folder of a certain type. You use a constant to designate which default folder your application will manipulate. The Contacts folder constant is olFolderContacts . Our Outlook Automation samples focus on the Contacts and Outbox folders as well as techniques for creating custom folders and populating them with items.

Note  

You can use the familiar Object Browser to probe the Outlook 2003 object model. Start by opening a Visual Basic Editor session in Outlook. For example, choose Tools, Macro, Visual Basic Editor, or just press Alt+F11. Then, select a class item, such as Application or Namespace. Next , select a class member and click the Help icon (?) for more information about the class member.

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.

Note  

The application can show multiple lines, but each line must end in a new-line character on or before its two hundred fifty- sixth character. Therefore, the mail explorer in this application can show no more than the initial 256 characters in each mail item matching the criterion until it encounters a new-line character, such as a carriage return. If there is no new-line character within a block of 256 characters, the procedure does not extract additional characters. A subsequent sample in the "Viewing Outlook Items in an Access Form" section in this chapter demonstrates a more general approach to the task that does not require new-line characters.

 'ClicktheViewMicrosoftAccesscontrolonthe 'VBEStandardtoolbarimmediatelyafterrunningthisto 'seethe form  Sub ShowDemoMessages()  Dimrst1AsADODB.Recordset     'Instantiaterecordsetandopenitonasubset 'ofthecolumnsandrowsintheOutlookInbox 'onthecurrentcomputer Setrst1=NewADODB.Recordset rst1.CursorLocation=adUseClient rst1.Open "SELECT[From],Subject,Received,Contents " &_  "FROMInboxIN'C:\Windows\Temp\;'" &_  "[Outlook9.0;MAPILEVEL=PersonalFolders;]" &_  "WHEREINSTR(Subject,'Accessmailexplorer')>0;",_ CurrentProject.Connection,,,adCmdText     'OpenfrmInboxExplorerandassignrst1toit DoCmd.OpenForm "frmInboxExplorer" SetApplication.Forms("frmInboxExplorer").Recordset=rst1     'Printthename,actualsize,andvalueforeachrow 'intherecordset;delimitrowsfromoneanother DimiAsInteger DoUntilrst1.EOF     Fori=0Torst1.Fields.Count-1 Debug.Printrst1.Fields(i).Name,_ rst1.Fields(i).ActualSize,_ rst1.Fields(i).Value Nexti Debug.Print "-----" rst1.MoveNext     Loop     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

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.

 

 SubListContacts() DimmyOlAppAsOutlook.Application DimmyNameSpaceAsNameSpace DimmyContactsAsItems DimmyItemAsContactItem     'CreateaninstanceofOutlook. 'ReferenceitsMAPINameSpace. 'ReferenceMAPI'sContactfolder. SetmyOlApp=CreateObject("Outlook.Application") SetmyNameSpace=myOlApp.GetNamespace("MAPI") SetmyContacts=_ myNameSpace.GetDefaultFolder(olFolderContacts).Items     'EnumerateitemsinContactfolderand 'printselectedfields ForEachmyItemInmyContacts Debug.PrintmyItem.FirstName,myItem.LastName,_ myItem.Email1Address Next     'Cleanupobjects SetmyOlApp=Nothing     EndSub 

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.

 SubAddOneContact() DimmyOlAppAsOutlook.Application DimmyItemAsContactItem     'CreateaninstanceofOutlook SetmyOlApp=CreateObject("Outlook.Application")     'CreateanitemfortheContactsfolder. 'Populatetheitemwithvalues. 'Savetheitem. SetmyItem=myOlApp.CreateItem(olContactItem) WithmyItem .FirstName= "foo" .LastName= "bar" .Email1Address= "[e-mail]" .Save EndWith     'Cleanupobjects SetmyItem=Nothing SetmyOlApp=Nothing     EndSub     SubRemoveOneEmail()  DeleteAContact("[e-mail]")      EndSub     SubDeleteAContact(strEmail) DimmyOlAppAsOutlook.Application DimmyNameSpaceAsNameSpace DimmyContactsAsItems DimmyItemAsContactItem     'CreateaninstanceofOutlook. 'ReferenceitsMAPINamespace. 'ReferenceMAPI'sContactfolder. SetmyOlApp=CreateObject("Outlook.Application") SetmyNameSpace=myOlApp.GetNamespace("MAPI") SetmyContacts=_ myNameSpace.GetDefaultFolder(olFolderContacts).Items     'Enumeratetosearchforitemtodelete ForEachmyItemInmyContacts IfmyItem.Email1Address=strEmailThen myItem.Delete ExitSub EndIf Next     'Noentryfound MsgBox "Noentryfoundwithemailof " &strEmail,vbCritical,_  "ProgrammingMicrosoftAccess2003"     'Cleanupobjects SetmyOlApp=Nothing     EndSub 

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:

 SubAddContacts() DimmyOlAppAsOutlook.Application DimmyItemAsContactItem Dimrst1AsNewRecordset     'OpentheContactsfolderinOutlook SetmyOlApp=CreateObject("Outlook.Application")     'Openthetablewiththenewcontacts Withrst1 .ActiveConnection=CurrentProject.Connection .Open "oe4pab" EndWith     'Createacontactitemforaddingcontactsand 'loopthroughthetablerecordstoaddthemtothefolder AssistantWorkingOn DoUntilrst1.EOF SetmyItem=myOlApp.CreateItem(olContactItem) WithmyItem .FirstName=IIf(IsNull(rst1.Fields(0)),_  "",rst1.Fields(0)) .LastName=rst1.Fields(1) .Email1Address=rst1.Fields(2) .Save EndWith rst1.MoveNext Loop AssistantIdleOn     'Cleanupobjects SetmyItem=Nothing SetmyOlApp=Nothing     EndSub 

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.

 SubRemoveEmails() Dimrst1AsNewRecordset     'Openthetablewiththenewcontacts Withrst1 .ActiveConnection=CurrentProject.Connection .Open "oe4pab" EndWith     'Loopthroughthetablerecordstomovethemtothefolder AssistantWorkingOn DoUntilrst1.EOF DeleteAContact(rst1.Fields(2)) rst1.MoveNext Loop AssistantIdleOn     'Cleanupobjects rst1.Close Setrst1=Nothing     EndSub 

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:

 SubDeleteAContact2(strEmail)  OnErrorGoTodelete2Trap DimmyOlAppAsOutlook.Application DimmyNameSpaceAsNameSpace DimmyContactsAsItems DimmyItemAsContactItem DimstrFilterAsString     'CreateaninstanceofOutlook. 'ReferenceitsMAPINameSpace. 'ReferenceMAPI'sContactfolder. SetmyOlApp=CreateObject("Outlook.Application") SetmyNameSpace=myOlApp.GetNamespace("MAPI") SetmyContacts=_ myNameSpace.GetDefaultFolder(olFolderContacts).Items 'Findtargetitemandremoveit strFilter= "[Email1Address]= """ &strEmail& """" SetmyItem=myContacts.Find(strFilter) myItem.Delete     delete2Exit: ExitSub delete2Trap: IfErr.Number=91Then 'Ifitemisnotthere,keepongoing ResumeNext Else 'Otherwise,pausewithamessagebox MsgBoxErr.Number& ": " &vbCrLf&_ Err.Description,vbCritical,_  "ProgrammingMicrosoftAccess2003" ResumeNext EndIf     'Cleanupobjects SetmyOlApp=Nothing     EndSub 

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.

 SubMergeEmail() DimmyOlAppAsOutlook.Application DimmyNameSpaceAsOutlook.NameSpace DimmyFoldersAsOutlook.Folders DimmyFolderAsOutlook.MAPIFolder DimmyMailItemAsOutlook.MailItem Dimstr1AsString Dimrst1AsRecordset     'SetreferencetoOutlook,NameSpaceobject, 'andthefirst-levelfolderscollection SetmyOlApp=CreateObject("Outlook.Application") SetmyNameSpace=myOlApp.GetNamespace("MAPI") SetmyFolders=myNameSpace.Folders.Item(1).Folders     'SetareferencetothePMAMailfolderand 'selectasubsetfromatablecontainingcontacts SetmyFolder=_ myFolders.Item("PMADemo").Folders.Item("PMAMail") Setrst1=NewADODB.Recordset rst1.Open_  "SELECT*FROMWebBasedListWHERE " &_  "StateOrProvince='NY'OR " &_  "StateOrProvince='NJ'OR " &_  "StateOrProvince='CT'",_ CurrentProject.Connection 'Loopthroughrst1andassigntheFirstName 'fieldtothebodyofthemessage AssistantWorkingOn DoUntilrst1.EOF SetmyMailItem=myFolder.Items.Add(olMailItem) str1= "Dear " &rst1.Fields("FirstName") str1=str1& "," &vbCrLf&vbCrLf str1=str1& "OnOctober1,2002,CAB,Inc. " str1=str1& "willbeholdingaseminaratthe " str1=str1& "Hiltonjustthissideofheaven. " str1=str1& "Youarecordiallyinvitedtoattend " str1=str1& "thecomplimentarycontinental " str1=str1& "breakfast,withhealthyservingsof " str1=str1& "mannaandwaterfrom7:30to8:30AM. " str1=str1& "Anenlighteningmultimedia " str1=str1& "presentationanddemofollows " str1=str1& "from8:30to11:30AM." &vbCrLf str1=str1&vbCrLf& "RickDobson" &vbCrLf str1=str1& "President" &vbCrLf& "CAB,Inc." str1=str1&vbCrLf&vbCrLf& "PS:Please " str1=str1& "RSVPviaareplytothis " str1=str1& "message.Thanks." myMailItem.Body=str1 myMailItem.Subject= "SeminarInvitation" myMailItem.Recipients.Addrst1.Fields("EmailAddress") myMailItem.Send rst1.MoveNext Loop AssistantIdleOn     'Cleanupobjects rst1.Close Setrst1=Nothing SetmyOlApp=Nothing     EndSub