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 an email produced 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.
If you found this post helpful, I’d really appreciate it if you would rate it 😀
Let me know in the comments section if you have any questions.
