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.
Luis says
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?
Paulie says
That is what it does, as long as outlook is open, new emails should be processed
Dan says
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.
Chris says
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?
Carolina says
This works only few times. I dont know events stop works randomly when outlook is open for days.
Paulie says
I’ve had it running in one instance for about 6 months and it’s still going. Must be something else going on.
Carolina says
I have office 2013 on windows server.
What do you have? Sorry for my english.
Paulie says
Office 2016 on Windows 10
Luigi Ferreli says
Hi Paule, would be possible to do the same task but only for the email with a particular subject?
Thank you 🙂
Paulie says
Yes, just use an if statement on “Item.Subject” – that is exactly how I use it.
Nikana says
Hi Paulie, I followed your step but it did not work when the new email is received. I am using Window 07 Office 10, and there are two accounts (gmail and outlook) in my MS outlook.
Paulie says
Hi, not sure why it wouldn’t work, I’ve tried it on a good number of machines. Did you restart Outlook?
Jon Garcia says
Not sure if anyone suggested this avenue, but I found this article talking about using “Rules” to automatically run a script. I believe this is for Outlook 2013 [That’s the version I am running]. I can’t paste code because it has alot of pictures, and instructions that I cannot paste.
https://www.extendoffice.com/documents/outlook/3747-outlook-auto-download-save-attachments-to-folder.html
Logan says
If you’re on Office 365 (Outlook 2016), Outlook will not execute the macro by default because of the Trust Center settings. Microsoft has Outlook setup for notifications of macros when they ONLY have a digital signature. The general user needs to enable notifications, at the minimum, for ALL macros. Do so as follows:
Go to File (top left of application) -> Options (left side of application) -> Trust Center (left side of the Outlook Options dialog) -> Trust Center Settings (button on the right of dialog) -> Macro Setting (left of the Trust Center dialog) -> Toggle the “Notifications for all macros”
Please don’t hesitate to ask me any questions if you’re unclear on the above.
Hakim says
HI Paulie
i woud like to excute macro when irecieve a new mail in second account (is an commun account), i’m trying with this sollotion but dose’nt work.
Adam says
Brilliant! What If I want to use that script, but not for my basic Inbox? I want that messagebox to appear, when new mail comes to shared mailbox. Where is the reference to the mailbox that the script applies to?
C L Williams says
would like to get this line to change only on word in the subject line — FROM UPDATE to SHIPPED
Nora says
How do you automaticall update a sql table? save the file and use a function like openrowset? save it directly somehow? create an api from a shared file? please help me. i would like to add salesdata where my only source is a excel file that i receive every monday and I would like to have this information in my database.
Best!
Paulie says
Hi Nora,
What you want to do is out of the scope of what this particular article. First of all concentrate on possible methods of connecting to your SQL environment from VBA, and then work from there. You could use an API or connect to your SQL server directly if it is available to you, or via an API. I don’t know enough about your setup to give you any guidance.
Kind Regards
Paul
Matt says
Excellent piece of code 🙂 Thanks!
I have one question. I have two mailboxes in my outlook: my own and one shared between users, two different e-mail addresses. Your code works flawlessly for my inbox, but how to make it work for different mailbox? 🙂
Thanks!
Paulie says
I do not know of a way (and I do not think it is possible). So if you do find a way, I would be very interested to hear it.
MrPfromV says
Thanks, Paul! The source code is very helpful but there is just a question: I get the message only if outlook is on top of all the open tasks. E.g. if I am working in another environment (Word(R)) I can´t receive the message. I just see it if I change to Outlook. Is there any chance to modify?
Thanks in advance and…
…all the best
Reinhard
PS: please excuse my awful english….
Radoslaw says
Hi 🙂
Sorry for my english but I’m learning it still.
How can I cut full-ID-Sender ?? I want to have connected only one, alone person without item below
Because I have “in New Message Received Window Sender: FULL ID Sender
Thx for helping
Paulie says
Sorry – I am not very clear on what you are trying to achieve. Could you try to ask the question in a different way?
Swapnil says
Hi Paulie,
Very nice article, thanks for sharing it.
I am new in VBA macro,I have 1 question/Scenario , I want to pass a received emails subject to a .bat file, so that I can use that subject to perform any action written in my .bat file.
Lets say I want to create a folder with name as emails subject content.
Could you please help me with this.
Thanks in Advance
Raimundo says
Hi Paulie
It’s a great article, it was very useful for me. Exists a way to identify and get if a url link are in Item.Body ?
Thanks a lot. Best regards
Paulie says
You could use a regular expression to do this, although you may struggle to catch every url.
Eduardo says
It was very helpful. Congratulations
Carl Childers says
Great Code! I have been recieving more than 100 un-nice emails a day (no I did not do anything bad). I have found a common message segment, and have coded to remove only the (un)desired messages, but have to run the code manually. By pointing your code to the junk folder, and trimming some code, I run my code each time the new potential junk mail arrives.
Paulie says
Sounds like a neat solution – well done.
Amit Berku says
Hi there,
Thank you very much for sharing this code, I searched by the google and got this link very fast, just what I looked for.
What I have to do is save an excel file that the user gets from a particular source and execute a macro in that excel the do … and save them in SQL server, you solve the first step of complete project
Regards,
Amit
Paulie says
Glad it helped, sounds like a fun project. Might be easier to use Microsoft Flow these days.
Uncle Dick says
Works perfectly as written, HOWEVER…
I want to use this to edit the subject line. Specifically to remove “[External]” from any subject line that has it. Added another line to the output:
“New Subject : “, Replace(Item.Subject,”[External]”,””) & vbCrLf & _
The message box correctly shows the edited subject, but
when I attempt to change the subject, it does not “take”, i.e.
Item.Subject = Replace(Item.Subject,”[External]”,””)
I tried using a temporary variable (Temp), which also prints out correctly, and then
Item.Subject = Temp
but it still does not replace the subject.
Item.Subject is supposed to be Read/Write
Any suggestions?
Paulie says
I will have a look tomorrow and let you know what I come up with.
Paulie says
It’s pretty easy to do, replace the example code above with this:
If TypeName(Item) = "MailItem" ThenIf InStr(Item.Subject, "[External]") Then
Item.Subject = Replace(Item.Subject, "[External]", "")
Item.Save
End If
End If
Nwanda says
How would you modify this script to capture an attachment name and/or save the attachment to a given path?
Paulie says
You would have to iterate through the attachments collection of the mail item and then save them to the file system. Does this get you on the right path or do you need more help?
Nwanda says
Thanks for the quick reply! My largest struggle has been understanding how to reference the attachments. How do I declare and/or refer to attachments? If I could bother you for a sample of how to add the attachment name to the popup, I can figure the rest out from there. Also, do I have to iterate (loop) through attachments when there will always only be one? Assumptions: O365 (No rule to trigger script), Only one attachment per email, and need script to execute on every email as it arrives (will always have Outlook client open).
Paulie says
Add this code after
Result = MsgBox(MessageInfo, vbOKOnly, "New Message Received")If Item.Attachments.Count > 0 Then
Dim AttachInfo As String
Dim objAttach As Outlook.Attachment
For Each objAttach In Item.Attachments
AttachInfo = "" & _
"Filename: " & objAttach.FileName & vbCrLf & _
"Size of: " & objAttach.Size
Result = MsgBox(AttachInfo, vbOKOnly, "Attachment Information")
Next
End If
It should produce you a message box something like this:
I do think it might be worth experimenting with Microsoft Flow/Power Automate to complete your task though.
Nwanda says
Is there a way to include the file name in the message box that is already being created by your original code and not as a separate MsgBox? Can the MsgBox include Sender, Sent, Received, Subject, and Attachment Name?
I agree, Flow/Power Automate would be a better fit and will be the long term solution, but I need a more immediate replacement for a plugin that no longer works because Office 2010+ does not allow code execution/custom actions in rules.
Paulie says
Yes, it is possible. You can rearrange the code you already have in order to do it. I can’t write it all for you 😀
Just be aware attachments are a collection, which is why it is in a loop.
Nwanda says
My apologies… I didnt mean to be such a pain, but the visual of the code would have helped me understand what I was missing. The confirmation that attachments have to be run through a loop is what I was missing.
Your time and help are much appreciated. Thanks!
Arthur says
Hi Pauline,
your code looks cool for me and I use it in ThisOutlookSession. If it works, then works, but sometime doesn’t and I really don’t know, why…
Another Question:
I try to write VBA to save the attachements atomaticly on the disk, it new Mail come with specific Subject in the Inbox. The code is in ThisOutlookSession:
Option Explicit
Private Sub Application_Startup()
Dim outlookApp As Outlook.Application
Dim objectNS As Outlook.NameSpace
Dim saveFolder As String
Set outlookApp = Outlook.Application
Set objectNS = outlookApp.GetNamespace(“MAPI”)
Set inboxItems = objectNS.GetDefaultFolder(olFolderInbox).Items
End Sub
Sub inboxItems_ItemAdd_Save_Attachements_RC(ByVal Item As Object)
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
Dim MessageInfo
Dim Result
Dim outItem As Object
Dim subjectFilter As String
Dim outMailItem As Outlook.MailItem
If TypeName(Item) = “MailItem” And subjectFilter = (“Report: Reopen-Canceled Date “) Then
Set outMailItem = outItem
If InStr(1, outMailItem.subject, “subjectFilter”) > 0 Then
Call Aufrufen_Cancel_Reopen
End If
End If
ExitNewItem:
Exit Sub
ErrorHandler:
MsgBox Err.Number & ” – ” & Err.Description
Resume ExitNewItem
End Sub
Sub Aufrufen_Cancel_Reopen()
Dim Mail As MailItem
Set Mail = Application.ActiveExplorer.Selection(1)
SaveToDiskCancel Mail
End Sub
This Code is in Modul001
Public Sub SaveToDiskCancel(olMail As MailItem)
Dim Pfad As String
Dim Datei As Attachments
Pfad = “G:\TEAM\GRP\Cancel Reopen 2019\”
On Error Resume Next
Set Datei = olMail.Attachments
For I = 1 To Datei.Count
Datei.Item(I).SaveAsFile Pfad & Datei.Item(I).FileName
Next I
End Sub
Code from Modul001 works alone fine. I can start it manually in Outlook via Button an the Atachements are saved to the specifik folder. It would be great to do not have to start this manually. Outlook should execute this Modull001 by arriving a new email with the specifick subject as given above. Any Idea, why it doesn’t work?
Regards,
Arthur
Paul says
If it doesn’t work add a void Application_Quit at the end.
Public Sub Application_Quit()
End Sub
Sergey says
Hi Paulie!
Your post is really useful!
I have two mailboxes in Outlook. I need the code to work with the second mailbox only.
Second mailbox name is “Specification Estimation RU41”
What I did was to replace the line
Set inboxItems = objectNS.GetDefaultFolder(olFolderInbox).Items
with the line
Set inboxItems = objectNS.Folders(“Specification Estimation RU41”) _
.Folders(“Inbox”).Items
It doesn’t throw any error when debugging, however it doesn’ work. What would be the reason for that?
Complete code:
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.Folders(“Specification Estimation RU41”) _
.Folders(“Inbox”).Items
‘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
Jake Alimurong says
Hi Paulie,
I need this code for our project im new to VBA on outlook. if you have time can I have a step by step on how to apply or put it in the outlook. sorry just a newbie here.
Thanks in Advance!
Jake
JIm Duncan says
Ok,
So how do we get this to work if the email inbox is not the person’s primary inbox?
For example, the primary inbox is for [email protected], but the secondary inbox, which Jane Doe has access to, is [email protected]?
Paulie says
I don’t think it is possible, but you could do it with Power Automate instead.
JIm Duncan says
what is power automate?
Paulie says
It’s an automation tool built that is part of the Office 365 Suite, you can do many things with it, it’s pretty cool.
JIm Duncan says
Good, I will take a look at it. Thanks.
Uncle Dick says
My email appears to be hosted on Office 365, but I am using stand-alone Office 2019 and Outlook.exe and not part of the organization domain. Maybe I am incorrect, but it appears that Power Automate isn’t available to me. Certainly a limit of “5 executions” would not be helpful.
Uncle Dick says
New posts on this thread reminded me that my previous effort failed miserably. My organization has begun “protecting users from themselves” by prepending [External] to any message arriving from outside the organization domain. This annoys people who email me because I often forget to delete that tag when doing a “reply”.
Using Outlook 2019, I have this “Rule”:
Apply this rule after the message arrives
with [External] in the subject
and on this computer only
run StripExternal.ThisOutlookSession.EraseExternal
And this Visual Basic Macro:
StripExternal(VbaProject)
Microsoft Outlook Ojects
ThisOutlookSession
Public Sub EraseExternal(Item As Outlook.MailItem)
Item.Subject = Replace(Item.Subject, “[External] “, “”)
Item.Save
End Sub
Doesn’t work.
Uncle Dick says
(Doh) As soon as I posted that message, it DID work for three messages in a row. SORRY.
(But I do not understand, and fear that it will stop working.)
“My Bad”
Rich P says
This codes only works for me when I start Outlook and a new (unread) email is processed. Any emails after that are not making it to the inbox – unless I restart Outlook. Any idea why this is happening?
Uncle Dick says
My macro to remove “[External]” from new message subjects is inconsistent. It will work for several messages, then miss one, then catch some more, then miss another. For example, Amazon offered a bunch of eBooks for free today, so I purchased a bunch to see if I like the author. (Amazon’s point in the first place.) I got these message subjects:
Your Amazon.com order #112-8848734-1661036 has shipped
Amazon.com order of Polar Bear Dawn: A Detective Bernadette Callahan Mystery
Amazon.com order of Bad To The Bones: An Evan Buckley Crime Thriller (Evan Buckley
Amazon.com order of Snapped: A Gripping FBI Thriller (An Agent Jade Monroe FBI T
Amazon.com order of Primary Target: The Forging of Luke Stone-Book #1 (an A
[External] Amazon.com order of Sniper’s Nest: A Gripping Vigilante Justice Thr
Amazon.com order of Agent Zero (An Agent Zero Spy Thriller-Book #1)
Amazon.com order of The Grave Man – A Sam Prichard Mystery.
Amazon.com order of The Last Teacher: A Stand-Alone Mackenzie Mystery.
Amazon.com order of Any Means Necessary (a Luke Stone Thriller-Book #1).
Amazon.com order of Never Say Spy (The Never Say Spy Series Book 1).
[External] Amazon.com order of An Ace and A Pair (A Dead Cold Mystery Boo
At first, I thought maybe the Email on my Android phone had grabbed those two before my desktop Outlook with the Visual Basic could process it. But my cell phone is set to sync every 15 minutes and these messages arrived within a span of only 7 minutes. The cell phone might have caught one, but should not have caught both.
Suman Panigrahi says
Hi Paulie,
Sorry for going out of context. I need a VBA Macro which should give me an notification whenever the recipient read my email for the first time. its kind of read receipt. But there should not be any pop-up window asking the recipient to provide click “Yes” or “No” button, how currently we have in MS outlook.
I don’t want for all my emails. I want for certain emails only. I have macro to sent those emails , but even i need to throw me an notification when recipient reads it. so i can include the codes into my macro.
System: windows 10, MS office 13
Santosh says
Hello Paul,
can a VBA code be written to notify (send out emails to a distribution group) letting them know that there are X number of unread emails in a particular mailbox. i dont want a message notification box, instead it needs to send out emails to a distribution group. is this possible sir?
Nacht says
For me (Outlook subscription, latest May 2020 update) it does not work.
It compiles fine, but it does not enter the event routine.
Interestingly, newMail event gets fired instead.
Nacht says
I figured out why it didn’t run: I forgot to include Private WithEvents inboxItems As Outlook.Items.
Reason: I already had some VBA code running and only inserted the method.
Tim Hough says
For those wishing to specify the account you wish to monitor the inbox for you can do this:
Private Sub Application_Startup()
Dim outlookApp As Outlook.Application
Dim objectNS As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Set outlookApp = Outlook.Application
Set objectNS = outlookApp.GetNamespace(“MAPI”)
Set objFolder = objectNS.Folders(“[email protected]”).Folders(“Inbox”)
Set inboxItems = objFolder.Items
End Sub
where [email protected] is the account you wish to monitor
Paulie says
That’s handy – thanks!
Michael Hober says
I cut and pasted the code as typed out but it does nothing when a new email arrives. I saved the vba code and exited and opened outlook but still didn’t function. Any idea why that would be?
Wil Heeren says
Hello
Nice macro but here is what i would like to do.
I have several email accounts but for 1 particular account i would like to when a new email comes into that account either when outlook 2010 is open or when i open it.
1) open the email
2) reply to
3) insert an oft
4) send
5) close the email
If this is at all possible ?
Paulie says
It probably is possible. But I don’t know, I haven’t used Outlook 2010 in about 10 years. I don’t know what your objective is but consider checking out Microsoft Power Automate for a more robust and easier to build solution.
ram says
Very helpful.. Thank you
Stephen Allen says
I tried the following and I am not getting an error however; the emails sent from gmail are not showing up in the inbox or anywhere if this is running.
The category also doesn’t get set.
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
If InStr(Item.SenderEmailAddress, “@gmail.com”) Then
Item.Categories = “Test”
Item.Save
Else: GoTo ExitNewItem
End If
End If
ExitNewItem:
Exit Sub
ErrorHandler:
MsgBox Err.Number & ” – ” & Err.Description
Resume ExitNewItem
End Sub
Michael W says
Paul, thank you SO much for this article. Unfortunately, it’s not working for me. I am using Outlook 2007 (I know out of date) and your code doesn’t present any errors but it doesn’t work either. When I send test email from a gmail account, I receive the email but no MsgBox appears either. It’s like the incoming email isn’t triggering the code to run. Any advice? Thank you!
Aizat says
Hi Paulie,
How to run the macro for multiple folders
Alejandro says
Wow, it works perfecly …! You are the best Paulie …!
Jean says
Hello Paulie,
Thanks for the code, what is the purpose of the “Msg” variable in the second sub defined as Outlook.MailITem ? you don’t use it in your code
Thanks !
Mike says
You can access the shared mailbox if it is added like outlined in this link: https://support.microsoft.com/en-us/topic/add-rules-to-a-shared-mailbox-b0963400-2a51-4c64-afc7-b816d737d164
Once it is added like that, it is accessible in the namespace object. I used the below to access my shared inbox.
Set inboxItems = objectNS.Folders.Item(2).Folders.Item(2).Items
You can use the debugger to drill down into the namespace object to see which indexes to use.
Hope this helps.
Paulie says
Great tip, thank you!
Andrew says
The code works great! But when building out, I tried having the code call another sub instead of create the message and I got a “438 object doesn’t support this property or method”. The other sub opens up a excel file if the email sender is from a specific email ( the if statement is in your sub and works, just doesn’t allow me to call the other sub). Any thoughts?
Gaetan Doger de Speville says
Thank you a million times. You have taken me directly to the point without having to read through poorly written instructions. I would give you 10/10.
Vic says
Thanks a lot for the code.
I am trying to figure out a way to trigger different modules based on different subject lines. Goal is to forward email without a FW in subject line and ‘forwarding’ header in the body of the mail. For that I use outlook rule which runs a small script
**
Sub SendNew(Item As Outlook.MailItem)
Dim objMsg As MailItem
Set objMsg = Application.CreateItem(olMailItem)
objMsg.Body = Item.Body
objMsg.Subject = “FW: ” & Item.Subject
objMsg.Recipients.Add “[email protected]”
objMsg.Send
End Sub
**
problem is, I want for different names in the subject line to trigger different module which in return send to different email addresses. It appears that VBA accepts only one project and I cannot figure out a way to select various modules in ‘rules’ it offers only 1 script.
Tamás says
Hi Paulie,
I would like to use your code, I copied it according to what can be found at the beginning of the post.
When Outlook is opened, the Private Sub Application_Startup procedure is executed, but when a new mail arrives, the Sub inboxItems_ItemAdd(ByVal Item As Object) event is not executed.
Running the macro is enabled in the Settings menu.
Outlook receives and sends email.
What could be the cause of the error?
Thank you in advance for your reply.
Mark Schultz says
How would this code be adjusted to use a second email account within outlook, say, [email protected]?
Norma Cruz says
I would like to use your code, but would it be possible to use it if I have several email accounts configured? That it gives me the information regardless of which account the email arrives at?