Automate E-Mail Operations with Outlook and VBA

Note: The code referenced in this article is available for download here.

Microsoft Office is a wonderful productivity suite. Besides being packed with features, its claim to fame for me (and the major reason I haven’t switched to other competing Office suites) is that it allows a developer to customize and enhance the product’s functionality using Visual Basic for Applications (VBA) or .NET code. For businesses, this means that Office applications (Word, Excel, Access, PowerPoint, Outlook, and others) can be used in the software development process to solve certain problems that would otherwise be difficult to write from scratch, such as automated document and spreadsheet creation.

Over the years, Four Hundred Guru has run quite a few tips on how to wed DB2 for i data with Excel using VBA. This time, I’d like to focus on doing something similar with Outlook. To follow this tip, you’ll need some basic knowledge of the VBA language and the ActiveX Data Objects (ADO) library, which will allow Outlook to communicate to the IBM i (a.k.a. AS/400). Further, this tutorial was written using Outlook 2007 (a.k.a. Outlook 12), although I don’t believe there are any 2007 specific features implemented.

The Challenge

Consider the common dilemma of receiving manifold e-mails with text file attachments and the need to automatically upload these attachments to a DB2 table (without user intervention). These specific e-mails are received by a company’s Exchange server and delivered to a Microsoft Outlook client. From there, Outlook should:

1. monitor all new mail items
2. identify each specific e-mail as an automation candidate by inspecting the e-mail’s sender, subject, attachment, or body (e-mails that don’t fit the specified criteria are left alone)
3. automatically save the text attachment (in this example, a comma delimited “csv” format)
4. read the saved attachment and upload the data to DB2 for i
5. move the e-mail to a “processed” folder when done

Outlook can do this relatively easy!

The Outlook Integrated Development Environment

To begin, the VBA Integrated Development Environment (IDE) is accessed within Outlook the same as in other Office applications: choose Tools→Macro→Visual Basic Editor or use the Alt+F11 key sequence. Once in the VBA IDE, you have the ability to add your own standard or class modules.

When viewing the Outlook VBA project window (see Figure 1 below), you’ll notice a special VBA class module specific to Outlook called “ThisOutlookSession”. When programming VBA within Outlook, the “ThisOutlookSession” class module can be used to tap into the Outlook event model. If you’re not familiar with Office VBA coding, Office follows an event-driven programming model. “Events” are specified areas where a developer can place code that will be automatically called in response to certain incidents such as when Outlook starts, an e-mail is received, an e-mail is sent, etc. You can see an Outlook Object Model reference here.

Figure 1: Outlook VBA projects have a special class module called ThisOutlookSession.

The code you create within Outlook will be stored in a special file called VbaProject.otm. For backup purposes, know that this file is stored within the Windows user profile’s application data subfolder. (Get OS specific information here.) Once you’ve written code you’ll want to make sure you have a backup of this .otm file or at least obtain an export your modules for safe keeping.

The VBA Code

For simplicity, the code example is primarily contained in one subroutine with a secondary subroutine used for initializing the automatic “monitor” for new e-mails. All of this sample code belongs in the “ThisOutlookSession” class module.

The sample code can be downloaded here. This VBA code is documented, but I’ll briefly explain a few of the highlights.

First, the class module starts with this class level object declaration:

Private WithEvents olInboxItems As Items

This statement declares object variable “olInboxItems” as an Items collection. The Items collection is an Outlook specific class that can be used to reference any group of Outlook “items,” including mail, notes, meetings, tasks, etc., that are contained in a given Outlook folder (such as Inbox, Sent Items, Outbox, etc.). The “WithEvents” keyword signifies that the class module can define code for any of the events triggered by the Items collection. The Items class offers the following important events: ItemAdd, ItemChange, and ItemRemove. As you’ll see in a minute, the code will take advantage of the ItemAdd event, which will be made to execute when an item is received in the Inbox.

The next thing to consider is a special event called “Application_Startup”. Any code in this subroutine will be called automatically when Outlook starts. In this example, the only thing to do when Outlook starts is to make the “olInboxItems” variable reference the Inbox folder:

Private Sub Application_Startup()
    Set olInboxItems = _ 
        Session.GetDefaultFolder(olFolderInbox).Items
End Sub

When Outlook starts, this statement creates the “monitor” of the Inbox. (Note that any Inbox subfolders will not be monitored by this statement.) olInboxItems is told to reference the items in the Inbox folder. Because it was declared using WithEvents, you can use its ItemAdd event to do the necessary processing when an e-mail comes in. With event driven programming, Outlook will do most of the work–you just need to fill in the missing pieces to do what you want when the event occurs.

