How to run a VBA macro when new mail is received in Outlook

It can be very useful to run a VBA macro when new mail is received in Outlook. A customer asked me to write something that would log an entry to a SQL database when a contact form was received.

It’s easy to do but can take a bit of trial and error to get working just how you want it.

You need to add an event listener to the Inbox which will process incoming messages. A the following code to ThisOutlookSession:

Option Explicit
Private WithEvents inboxItems As Outlook.Items

Private Sub Application_Startup()
  Dim outlookApp As Outlook.Application
  Dim objectNS As Outlook.NameSpace
  
  Set outlookApp = Outlook.Application
  Set objectNS = outlookApp.GetNamespace("MAPI")
  Set inboxItems = objectNS.GetDefaultFolder(olFolderInbox).Items
End Sub

Private Sub inboxItems_ItemAdd(ByVal Item As Object)
On Error GoTo ErrorHandler

Dim Msg As Outlook.MailItem
Dim MessageInfo
Dim Result
If TypeName(Item) = "MailItem" Then
    MessageInfo = "" & _
        "Sender : " & Item.SenderEmailAddress & vbCrLf & _
        "Sent : " & Item.SentOn & vbCrLf & _
        "Received : " & Item.ReceivedTime & vbCrLf & _
        "Subject : " & Item.Subject & vbCrLf & _
        "Size : " & Item.Size & vbCrLf & _
        "Message Body : " & vbCrLf & Item.Body
    Result = MsgBox(MessageInfo, vbOKOnly, "New Message Received")
End If

ExitNewItem:
    Exit Sub

ErrorHandler:
    MsgBox Err.Number & " - " & Err.Description
    Resume ExitNewItem
End Sub

Screenshot showing the location of where to paste code into ThisOutlookSession to trigger a macro when new mail is received.

You need to restart Outlook for the code to become active.

The above code will produce a simple message box that shows some of the message properties:

Screenshot of VBA Macro being triggered when new mail is received in Outlook

You can of course do whatever you like with the message when it is received. I used it to insert rows into a SQL table, then move the message to a different folder. It works very well.

It’s worth taking a look at all of the available properties of the Outlook mailitem that are available:

https://msdn.microsoft.com/en-gb/library/microsoft.office.interop.outlook.mailitem_properties.aspx

As this customer is on Office 365 I might investigate the possibility of replicating this functionality with Microsoft Flow.

9 thoughts on “How to run a VBA macro when new mail is received in Outlook

  1. Thank you!

    But when your Outlook is not open and when you open it, the code do not run. Is there a way to run the code when you have new emails and you just open Outlook?

  2. Awesome!!!
    I used to set the Rule to run my script in Office 2007.
    Office 365 Outlook turned off the Run macro.
    Thanks a lot for the event.

  3. Instead of creating an Outlook.Items with events and setting it up at startup, couldn’t you just put the code in the function “NewMail”, in Application?

  4. This works only few times. I dont know events stop works randomly when outlook is open for days.

Leave a Reply

Your email address will not be published. Required fields are marked *