Processing Incoming E-mails with Macros

Last reviewed on July 24, 2014   —  10 comments

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)

Dim objEmail As Outlook.MailItem
'Ensure we are only working with e-mail items
If Item.Class<> OlItemType.olMailItem Then Exit Sub

Debug.Print "Message subject: " & objEmail.Subject
Debug.Print "Message sender: " & objEmail.SenderName &" (" & objEmail.SenderEmailAddress & ")";
Set objEmail = Nothing
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.

This is documented in this KB article:
OL2002: ItemAdd Event Doesn’t Run in Some Scenarios

However, you can get around this if you use Outlook 2003. 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 objEmail As Outlook.MailItem
Dim strIDs() As String
Dim intX As Integer
strIDs = Split(EntryIDCollection, ",")
For intX = 0 To UBound(strIDs)
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

Written by

Eric Legault
An Outlook developer MVP since 2003, Eric has completed several dozen projects using the Outlook/Exchange/SharePoint platforms, from COM Add-Ins to custom Forms, desktop applications and Windows Service applications. When not working, Eric keeps busy building relationships with technical communities via social media and by contributing to online support forums, writing technical articles and editing technical books and speaking at conferences around the world on Outlook and Office development. Eric Legault+

Please post long or more complicated questions at Outlookforums.

10 responses to “Processing Incoming E-mails with Macros”

  1. G

    this works great thank you for posting =)

    however it crashes for me after a few emails have been received. if i restart outlook it works again but only to crash again after a few emails have been received.

    any ideas?

    thanks again for your help

  2. G

    actually to further add to my comment - the error i get is

    Object variable or With block variable not set (Error 91)

    1. Diane Poremsky

      Which macro are you using?

  3. G
    1. Diane Poremsky

      There are 3 there. :) The first sample has two Next statements but only 1 For line.

      Assuming the itemadd macro, you need three blocks of code (I know, its confusing the way the article is written) and they go into ThisOutlookSession. Click in Application_Startup and press the Run button then send a message to the account. It doesn't do anything useful as written - if it works, the subject, sender name and address are written to the immediate windows (View > Immediate window to see). Replace the debug.print with code that does something.

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

      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

      Private Sub objNewMailItems_ItemAdd(ByVal Item As Object)

      Dim objEmail As Outlook.MailItem
      'Ensure we are only working with e-mail items
      If Item.Class<> OlItemType.olMailItem Then Exit Sub

      Debug.Print "Message subject: " & objEmail.Subject
      Debug.Print "Message sender: " & objEmail.SenderName &" (" & objEmail.SenderEmailAddress & ")";
      Set objEmail = Nothing
      End Sub

  4. G

    here is the script in its entirety - thank you for your help - much much appreciated =)

    Option Explicit
    Private objNS As Outlook.NameSpace
    Private WithEvents objNewMailItems As Outlook.Items
    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
    Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
    Dim objEmail As Outlook.MailItem
    Dim strIDs() As String
    Dim intX As Integer
    Dim Args As String

    strIDs = Split(EntryIDCollection, ",")
    For intX = 0 To UBound(strIDs)
    Set objEmail = objNS.GetItemFromID(strIDs(intX))

    If objEmail.SenderEmailType = "EX" Then
    If objEmail.SenderName = Application.GetNamespace("MAPI").CurrentUser Then
    If objEmail.Subject = "Magenta" Then
    objEmail.BodyFormat = olFormatPlain
    objEmail.Save

    Args = objEmail.Body
    objEmail.Delete
    Process_Args Args
    End If
    End If
    End If
    Next

    Set objEmail = Nothing

    End Sub
    Private Sub Process_Args(Args As String)

    Dim strPath As String
    Dim dash_count As Integer
    Dim WshShell As Object
    Dim sBody As Variant

    On Error GoTo ErrHandler:

    Set WshShell = CreateObject("WScript.Shell")
    strPath = WshShell.RegRead("HKLM\Software\Wow6432Node\Magenta\ScriptDir")

    strPath = strPath & "\Magenta.exe"

    If Dir(strPath) = "" Then
    SendMessage "File Path Does Not Exist:" & vbCrLf & strPath, False
    Exit Sub
    End If

    sBody = Split(Args, vbCrLf)
    Args = sBody(0)

    Args = Trim(Args)

    dash_count = Len(Args) - Len(Replace(Args, "/", ""))

    If dash_count 2 Then
    Shell strPath & " /help " & Args
    Exit Sub
    End If

    If InStr(Args, "/A") = 0 And InStr(Args, "/U") = 0 And InStr(Args, "/C") = 0 Then
    Shell strPath & " /help " & Args
    Exit Sub
    End If

    Shell strPath & " " & Args

    ErrHandler:
    'MsgBox (Err.Number)
    If Err.Number = -2147024894 Then
    SendMessage "Magenta has not been run on this PC:" & vbCrLf & Environ$("computername"), False
    End If

    End Sub
    Sub SendMessage(Message As String, DisplayMsg As Boolean, Optional AttachmentPath)

    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment

    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
    ' Add the To recipient(s) to the message.
    Set objOutlookRecip = .Recipients.Add(Application.GetNamespace("MAPI").CurrentUser)
    objOutlookRecip.Type = olTo

    ' Set the Subject, Body, and Importance of the message.
    .Subject = "Magenta Error"
    .Body = Message
    .Importance = olImportanceHigh 'High importance

    ' Add attachments to the message.
    If Not IsMissing(AttachmentPath) Then
    Set objOutlookAttach = .Attachments.Add(AttachmentPath)
    End If

    ' Resolve each Recipient's name.
    For Each objOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    Next

    ' Should we display the message before sending?
    If DisplayMsg Then
    .Display
    Else
    .Save
    .Send
    End If
    End With
    Set objOutlook = Nothing

    End Sub

  5. G

    hmm could it be because im deleting the email?

    i added this after the delete

    objEmail.Delete

    Set objEmail = Nothing

    and commented this after the for loop for now

    Set objEmail = Nothing

    1. Diane Poremsky

      Possibly - but unless you call the objEmail later, it shouldn't matter that it's not released until the end - but if it works, I could be wrong.

  6. G

    i dont call it later - well it gets called when new emails come in again i guess.

    hmm the process_args function has a few exit subs.. would that skip out set objemail = nothing if i left it outside the for loop?

    releasing it after the delete seems to be working so far... no errors yet.. but i aint holding my breathe!

  7. G

    still no crashes - that was it.. thanks for your help!!

Leave a Reply

If the Post Coment button disappears, press your Tab key.