Processing Incoming E-mails with Macros

The Rules Wizard is great for a lot of things when automatically processing incoming e-mails. However, when there's that one certain thing that you can't do with it, you can always be the Wizard yourself and write your own rules with VBA. However, one of the greatest challenges for developers programming with Outlook is learning how to effectively hook into application events. While it is relatively easy to gain access to objects on the fly, it is not entirely obvious where, when or how these objects should be managed. If Outlook automation was anything like most object models, it would be very straightforward.
Imagine this "fantasy" code:

Sub WorkWithNewMail()

Dim objOutlook As Outlook.Application
Dim objAllNewMail As Outlook.Items
Dim objMyEmail As Outlook.MailItem
Set objOutlook = New Outlook.Application
Set objAllNewMail = objOutlook.NewMail
For Each objMyEmail In objAllNewMail
'Do something with every e-mail received
Next
End Sub

Wouldn't this make things easier! Unfortunately, there is no magical NewMail collection. You have to build it, and hook into it at the proper time. What is essential is instantiating the necessary objects when Outlook starts. To begin, open the Visual Basic Editor (ALT+F11) and open the ThisOutlookSession module from the Project Explorer window. The first code that we need to add are module level variables that we'll declare in the general declarations section of the module at the top:

Option Explicit
Private objNS As Outlook.NameSpace
Private WithEvents objNewMailItems As Outlook.Items

The most important object in this example is objNewMailItems, as we'll soon see. The "WithEvents" statement means we are declaring this object in a way that will allow us to access not only the properties of that object, but also the events that the object exposes.

Now, we have to hook these variables up. The ThisOutlookSession module is special compared to the regular modules that you usually insert into a VBA project in Outlook - it has a "built-in" Application object variable already declared. This way you don't have to add a "Private WithEvents objApp As Outlook.Application" line or something similar in the general declarations section of the module. With that in mind, add this procedure:

Private Sub Application_Startup()
Dim objMyInbox As Outlook.MAPIFolder

Set objNS = Application.GetNamespace("MAPI")
Set objMyInbox = objNS.GetDefaultFolder(olFolderInbox)
Set objNewMailItems = objMyInbox.Items
Set objMyInbox = Nothing
End Sub

This is essentially where it all begins. As the name of the Application_Startup event indicates, this loads when Outlook launches and is essential to ensure that we gain access to e-mails delivered to the Inbox. This is done by hooking up an event aware procedure tied to the Items collection that we retrieve from the MAPIFolder object we set from the Inbox folder. The event where all the processing on incoming e-mails occurs will be here:

Private Sub objNewMailItems_ItemAdd(ByVal Item As Object)
'Ensure we are only working with e-mail items
If Item.Class <> olMail Then Exit Sub

Debug.Print "Message subject: " & Item.Subject
Debug.Print "Message sender: " & Item.SenderName &" (" & Item.SenderEmailAddress & ")";
End Sub

And that's really all there is to it! The Item object is checked to ensure that it is a MailItem object before we work with it any further.

Once it is validated, we can work with all the properties and methods of the MailItem object to do whatever we want with it. Just printing out the subject line and sender information to the Debug window like the example above is pretty boring, but there are all kinds of possibilities using code to work with e-mails in ways that the Rules Wizard can't handle:

- write e-mail info to a database
- automatically save attachments to the file system
- lookup the sender's Contact item and start a Word mail merge using their mailing address
- parse the message body for line items to be added to a spreadsheet

Those are just a few examples, but as long as whatever you want to do has an Object Model it can be done - you don't just have to automate Outlook or other Office applications.

There are a few caveats to mention though. If a large number of items are added to a folder at that same time, the ItemAdd event may not fire.

However, you can get around this if you use Outlook 2003 or newer. The NewMailEx event provides a list of all the unique EntryID values for e-mails that were delivered during the last Send/Receive cycle. These values can be used to retrieve each e-mail individually as in the ItemAdd event by using the NameSpace GetItemFromID method.

Private Sub Application_NewMailEx(ByVal EntryIDCollection As
String)
Dim objNS As Outlook.NameSpace
Dim objEmail As Outlook.MailItem
Dim strIDs() As String
Dim intX As Integer
strIDs = Split(EntryIDCollection, ",")
For intX = 0 To UBound(strIDs)
Set objNS = Application.GetNamespace("MAPI")
Set objEmail = objNS.GetItemFromID(strIDs(intX))
Debug.Print "Message subject: " & objEmail.Subject
Debug.Print "Message sender:" & objEmail.SenderName &" (" & objEmail.SenderEmailAddress & ")"
Next
Set objEmail = Nothing
End Sub

Note: The Outlook 2003 VBA help file seems to indicate that NewMailEx only works with Exchange Server mailboxes. This is not true - try it and see with POP or IMAP accounts.

Finally, don't expect this code to process e-mails the way server-side based rules do. Outlook of course has to be running for your code rules to work. For requirements where e-mail needs to be processed 24/7, see the Exchange SDK for information on building Event Sinks that run on the server.

To take this example further, there may be situations where you need to interact with e-mails that are opened rather than received. This involves a different approach and is explained in this article: Getting a Handle on Your E-mails with VBA

How to Use VBA

Copy and paste the code from this page into your ThisOutlookSession project. To do this, click within the code, Select All using Ctrl+A, Ctrl+C to copy.

In Outlook, press Alt+F11 to open the VBA editor and expand Microsoft Outlook Objects then double click on ThisOutlookSession to open it in the editing pane and Ctrl+P to paste the code.

For more detailed instructions and screenshots, see How to use Outlook's VBA Editor