The subroutine that will do all of the “real work” will be called when the ItemAdd event of the Inbox is triggered. The subroutine definition is shown here:

Private Sub olInboxItems_ItemAdd(ByVal Item As Object)

When this event is fired, Outlook will pass a reference to the item just added to the Inbox; it could be an e-mail, a note, an appointment, etc. For the Inbox, usually this event is fired when new mail is received, but it can also be fired by simply moving an item from one folder to another, such as moving an item from “Deleted Items” back to the Inbox.

When the item is received, first make sure it’s an e-mail and not a note or a meeting request. Then make sure the newly arrived e-mail has one attachment and has a specific subject line before attempting to automatically process it:

If TypeOf Item Is MailItem Then
    Set olMailItem = Item
    If olMailItem.Attachments.Count = 1 _
    And InStr(olMailItem.Subject, "My Import") > 0 Then

In a more elaborate scenario, you may also want to enhance the filter to check things like attachment type, sender name, and even received date, just to make sure someone didn’t drag an old e-mail back to the Inbox.

Once the mail item “passes” the validity test, it is saved in the C:tmp folder. For the record, this attachment is assumed to be a comma delimited file without headers:

strAttachmentName = olMailItem.Attachments.Item(1).FileName
olMailItem.Attachments.Item(1).SaveAsFile "c:tmp" + strAttachmentName

The code that follows (not shown here) uses the ADO library to open a table (physical file) on the IBM i. It then opens and loops through the text file, parses the row data into columns, and sends the data to DB2 for i. To keep things uncomplicated, it is assumed that the work table columns in DB2 match the fields in the .csv attachment file in number of data columns and data types. In a more sophisticated scenario, we could also kick off a stored procedure or i/OS command to process the data that was just uploaded. Remember, to use the code sample as-is, you’ll need to add the ActiveX Data Objects library to your project’s references, which can be found in the VBA IDE choose Tools→References.

Finally, once the attachment has been copied to DB2, the Move method of the olMailItem object is used to automatically move the message out of the Inbox to an Inbox subfolder:

olMailItem.Move olDestFolder

olDestFolder is a Folder reference that is set in the code to point to a pre-existing subfolder called Processed_OK unless an error is encountered. When an error occurs, olDestFolder is set by code to reference a pre-existing subfolder called Processed_Errors. Pretty simple stuff!

If you know VBA, programming Outlook is fairly simple–it just takes a bit to learn the object model and event model. The Microsoft Outlook documentation and a site called OutlookCode.com were all I needed to get started.

A Few Pitfalls

I should mention a couple of disappointing things: there is no easy way to set the status bar or the hourglass (which are relatively easy to control in Excel and Access) to alert the user to what’s happening. You can however, add the Microsoft Forms library to your Outlook project references and then create a status form similar to how you would create an Access or Excel form. However, this is more work than I want to do for such a simple task.

Also, while testing startup related code, you may find yourself needing to shut down and re-restart Outlook. In some cases Outlook has been known to hang around in the Windows background after being closed. Once you shut it down, check the Windows task manager to make sure Outlook.exe doesn’t appear in the list of active processes.

For those using an older version of Outlook, the following declaration in Outlook 2007:

Dim olDestFolder As Folder

will need to be changed to:

Dim olDestFolder As MAPIFolder

Remove the Security Noose

Because of vulnerabilities in receiving e-mails and the potential nefarious uses of VBA code (and Microsoft’s reputation for security issues), recent Outlook versions default the macro security setting to ignore VBA code. This restrictive setting can be overridden by choosing Tools→Macro→Security and then selecting “Warnings for all macros” or “No warnings for macros.” In earlier versions of Office these levels were labeled High, Medium, and Low. If you choose “Warnings for all macros” (a.k.a. medium) and add VBA code, you’ll receive this message every time you start Outlook:

Figure 2: Outlook Macro security “warning”.

If you do not change this setting your VBA code will not run!

The Benefit

The Outlook object and event model is full featured and can be used to do myriad tasks including:

• sending e-mails
• tracking when a specific e-mail was sent
• automatically forwarding certain messages
• doing something specific with received e-mails and attachments
• automatically adding a reminder/alert to your calendar when, say a shipment or customer repair hasn’t completed in time

The possibilities are endless. Further, the Outlook model can be referenced by code outside of Outlook including VBScript (with some limitations), .NET, or from within another ActiveX/COM-based application. Microsoft Office is a great suite and you paid good money for it, so use it to its full potential.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED RESOURCES

Office 2007: Outlook Object Model Reference

Office 2007: Outlook Events

Writing VBA Code for Microsoft Outlook

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot