All posts by Paulie

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

It can be very useful torun 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.

How to find the startup folder on Windows Server 2012 or 2016

It’s always been simple to find the startup folder in most versions of Windows to configure an application to start automatically when a user logs in.

Here is how to find the startup folder on Windows Server 2012 and Windows Server 2016

  • Right Click on the start menu and choose run
  • Type “shell:startup” and click ok.The run dialog box showing how to access the startup folder on Windows Server 2012 and Windows Server 2016
  • Then the startup folder will appear and you can drop shortcuts or applications into it.

Easy but quite well hidden.