How to use Outlook's VBA Editor

Last reviewed on February 22, 2015   —  128 comments

The steps are the same for all versions of Outlook and all Office applications

It's easy to find code samples on the Internet, but unless you know how to use them, they aren’t going to do you much good.

We're going to assume the code is 100% ready-to-use, not a code snippet that is half complete.

If the code will be run against items in your mailbox or personal folders, you should make a backup of the mailbox or data file, just in case something goes wrong with the code. If the code runs against a specific folder, copying the folder and contents may be enough "insurance", (You should have routine backups regardless, but I know many users don't bother.) At least very least, make a copy of the items in the folder you are running the code against.

Check Macro Security Level

Enable Macros in Outlook's Trust CenterBefore you begin, you'll need to check your macro security setting, otherwise, you'll need to use selfcert.exe to sign your macros to test them.

Outlook 2007 and older: Access the dialog to change the security level from Tools, Macros, Security.

Outlook 2010 and up: File, Options, Trust Center, Trust Center Settings, Macro Security.

It’s highly recommended that you set the Macro Security level to only allow digitally signed macros. Do not choose the Low option (run all, never ask), except during testing. Note that some security software will set it to High and your macros will not run. You may need to restart Outlook after changing the security setting.

Open the VBA Editor

Now you are ready to open the VBA Editor. Press Alt+F11 on your keyboard, or if you are showing the Developer ribbon, click theVisual Basic Editor command to open it. In Outlook 2007 and older, the command in on the Tools, Macros menu.

Visual Basic Editor If your screen does not resemble the screenshot on the right, with the white editing pane open, you'll need to click on Microsoft Outlook Objects to expand it then double click on ThisOutlookSession to open it in the editing pane on the right side.

To add a module to the VBA project, right click on Project1 and choose Insert > Module to insert a new module.

Generally speaking, you can put the code into either ThisOutlookSession or a Module, unless otherwise noted that it must be in ThisOutlookSession or a module.

Application_Start macros must be in ThisOutlookSession.


Starting out with Outlook Macros Video Tutorial

This video tutorial shows you have to change the macro security settings, add a macro to Outlook, run the macro, and then create a digital signature using SelfCert.exe to sign the macro before increasing macro security.

Run the Macro

Click the button to run the macroPress F5 or the Run button to run the macro from the VBA editor. (It's highly recommended you make a backup of the folders or message store before running macros.)

Ctrl+Break will pause the macro, or press the Stop button on the toolbar to end it.

If you will be using the macro often, you should use selfcert to sign it then set Macro Security to only allow signed macros.

Don't sign the macro until you are done tweaking it and if you tweak it later, you'll need to re-sign it.

To run the macro from Outlook 2007 or older, go to Tools, Macro, Macros and select the macro. In Outlook 2010, you need to show the Developer ribbon first (File, Options, Customize Ribbon – check Developer ribbon on the right). You can also open the Macros dialog using Alt+F8 (all versions). You can also add macros to toolbar or ribbon buttons.


Using SelfCert to sign a macro

Self-sign macros

To sign a macro using selfcert, you first need to create a certificate. You can find selfcert in the folder with your Office files. In Outlook 2010 and older (as well as Outlook 2013 in volume licensing), selfcert is usually at C:\Program Files\Microsoft Office\OfficeXX where XX is your version of Office.

In Outlook 2013 “click to run”, users will find selfcert at C:\Program Files\Microsoft Office 15\root\office15, or in C:\Program Files (x86)\Microsoft Office 15\root\office15 if using 32bit Office on 64-bit Windows.

With any Office version, if you use 64-bit Windows and 32-bit Outlook, selfcert will be in the 32-bit Programs directory.

It's simple to use: just type a name for your digital certificate then click ok.

Once you have the certificate, sign the macros using Tools, Digital Signature dialog and choosing a certificate. When you close Outlook, if you are asked to save the VBA project, click Yes, even if you saved it from the VBA editor.

Note that every time you edit the macro you'll need to resign it.

Add Macros to the toolbar or ribbon

If you are going to be using a macro often, you will probably want to create a toolbar button for it.

Customize Outlook 2010 and 2013 ribbonIn Outlook 2010 and Outlook 2013, you can add buttons to the QAT or the ribbon. Go to File, Options and choose Customize Ribbon or Quick Access Toolbar.

You’ll need to add a new Group to an existing ribbon tab or add a new tab and new group before selecting Macros from the Choose commands from menu. Select the macro and click Add to add it to the selected group in the right pane.

In Outlook 2007 ribbons: you can only add buttons to the QAT. Right click on a ribbon tab and choose Customize Quick Access Toolbar. Select Macros from the Choose commands from menu then select the macro and Add it to the right pane. (When you add buttons to the QAT you can use Alt+n keyboard shortcuts.)

Add macros to the toolbar for easy accessIn Outlook 2007 (main interface) and older versions of Outlook: right click on the toolbar area and choose Customize. Switch to the Commands tab and find Macros on the left.

Drag the macro from the right side to the toolbar and drop. Right click on it to edit the name and button image.

More Information

Need help with sample code? Try the forums at or the Microsoft programming forums at MSDN

Looking for sample code? Outlookcode and have a large number of code samples.

About Diane Poremsky

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Please post long or more complicated questions at Outlook forums by

128 responses to “How to use Outlook's VBA Editor”

  1. john

    hello Diane,
    please can you give me a code that automatically attaches files to out-going emails?

    1. Diane Poremsky

      I'll have to see if I have a code sample that does that around here...

    2. Jack

      Dim OL As Object, MailSendItem As Object
      Dim sAttachment As String
      Dim strFrom As String

      Set myItem = myOlApp.CreateItemFromTemplate("Path")


      sAttachment = OpenPath & LatestFileToAct 'Path of the file to attach

      With myItem
      .Subject = "Subject informatoin"
      .Attachments.Add sAttachment
      .To = ""
      .Cc = ""
      .Bcc = ""
      .Body = strbody
      End With

  2. sandy ingram

    Hi Diane,
    Thanks for your macro info.
    I have a customer who wants to be able to click a button from within his outlook client 2010 when he replies to a mail that came from a source that can only send plain text, like a BlackBerry device. He wishes to have all of his outbound mails go out in html format and he is looking for a quick way to do that without the few steps of forcing the reply text to switch from plain text to html. I've read some web posts that mention adding macros in VBA and I've enabled the developer tab on my own outlook 2010 and am trying to create a macro but I lack the actual code to go in the macro. Can you direct me to a site that has code capable of converting all text in outbound mails to html before sending?
    Thanks for any help,

    1. Diane Poremsky

      You can use VBA... for this example, the user will need to remember to click the Reply button that this macro is assigned to. Create a button next to the Reply button called "Reply using Plain Text" then use it anytime you want to always reply using plain text.

      Public Sub New_Reply()
      Dim oMail As Outlook.MailItem
      Set oMail = Application.ActiveExplorer.Selection(1).Reply
      oMail.BodyFormat = olFormatPlain
      End Sub

      We can add an if... then statement that includes the address but that is not necessary unless you want to capture the reply event and check every message. I'll see if i can find a code sample.

  3. sandy ingram

    Thanks very much Diane,
    This guy wishes to send every outbound mail in html format (instead of plain text) so can I simply change the code line to : oMail.BodyFormat = olFormatHTML
    ? Excuse my code ignorance, would that work?

    1. Diane Poremsky

      Yes, that would work if you replace the default reply button. For a macro that captures the reply button click, see always reply using html

  4. sandy

    Diane, I never thanked you properly for this. Your code and instructiosn worked perfectly for me, it ended up that my customer could never set it up for himself though as he only ever accessed his outlook via a citrix session which was locked down too tightly. It could only be done in a full outlook client, thanks again, I learnt something. sandy

  5. blaz

    Hi Diane,
    this is what i was looking for. however does not work on my laptop (win8, ms office professional plus 2o1o).
    i did everything exactly as written above (and in the link about macros). when i try to run it a new window opens asking for: select folder. i do select the Contacts folder, i see something is going on (the small circle - ex sandclock), but no result for synched contacts from wp 7.5.
    any ideas what am i doing wrong...
    many thanks

    1. Diane Poremsky

      Which macro are you trying to use?

  6. Nancy

    Hi Diane...That cose is the one I have been wanting... but how do I create the New reply button?

    1. Diane Poremsky

      It depends on your version of Outlook - in Outlook 2010, customize the ribbon to add the Reply macro to the ribbon.

  7. Alex

    This tutorial is missing a very important part. Signed macros are not trusted until certificate is added to the "Trusted Publishers" certificate storage. It can be done from the window where you assign certificate to the VBA project. There's a link to "View Certificate" then in the next window you have to click "Import Certificate" and choose "Trusted Publishers" storage for it.

  8. merium

    i want to get all incoming mails to a specific hidden email id at outlook
    can you please help me out and give me the code of this.

    1. Diane Poremsky

      So you want to send all messages you receive to a new address? You can use a run a script rule. See forward messages using run a script rule.

  9. merium

    but both codes automatially BCC all messages ( and incoming rules are not working simontanously.
    and a atuo bcc all messages is not working in outlook 2003
    kindly help me out

    1. Diane Poremsky

      The first run a script rule should work, if you have VBA enabled.

      Test this - it will display the message instead of sending
      Sub ForwardMessage(mItem As Outlook.MailItem)
      Set myForward = mItem.Forward
      'this puts the name in the To field
      myForward.Recipients.Add ""
      End Sub

    2. Joshua Ragan

      Hi Diane, I am trying to get a code to remove a specific line of text from my signature in the body of the e-mail.

      Can you help me out with this?

    3. Diane Poremsky

      It's probably easiest to use Word code - has the basics. You need to find the selection and backspace over it. will it always be in the same position? Like the last line in an email for example.

  10. mera

    i cant open my vb by pressing ALT+f11 or by going to macro then vb editor kindly tell me what to do. even i could open before but after pastinjg 2 codes i am unable to edit that.

    1. Diane Poremsky

      What happens when you try? At the very least, you can close Outlook, rename the OTM file (paste %appdata%\Microsoft\outlook in the address bar of windows explorer and press enter to jump to the location of the file). You'll lose all macros but it will allow you to open the editor.

  11. mera
  12. mera

    kindly tell me in details whcih file to be rename and wht to do next

    1. Diane Poremsky

      Close Outlook. Go to C:\Users\username\AppData\Roaming\Microsoft\Outlook - there is a file called VbaProject.OTM. Rename it to VbaProject.old and restart Outlook.

  13. mera

    how to enabled VBA

  14. mera

    i did the same but still cant open my VB editor

    1. Diane Poremsky

      What happens when you try? Are you using the Alt+F11 shortcut?

  15. maher

    hi Diane

    right now i need to know that the code ( worked at my laptop once but after that its not working there and any where. i tried it at ms outlook 2003, 2007 and outlook express 6. let me know what to do. tell me in details as i am not VB expert but have bit understanding. is there any restriction of domains?

    1. Diane Poremsky

      it will not work in Outlook Express but will work in all others. Did you check the macro security settings - Tools, Macro, macro security menu.

  16. mera
    i used Alt+F11 and went to tools macro then editors as well but couldnt open my VB editors.
    then used your given solution
    Close Outlook. Go to C:\Users\username\AppData\Roaming\Microsoft\Outlook - there is a file called VbaProject.OTM. Rename it to VbaProject.old and restart Outlook.
    but no benefits at all.
    its due to when i enter automatically bcc all messages and code togather. kindly solve my problem

  17. merium/maher

    Dear Diane
    yeah i set warning for all macro. and it worked once at my pc but when i tried another code for forwading all messages to a specific id and then delte. i used ths form your site. then it stop working disable my VB eidtor. but the more strange thing when i tried this at my client pc it didnt work. kindly help me out. (i asked this issue as well at your site)

    1. Diane Poremsky

      Was macro security set to allow unsigned macros on the client site?
      The macro itself didn't disable the VBA editor, Outlook did for one reason or another. How much mail is the macro processing ay once? If you receive a lot of mail at once and it's trying to process all of it, it may skip some or it will crash. Adding 'on error resume next' as the first line should help.

      What type of email account? If you are using pop3 or imap and are forwarding all mail that arrives each time you check for mail and there is a lot of download, it can cause problems. You don't want outlook checking mail too often in this case, but also don't want long periods between checks.

  18. merium

    - you mean to say that i need to set macro security setting at that email id in which i want to receive all bcc messages. so which option should be set
    * warning for signed macro, all unsigned macros are disabled
    (although i checked from webmail that mails are doing bcc or not)
    - one of user
    asked to add macro on trusted publisher so is it necessary to do?
    - yeah i download to many emails that was my mistake.

    1. Diane Poremsky

      On the computer where you are using the macro, you need to set macro security to low (or use signed macros), otherwise the macro will not run.

      The macro won't be added to trusted publisher, the *certificate* you sign it with needs to be trusted. When you use selfcert and sign the macro, it should be added to trusted publishers, but if its not listed there, add it.

  19. merium

    thank alot Diane for such a wonderful description
    its working now.
    - but at one of my domain mails are not sending
    when i try to send email following message is coming form System Administrator.
    Your message did not reach some or all of the intended recipients.

    Subject: nte
    Sent: 6/1/2013 4:02 PM

    The following recipient(s) cannot be reached:

    'abc@domainname on 6/1/2013 4:02 PM
    550 Requested action not taken: mailbox unavailable or not local

    why this is happening to all emaial ids of this domain. kidnly solve this

    - one more thing every time i open outlook a window opens that
    *sing in trusted macro
    *enable all macros
    *disable all macros

    so is there way out to solve this


    1. Diane Poremsky

      On the macro problem, you either need to set outlook to trust all or sign the macro and set outlook to run signed macros with out notification. See Using SelfCert to learn how to sign the macros.

      On the system admin error, it's hard to say. Is the address you are sending to correct? Triple check it.

  20. mera
    - i used Alt+F11 and went to tools macro then editors as well but couldnt open my VB editors.
    then used your given solution
    Close Outlook. Go to C:\Users\username\AppData\Roaming\Microsoft\Outlook - there is a file called VbaProject.OTM. Rename it to VbaProject.old and restart Outlook.
    but no benefits at all.
    its due to when i enter automatically bcc all messages and code togather. kindly solve my problem
    - i uninstall msoffice 2007 and then intall again but still i am unable to access my VB editor

    1. Diane Poremsky

      Reinstall won't fix problems. If you rename the VBaProject.OTM file, outlook will make a new blank file and the editor should open. I really don't know why the editor wouldn't open.

  21. merium

    hi Diane
    this code is not working on vista
    bcc address is coming on sent items as bcc.
    kindly let me know same code for vista as well

    1. Diane Poremsky

      The OS is not affecting the code. If you are using the BCC macro, the addresses are supposed to be on the BCC field in sent items.

  22. merium

    i used following code for auto bcc
    Private Sub Application_ItemSend(ByVal Item As Object, _
    Cancel As Boolean)
    Dim objRecip As Recipient
    Dim strMsg As String
    Dim res As Integer
    Dim strBcc As String
    On Error Resume Next

    ' #### USER OPTIONS ####
    ' address for Bcc -- must be SMTP address
    ' or resolvable to a name in the address book
    strBcc = ""

    Set objRecip = Item.Recipients.Add(strBcc)
    objRecip.Type = olBCC
    If Not objRecip.Resolve Then
    strMsg = "Could not resolve the Bcc recipient. " & _
    "Do you want to send the message?"
    res = MsgBox(strMsg, vbYesNo + vbDefaultButton1, _
    "Could Not Resolve Bcc")
    If res = vbNo Then
    Cancel = True
    End If
    End If

    Set objRecip = Nothing
    End Sub

    i used obove code in windows XP it works very well. and bcc email id didnt showed in sent items
    but when i used same code in windows vista bcc address showed in sent items as bcc
    kidnly guide me for sending hidden bcc in windows vista.

    1. Diane Poremsky

      Same version of outlook on both machines? Different versions of Outlook will handle the BCC field in the sent folder differently.

  23. merium

    same outlook version msoutlook 2007 on both pcs but in windows 7 its showing bcc address in sent mail and in xp its not showing bcc email id in sent item. (sorry having problem in windows 7 not in vista.) kindly tell me the code which i may use on windows 7 and bcc EMAIL address will not show in sent items.

    1. Diane Poremsky

      Are both installed fully updated? If its showing in the view, remove the BCC field from the view. In an open item, the BCC field should always show if there is an entry in it. You can hide the entire message header but not the just the bcc field.

  24. merium

    i think there is problem of permissions in windows 7
    thats why bcc emails id are showing in sent itemsl
    kindly tell me to solve the permssion isssues
    ive not get the reply of last post. which has not crossed the moderation yet :(

    1. Diane Poremsky

      It's not permission issues. The BCC field will show on an open message if there is an address in it. Only the mailbox owner sees it.

  25. merium

    but diane in windows xp msoutlook 2007 bcc address is not showing in sent items but in windows 7 its showing. ive tried both.
    let me know the code for windows 7 in which i can hide bcc email ids in sent items.

    1. Diane Poremsky

      There is no code to hide it. The sender can always see the BCC field on sent messages. Are you sure you used BCC on the WinXP machine? I see the BCC field on both outlook 2007/winxp and outlook 2007/win7.

  26. Nick

    is there a macro that can be added to microsoft outlook 2013 that will allow me more opitions for the flag such as putting a time reminder on them in tasks for mail in my imap inbox - i'm thinking similar to the categories macro that you develops and works just wonderfully!

    1. Diane Poremsky

      No, sorry, that is not possible because imap accounts don't support the additional flags at all.

    2. Diane Poremsky

      The macro at set-flag-follow-up-using-vba/ - sets a due date but does not set a reminder. It's probably better to create a task... also, in some folders, if you select 2 or more items, you'll get the full flag menu. See for details. It won't work on mail but will work on other items if you store non-mail items in the new imap data file.

  27. Phil Reinemann

    MS Word has a learn macro option, but I can't find a learn macro function in Outlook (2007). Is there one? What can I do to learn a sequence of mouse or keyboard operations to create a macro?

    1. Diane Poremsky

      Unfortunately, Outlook doesn't have a macro recorder. You need to learn from books and websites. What are you trying to do?

  28. Phil Reinemann

    Thank you for responding Diane. (You have always done so promptly from my experience.)

    I have Outlook 2007 set such when I click Send in an email it by default holds it in the Outbox for 5 minutes, which comes in real handy if say, I forget to add the attachment or think of something else to add or say. The default has an exception that if a particular category is selected for the mail it doesn't wait, but does a SendNow.

    Sendnow comes in handy like when I'm talking with someone on the phone and they'd like the info now, instead of in 5 minutes, or when I send an email and then want to leave for the day and exit Outlook right away.

    For that I have a macro with a button assigned in the QAT. The macro pulls up the Categorize dialog, and one of them (the green one) is set to SendNow. I select SendNow and categorize the mail.

    I'd like to bypass the categorize dialog and have the QAT button/macro automatically set SendNow.

    I just don't know enough VB, much less any object oriented programming, to make the code work. (It's a beginner's issue of not understanding the context of what a method or property applies to/affects when there is such a mass of objects, properties, methods and attributes to each little piece of the Office tools. Category for example likely also has categories, and the context is somehow different for any particular method or attribute.)

    By the way, I like the "wait X" in Outlook 2007 much more than 2003's send/receive every X minutes. In 2003 if you said Send, it could go out any time between seconds later and X minutes depending on when the X minutes were up for Send/Receive.

    1. Diane Poremsky

      I was just working with a guy who wanted to add a category on send - his basic code will work for this - assign it to a button in the QAT

      Sub AddCategory()
      Dim obj
      Set obj = ActiveInspector.CurrentItem
      obj.Categories = "SendNow"
      End Sub

  29. Phil Reinemann

    Here is my current macro assigned to a QAT button. I'll see about fitting in/ replacing it with that snippet you posted. Thanks!
    (Making sense of it all will take a bit more, like figuring out what ActiveInspector encompasses and what CurrentItem is, in the contect of Outlook's received, currently viewed message, to-be-sent message, etc, and why obj.Categories is used insteadof obj.Category (as only one category seems to make more sense). So says a sequential programmer while learning OO. Did you pick "SendNow" because that's what I posted earlier, or because that's what the other user used?

    'by Michael Bauer
    Public Sub ShowCategoriesDialog()
    Dim Mail As Object
    Set Mail = Application.ActiveInspector.CurrentItem
    End Sub

    1. Diane Poremsky

      Inspector = an open outlook item form. ActiveInspector is the top most one, if more than one are open. Currentitem = item you are working on or is in focus.
      When you want to use a macro on an item in the message list, it's referenced as Selected item.

      Obj.Categories is because Categories is what Microsoft calls the object in the object model. The Mail part in your code or the obj in my obj.Categories is the specific object we are using. The word can be changed, as long as it's consistent throughout the macro.

      I used sendnow because I copied from the page and didn't change it.

  30. Phil Reinemann

    I followed the "add a category on send" link you posted and that helps a bit overall to put things together, such as you you posted SendNow, but I still have a ways to go!
    Again, thanks!
    (I wish these "Leave a Reply" boxes had elevator/scroll bars!)

    1. Diane Poremsky

      The comment box will scroll - it doesn't expand on some pages but Tab will bring back the Submit button.

      If you need help with the macro, you can post either here or there.

  31. Julia

    Hi there I have entered a vba code to bcc all sent messages, but its only working for one of my 3 email accounts in outlook 2010

    1. Diane Poremsky

      Unfortunately, its working here with all accounts so I'm able to figure out what could be wrong for you. You could try adding MsgBox "Message Sent" right before the end sub if the msgbox doesn't come up, you know the macro isn't running. If the message box comes up the macro ran.

  32. Jay

    Hello Diane,

    I would like to send a generic holiday greeting that I created via email to ALL of my contacts. I also would like to include those address that I have received emails from that I have not yet saved to contact but are in my inbox. Is it possible to do this without having to individually type/copy email addresses to each recipient and send?

    Thanks in advance for your help.


    1. Diane Poremsky

      You can get the addresses but figuring out which ones aren't in contacts might take some effort unless you create contacts.

      See Create contacts from email messages or use the method here to create a view containing only the email addresses and copy to excel to clean up.

  33. Clint Clarkson

    Hi Diane:

    I would like to have a VBA macro that identifies when an attachment has not been attached. I have found one online, but when it is added to "ThisOutlookSession," it stops working after Outlook is closed and re-opened. Is it possible to attach a macro to all Outlook sessions?

  34. P Reinie

    Clint, where did you find the code to do this? What triggers it - do you send every message with an attachment?

    I found that if I attach what I want to send before I put in the text of the email I haven't had a problem, but I know how it goes.

  35. Alex Noshe

    Hi Diane,
    Is it possible to write a VB code on one machine that will be available on another machine with the same account?
    I have a PC and a laptop and I want to be able to write code only on PC so that laptop is updated automatically (say, VBA code and Ribbon customization)

    Thanks in advance!

    1. Diane Poremsky

      No, not really, at least not to control outlook. Depending on what you want to change, you can do things to files remotely (assuming the computer is on the same network and you have permissions set correctly) - for example, you could use a script or batch file to copy the ribbon file or vba project file to the other computer.

  36. Alexander Noshe

    Thanks for reply.
    Diane, what about roaming locations for my account? Say, the folder that keeps coding and ribbon customization to put in server for my account...
    Makes any sense? Can help?

    1. Diane Poremsky

      You could use a roaming windows profile but its kinda buggy, especially if the machines aren't identical.
      C:\Users\username\AppData\Roaming\Microsoft\Outlook has the VBA file. Ribbon and QAT are at C:\Users\username\AppData\Local\Microsoft\Office, which isn't roamed.

      Rather than roam the folders, I'd use a batch file in Task Manager to copy the files to the server. You may be able to sync them using Skydrive or similar service. (I used to use sugarsync to sync signatures and other folders under roaming but they ended their free service.)

      (I'm not sure if Windows 8 supports roaming profiles - they are buggy and Microsoft talked about removing support. I think 7 does.)

  37. Jenny Bond

    I wish Outlook would write macros for me like Word does! I don't know how to write VB but would like an Outlook macro which filters address cards for me, taking me to the place where you type in the category:

    View, view settings, filter, advanced, in the field drop-down menu select frequently used fields and categories, type

    Then all my colleagues would have to do is click 'add to list' and 'OK' and 'OK'. (Unless you can do a macro which does those bits for them afterwards as well, but I suspect that is not possible.) It would also be helpful to have a macro which would then clear all categories! Is there something available online please or some idiot's guide which would tell me how to write it? Thank you.

    1. Diane Poremsky

      You can use a macro to filter but you can't macro to move to a field in the List (or any other) view. What is the objective? Add a bunch of contacts to one category? A macro can do that... or just select all, then select a category, either from the toolbar or from the right click menu.

  38. Rudy

    Hi Diane, First, Thanks for such a helpful article. I had couple of questions where in I needed advice from a VBA expert and was wondering if you could help.

    - I need to write VBA macro that runs on Outlook to generate Auto-Reply email. This has to be an auto-reply to EVERY single email that enters into the Inbox. Out of office reply will not work for this scenario :).
    So, I did write up some code to generate this reply but I want the macro to run always regardless of whether the Outlook session is open on the server or not. Is that possible?? Please advice.

    - Is it possible to generate a unique auto-reply email out of two different mailboxes that are configured on one Outlook client? I know it is weird but this is a real time scenario.
    Auto-Reply 1 from Mailbox 1, Auto-reply 2 from mailbox 2.

    How would we code this?


    1. Diane Poremsky

      It's not possible to run a macro if Outlook is closed since it is server side. You'd need ot use an event sink or transport rule or custom program to watch for new messages.

      You can use different autoreplies for different accounts. If you are using Rules, you'd create two rules and select the condition for 'received on account' for each rule. If you mean, have both account A and B reply to the same message, no, not without some VBA magic or redirection to the second mailbox.

  39. Anas

    Hi Diane, Can u please provide me a program code in outlook, which reads the email subject line,word by word and stores each word in the text file..

    1. Diane Poremsky

      I have a macro here - - that saves messages in a text file.

  40. Yair

    Is it possible to add a file attachment (as an icon) to a mail message using a macro?
    Thanks in advance

    Sub ניסיון()
    Set msg = Application.CreateItemFromTemplate("C:\Documents and Settings\y_amoyal\Application Data\Microsoft\Templates\ניפוק כימיקלים חודשי-בנק הדם.oft")
    Attachment.Add "P:\ניפוק חודשי למעבדות\בנק הדם.xls"

    End Sub

    1. Diane Poremsky

      As in, add it to the message body? Yes, but you need to use RTF formatting (which can be set using VBA).
      msg.BodyFormat = olFormatRTF

  41. Yair

    Hi again
    Thanks for your reply, but I did not understand your tip.
    My aim is to open a template mail, with .xls attachment that is in specific address (which I update outside that)

    1. Diane Poremsky

      I thought you wanted to insert the attachment in the message body, as an icon, not as an attachment under the subject field? Embedded attachments are in RTF messages.

      If you just want to insert the attachments, it would be msg.Attachments.Add filepathname
      If you are trying to set the filepath each time, see How to use Windows filepaths in a macro for one method.

  42. Yair

    It works!
    Great Thanks!

  43. Yair

    Diane, is it possible to ask about XLS files too?

    I am trying to open a sheet with next month date, such as "3-2014" (m-yyyy)
    Cannot find the mistake. I am on it for few months already...

    Thanks in advance

    Dim m As Variant
    Dim y As Variant
    Dim dt As String

    Dim newDate As Date

    newDate = DateTime.Now + Month(1)

    m = Month(newDate)
    y = Year(newDate)
    dt = CStr(m) + "-" + CStr(y)

    On Error Resume Next
    If Sheets(dt) Is Nothing Then
    newDate = DateTime.Now + Month(1)
    m = Month(newDate)
    y = Year(newDate)
    dt = CStr(m) + "-" + CStr(y)
    ActiveSheet.Name = dt
    dt = ""

    MsgBox ("There is a sheet already named with next month date.")
    End If

    1. Diane Poremsky

      Hmmm. It renames the sheet here. If you want to add a sheet, you need to use
      Dim WS as Worksheet
      Set WS = Sheets.Add

  44. Yair

    Thank you very much, Diane. It works. But you know that already.
    thanks again

  45. Nikeita P.

    I created a custom form to be used to take messages in our office. I saved the template on our server so that our employees can add the form to their outlook folder. I believe the problem I am having is maping the macro to the folder on our server where the template is located. Can you take a peek at my code below and let me know where I am going wrong?

    Sub DisplayForm()
    Set myFolder = Session.GetDefaultFolder("X:/Resources")
    Set myItem = myFolder.Items.Add("Phone Message.oft")
    End Sub

    Thanks for all your help in advance Diane.

    1. Diane Poremsky

      this: X:/Resources" should be X:\Resources oh, and getdefaultfolder is for outlook folders, not file system folders. See - you need to use Application.CreateItemFromTemplate("c:\path\template.oft")
      If the template has code or custom fields it may not work unless it's in the template folder.

  46. Paul

    Every time I run ( |> button ) the macro I get a dialog to name it. Any ideas what I'm doing wrong. Please keep the answer simple, I'm a newbie w/VBA

    1. Diane Poremsky

      Hmm. Click in the macro you want to run, then click the button. Do you get the dialog?
      If the macro is named in this format: sub macroname (something as something), then it needs to be called from another macro or in a run a script rule.

  47. Yair

    Hi Diane
    The problem still occurs.When I tried to run the Macro today it still give the MsgBox.
    Any other ideas?

    1. Diane Poremsky

      that's the msgbox that says the sheet already exists? Is it adding a new sheet and does a sheet by that name exist?

    2. Yair

      Yes. It is adding a sheet with the right data, but somehow indicates that the name of the sheet is already exist. Again, i run the macro once a month and the name is suppose to be the next month date.

    3. Diane Poremsky

      Unfortunately, I don't know the cause - the macro looks good to me, it works here and doesn't cause an error. While I can handle some Excel VBA, I'm not an expert in it. You might want to try the forums at Mr Excel.

  48. Phil Reinemann

    Yair, (from March 9, 2014 at 5:49 am) use your message box to output the dt string then check if it exists already.
    I found it too weird to debug using msgbox month(1) so I'd use:
    nowDate = DateTime.Now
    m = Month(nowDate) + 1
    and similarly for the year to get the sheet's name-string.

  49. Mary

    Hi Diane, your website is very helpful! I have an VBA-script that I cannot get working everywhere. The problem seems to be with the digital signature. What I've done is that I've created the signature with selfcert.exe, then exported/imported it through Internet Options into trusted root certification publishers and trusted publishers.
    Then I opened Outlook Trust Center and made sure the certificate was added to Trusted Publishers there. Then I assigned this signature to the VBA project.

    In Outlook, the security settings are set to 'all unsigned macros are disabled', and we want to keep it that way.

    Whenever I open the VBA editor, it does not do anything, the script does not run if I make it. If I set the security setting to 'always ask', it shows a warning when I open the VBA editor, but I don't get the option 'Always trust this publisher' (it's greyed out). If I manually say that the macro should run that time, it works fine. So, the problem is not with the VBA project itself, but with the security surrounding the digital signature.

    How do I get the VBA project to run, with the 'all unsigned macros are disabled' settings? What can I do to pass the security warnings?

    This system is running Outlook 2013/Win7.

    Thanks so much for your help already!

    1. Diane Poremsky

      Close Outlook then Right click on the Outlook shortcut and choose Run as administrator. Now you should be able to accept and trust the certificate. Once it's accepted, you can restart outlook as a normal user.

  50. Phil reinemann

    Could the sheet be in the Personal.xlsb instead of in the main xlsm document?

  51. Phil Reinemann

    My last question about the sheet in Personal.xlsb is about Yair's code to make a new excel sheet.

  52. Yair

    Do you mean nowDate or newDate? my Macro us indeed in the Personal xlsb. is it a problem?
    I need a guidance for implementing your suggestion...

    1. Diane Poremsky

      He's using nowDate as a variable in his code snippet. Personal can be a problem - i tested it in a workbook.

  53. Phil Reinemann

    Yair, I used nowDate because it retrieves the current time, and from that newDate is computed. Give it whatever names you want.

    I was wondering if the sheet that your code detects as already existing is a sheet in your Personal.xlsb instead of in the workbook where you created the new sheet.

    So in your test for existance "If Sheets(dt) Is Nothing" perhaps you should specify the workbook where you created the sheet to make sure you're looking for the sheet in the right workbook.

    I only see the code you originally posted so I can't help you more.

  54. Emmett McAuliffe

    Hi Diane.

    Was wondering if there was a code to search everything that might've happened in Outlook on a particular day: Received, Sent, Modified, Created ... more? Advance find seems to only produce an "AND" result. So you really have to perform those searches separately and compare the results (you get a lot of duplication).
    Thanks for any help you can give.

    1. Diane Poremsky

      I'm not sure I understand the need, but Advanced Find with the Query Builder might work.

  55. Emmett McAuliffe

    Thanks Diane. I will for sure take a look at Query Builder. To answer your question about why anyone would need this, ... advanced find allows you to "To find more criteria" , ... then conveniently "add [it] to list" but it only does so only with the operator AND. I need the operator OR. I can add unlimited criteria (a good thing)... but it will only find Outlook Items that match ALL of those criteria not ANY of them (like most query engines work ... for example , the search engine I use every day as a trademark lawyer where it assumes that you want the operator OR ... and you have to manually change it.)

    For example, in a Venn diagram, if a red balloon in a blue balloon overlap, Microsoft Outlook will only let you see the purple overlap area, it will not show you the entire area of the red balloon and the blue balloon.

    1. Diane Poremsky

      My query was more along the lines of 'why do you need all 4 date fields together' - the sent or received date should = the modified or created dates. Created will restrict it to only new items, while modified will include older items that were changed today.

  56. Emmett McAuliffe

    So what I'm finding is that by "Modified", Outlook means "last modified". Therefore, if an email was received on say March 17, but modified with the flag (for example), on March 31, it will not show up in a search for "modified on March 17".

    1. Diane Poremsky

      Correct. The created date should be the date it was received but modified is the date it was last touched.

  57. Gary Davis

    My outlook script runs and adds a Subject when I hit "yes". However sometimes I hit yes to quickly so I would like to require to ask one final time Send Email Y or N. And have it default to Yes so I can just hit Enter and it will send or N for No.

    1. Diane Poremsky
  58. Paul Grace

    Hiya Diane -

    Wonder if you can help? We are attempting an auto-reply of different styles of incoming email:
    a) Where a pdf attachment (timesheet) is received: to email back confirmation of receipt (along with the pdf itself so the send knows what we have received).
    b) Where a holiday refund request is received - no attachment.

    In both cases the sender's email address is within the email body (generated via a corporate website add-on). Seems to be causing brain cells to explode without an answer. :-)

    Many thanks -


    1. Diane Poremsky

      you can do both using regex. Basically, the last macro on this page: - the code samples here - - have a sample that uses the email address.

      The macro at shows how to reply with attachments.

    2. Paul Grace

      Diane - a very BIG thank you! And I have found the outer limits of my knowledge and comfort zone!! Having played with the code and managed to have some bounce out of the incoming folders - but not as expected ... a question.

      Are we able to purchase your skills and time to set this up so that it works first time every time?

    3. Diane Poremsky

      I am available for hire - see for details and a link to the scheduling software.

  59. u01brb2


    I have been trying, in outlook 2010, to create a macro that i can add to the QAT with a drop down menu to choose my template emails. I managed this last year then had my computer rebuilt and forgot the code! Currently i have a different icon for each template on my QAT, i would rather have a dropdown menu to choose a specific email template - does anyone know the code? Currently i am using:

    Dim template As String

    Sub OpenTemplate1()
    template = "C:\Users\benbarclay\AppData\Roaming\Microsoft\Templates\safetytalks.oft"
    End Sub

    Sub OpenTemplate2()
    template = "C:\Users\benbarclay\AppData\Roaming\Microsoft\Templates\Your Online Preview of SafetyTalks.oft"
    End Sub

    Sub OpenTemplate3()
    template = "C:\Users\benbarclay\AppData\Roaming\Microsoft\Templates\SafetyTalks New Training.oft"
    End Sub

    Sub MakeItem()
    Set newItem = Application.CreateItemFromTemplate(template)
    Set newItem = Nothing
    End Sub

  60. Rex

    Just a friendly buzz for your valuable materials posted in this page so that I have made use of it in my working area :)

  61. Leo Verberne

    Hi Diane,

    I have a macro in outlook which opens an excel worksheet, scans through mails and puts information from those mails in the excel sheet.
    In order to find out which is the last line with info in that sheet, i used the line:

    Set Wks = MyXL.Activesheet
    RowCounter = Wks.Cells(65536, 4).End(xlUp).Row

    I now get an error on the line with the End(): "fault 1004 application or object defined error"
    errortekst has been translated from dutch.

    This worked perfectly in outlook 2007 but not anymore in Outlook 2010.
    In macros in Excel 2007 and Excel 2010 this is still working.

    What is wrong here?


    1. Diane Poremsky

      Did you set a reference to Excel in Outlook's VB Editor > Tools > References.

  62. Leo Verberne

    Hi Diane, That was it. Thanks a lot. A bit strange that this was the only statement that failed in the complete code. And it was a bit weird that i posted my Q on 26th August and it only appeared just now on the forum.

    But okay, Thanks anyway for the help.

  63. Robert

    Hi Diane,
    I was wondering if you could help me out a little bit, Im trying write a rule/script for when i recieve an email with 7 alphanumeric characters in the front of the subject to then import a .png from the msg body into onenote, grab TXT from the .png using text recognition then import it into a already saved excel spreadshet. Currently working on it, but if you had any snippets I could use it would be greatly appreciated.

    1. Diane Poremsky

      I don't have anything that does this. Sorry.

  64. Viki

    Hi Diane,
    I have multiple e-mail accounts set up in my one outlook profile. Is there a way to BCC all send items from one account to one email address?

    1. Diane Poremsky

      Use the code at and add an if statement -
      To only BCC from one account, add this before the Set Recipients line and enter the email address you want to send BCCs from

      If Item.SendUsingAccount = "account@address" Then

      add another end if at the end of the other 2 end if's.

  65. Eric


    My partner likes to put the date he sends an e-mail in his signature (I know that the header has that too, but he likes it in the signature as well). Outlook does not seem to allow self-updating fields in signatures. I found a suggested workaround of putting a placeholder such as "**DATE**" and using VBA to swap it out on sending, but I am not a developer and do not know enough Visual Basic to put such a script together. Do you have any suggestions?

    Thanks in advance.

    1. Diane Poremsky

      Try inserting the date into a message, set Update Automatically option then copy and paste it into the signature editor - i think you need to use paste special, keep formatting.

  66. Marcos

    Hi Diane, do you know how it will be possible to forward a mail only changing the address (the new address will be taken from the CC of the received email)? Thank you so much!

    1. Diane Poremsky

      This does it using a rule - - try replacing myForward.Recipients.Add "" with myForward.Recipients.Add - if that doesn't work, you'll need to use the recipients collection and get the cc address. I have code to do that here somewhere.

  67. vicki

    Hi Diane,
    I’m totally new to VBA and have been reading a lot online to figure out where I even start.
    I was trying to make a rule but it looks like I need to use VBA to get what I need. In a nutshell…

    Message arrives to Inbox
    from <>
    on Cc line <>
    move email to <>

    Is it easy to do with VBA?

    Also my current Inbox is not set up to let me Enable Macros, but I will follow up with IT to get that corrected because I will need that turned on, correct?

    Thank you in advance!

  68. Alberto

    Hi Diane - I am trying to create a certificate and digitally sign my VBA code. I have outlook 2013 and windows 7. I don't have a selfcert. But I am able to create the certificate using the "Manage your file encryption certificates" in the control panel.
    I am able to see my certificate using MMC (microsoft manager console) in the folder \Personal\Certificate. But When I go to outlook VBA>tools>digital signature my certificate is not there. Why? Any ideas? Thanks in Advance..

    PS: I noticed that my certificate has an intended purpose of "Encryption File System" instead of "Code Signing". However, I am not able to change its purpose because I do not have admin rights to the machine. Could that be the issue?
    However, I have a series of other certificates issued by/to microsoft as "Codde Signing" and none will show up on my VBA.


Leave a Reply

If the Post Comment button disappears, press your Tab key.

This site uses XenWord.