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 VB Script. Outlook 2000 adds programmatic control with VBA. Outlook 2000 supports scripting with either VBA or VB Script. For compatibility with the rest of the book, this section focuses on scripting Outlook from Access using VBA. In addition, all the samples use the Contacts folder to provide a familiar context.

You can establish a 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 takes a single argument, which must be MAPI for the current release. 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.

Enumerating Items in the Contacts Folder

The following procedure manipulates the 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. The book's companion CD also includes some sample contact information for populating a Contacts folder.

 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 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 expose the items in the Contacts folder. The last of these uses the GetDefaultFolder method to return the Contacts folder, and it uses the Items property to access the individual items. The enumeration takes place with 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.

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 folder. 'Populate the item with values. 'Save the item.     Set myItem = myOlApp.CreateItem(olContactItem)     With myItem         .FirstName = "foo"         .LastName = "bar"         .Email1Address = "[e-mail]"         .Save     End With End Sub Sub removeOneEmail()     deleteAContact ("[e-mail]") 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 2000" End Sub 

The 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 using a constant 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 it 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 it finds a match, it removes the item by invoking the Delete method and exits the procedure 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 below 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 ContactItem 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 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 34 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. (Chapter 8 describes how to design procedures to control Assistant animation. You can find the procedures for Assistant animation in the Chap08 directory on the companion CD.) 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. It 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 add them to the folder.     AssistantWorkingOn     Do Until rst1.EOF         deleteAContact (rst1.Fields(2))         rst1.MoveNext     Loop     AssistantIdleOn 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, it pauses with a message box that requires the user to click a button to proceed. If there are numerous entries in the list of items to delete that 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 below.

 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, just keep on going.         Resume Next     Else 'Otherwise, pause with a message box.         MsgBox Err.Number & ": " & vblfcr & _             Err.Description, vbCritical, _             "Programming Microsoft Access 2000"         Resume Next     End If 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, it applies the Delete method to that item. Using Find saves a noticeable amount of time even with a short list of items such as those in the oe4pab table, but its speed advantage grows with longer lists of e-mail addresses or with Contact folders that have many items. The procedure also traps failures of the Find method to return an item. This happens when there is no matching item in the Contacts folder for an e-mail address. In this situation, deleteAContact2 silently returns control to the calling routine so that it can search again for a new e-mail address. There is no need for operator intervention.