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
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:
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:
As this customer is on Office 365 I might investigate the possibility of replicating this functionality with Microsoft Flow.