Last reviewed on May 1, 2015   —  151 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

Before 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.

Enable Macros in Outlook's Trust Center

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 above, 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

Press 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.)
Click the button to run the macro

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-cert

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.

In 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.
Customize Outlook 2010 and 2013 ribbon

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.)

In 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.
macros-toolbar

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 Outlookcode.com or the Microsoft programming forums at MSDN

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


Comments

    • Jack says

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

      Set myItem = myOlApp.CreateItemFromTemplate("Path")

      myItem.Display

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

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

  1. sandy ingram says

    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,
    Sandy

    • Diane Poremsky says

      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
      oMail.Display
      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.

  2. sandy ingram says

    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?
    Sandy

  3. sandy says

    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

  4. blaz says

    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

    • Diane Poremsky says

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

  5. Alex says

    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.

  6. merium says

    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.

    • Diane Poremsky says

      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 "drcp@cdolive.com"
      myForward.Display
      End Sub

    • Joshua Ragan says

      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?

  7. mera says

    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.

    • Diane Poremsky says

      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.

    • Diane Poremsky says

      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.

    • Diane Poremsky says

      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.

  8. mera says

    http://www.slipstick.com/developer/how-to-use-outlooks-vba-editor/#comment-177349
    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 http://www.slipstick.com/developer/how-to-use-outlooks-vba-editor/#comment-177208 code togather. kindly solve my problem

  9. merium/maher says

    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)

    • Diane Poremsky says

      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.

  10. merium says

    - 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 http://www.slipstick.com/developer/how-to-use-outlooks-vba-editor/#comment-176791
    asked to add macro on trusted publisher so is it necessary to do?
    - yeah i download to many emails that was my mistake.

    • Diane Poremsky says

      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.

  11. merium says

    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

    Regards

    • Diane Poremsky says

      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.

  12. mera says

    http://www.slipstick.com/developer/how-to-use-outlooks-vba-editor/#comment-177349
    - 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 http://www.slipstick.com/developer/how-to-use-outlooks-vba-editor/#comment-177208 code togather. kindly solve my problem
    - i uninstall msoffice 2007 and then intall again but still i am unable to access my VB editor

    • Diane Poremsky says

      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.

  13. merium says

    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
    thanks

    • Diane Poremsky says

      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.

  14. merium says

    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 = "one@arktex.co.uk"

    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.

    • Diane Poremsky says

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

  15. merium says

    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.
    thanks

    • Diane Poremsky says

      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.

  16. merium says

    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 :(

    • Diane Poremsky says

      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.

  17. merium says

    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.

    • Diane Poremsky says

      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.

  18. Nick says

    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!

  19. Phil Reinemann says

    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?

    • Diane Poremsky says

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

  20. Phil Reinemann says

    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
    'http://www.vboffice.net/en/developers/assign-email-categories-before-sending

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

    • Diane Poremsky says

      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.
      outlook-vba-work-with-open-item-or-select-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.

  21. Phil Reinemann says

    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.

    • Diane Poremsky says

      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

  22. Phil Reinemann says

    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!)

    • Diane Poremsky says

      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.

  23. Julia says

    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

    • Diane Poremsky says

      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.

  24. Jay says

    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.

    Jay

  25. Clint Clarkson says

    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?

  26. P Reinie says

    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.

  27. Alex Noshe says

    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!

    • Diane Poremsky says

      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.

  28. Alexander Noshe says

    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?

    • Diane Poremsky says

      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.)

  29. Jenny Bond says

    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.

    • Diane Poremsky says

      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.

  30. Rudy says

    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?

    Thanks.
    Rudy

    • Diane Poremsky says

      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.

  31. Anas says

    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..

  32. Yair says

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

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

    End Sub

    • Diane Poremsky says

      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

  33. Yair says

    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)
    Regards,
    Yair

    • Diane Poremsky says

      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.

  34. Yair says

    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 = ""

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

    • Diane Poremsky says

      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

  35. Nikeita P. says

    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")
    myItem.Display
    End Sub

    Thanks for all your help in advance Diane.

  36. Paul says

    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

    • Diane Poremsky says

      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.

  37. Yair says

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

    • Diane Poremsky says

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

    • Yair says

      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.

    • Diane Poremsky says

      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.

  38. Phil Reinemann says

    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.

  39. Mary says

    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!

    • Diane Poremsky says

      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.

  40. Phil Reinemann says

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

  41. Yair says

    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...
    Thanks
    Yair

    • Diane Poremsky says

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

  42. Phil Reinemann says

    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.

  43. Emmett McAuliffe says

    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.

  44. Emmett McAuliffe says

    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 http://tmsearch.uspto.gov/bin/gate.exe?f=searchstr&state=4809:z3diok.1.1 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.

    • Diane Poremsky says

      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.

  45. Emmett McAuliffe says

    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".

    • Diane Poremsky says

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

  46. Gary Davis says

    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.

  47. Paul Grace says

    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 -

    Paul

  48. u01brb2 says

    Hello,

    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"
    MakeItem
    End Sub

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

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

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

  49. Rex says

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

  50. Leo Verberne says

    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
    Wks.Activate
    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?

    Thanks,
    Leo

  51. Leo Verberne says

    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.

  52. Robert says

    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.

  53. Viki says

    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?

  54. Eric says

    Diane,

    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.

    • Diane Poremsky says

      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.

  55. Marcos says

    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!

  56. vicki says

    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 <>
    or
    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!

    • Diane PoremskyDiane Poremsky says

      You will need to be allowed to run VBA to use macros and yes, to use a From OR To rule, you'll need to use a macro.

  57. Alberto says

    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.

    Alberto

    • Diane PoremskyDiane Poremsky says

      you need a code signing certificate. Selfcert.exe is under your Office installation. It should either be at C:\Program Files\Microsoft Office 15\root\office15 or C:\Program Files (x86)\Microsoft Office 15\root\office15

  58. Mark Johnson says

    Hi Diane, thanks so much for the code to always go to a specific task list. I have never done such before and you made it simple enough that i could handle.

    I want to make it that a new task created by the shortcut "CNTL-SHIFT-K" will be created in that same task list regardless of whether I am in calender, email or contacts. The default seems to be the to-do list. Can you help?

    • Diane PoremskyDiane Poremsky says

      Technically, the default is your default task list. It just shows up in the To-do list. Assuming your can't change the default data file, you can do it one of two ways: pick up the opening of the new task form and move it via a macro when you click save or watch the default folder. Watching the folder is probably the easiest.

  59. Mark Johnson says

    There are several aspects to my need for a solution.

    iPhone reminders would not sync to the default list so I had to create a new list.

    Tasks created in Outlook for Mac from a shortcut or menu are slow to appear on the PC and show in the to-do list on the PC Outlook unless they are created when viewing the task list on the mac.

    I have two exchange accounts set up in Outlook so I need the tasks to the same task list each time and one that will sync across devices.

    Now when I create from an outlook window other than tasks the task is created in the to-do list rather than the default task list and that task list does not sync to the iOS.

    It does not look like I can change the default task list on either platform and even if I watch the folder it does not let me change. How can I create a macro on PC for teh task folder to be saved to and any solutions on the mac?

  60. Andrew G says

    Hi,

    Thank you for all of these helpful posts. Is there any way to format the text to appear as an email? Ie with formatted body and signature?

    Private Sub Application_Reminder(ByVal Item As Object)
    Dim objMsg As MailItem

    Set objMsg = Application.CreateItem(olMailItem)

    If Item.MessageClass "IPM.Appointment" Then
    Exit Sub
    End If

    If Item.Categories "Send Message" Then
    Exit Sub
    End If

    With objMsg

    ' Email addresses go here, use semi-colons to separate multiple addresses
    .To = "bgsd@stlouis.org"
    .Subject = "Training Schedule 4/17/2015-4/27/2015 REV 3"
    .Body = "This email is a test"

    ' use .display to view but not automatically send
    .Send
    End With

    Set objMsg = Nothing
    End Sub

    • Diane PoremskyDiane Poremsky says

      Dang, I swear I answered this 4 or 5 times - i guess I start to answer it then get distracted and the browser gets closed before I finish. you can use basic HTML formatting and may need to use .htmlbody instead of .body in Body = "This email is a test"
      .HTMLBody = "<b>Hello!</b>" & vbCrLf & "<p style='color:red'>Howdy</p>"

      if you use signatures, you can use .Body = "This email is a test" & .body to keep the signature.

  61. Barry says

    Hi Diane... can you help me with a code to save a pdf attachment from one person using outlook 2013.. As I dont want to save all attachments on all emails. thank you

    • Diane PoremskyDiane Poremsky says

      you need to add an if statement - this assumes the sender is not a coworker if you are using exchange.
      if item.senderemailaddress= "email@address" then

      'do whatever

      end if

  62. Charles Woodward says

    Hello, I'm cant follow the video it seems you preprepared and it means that the video cannot be followed! So.. I paste my code into thisoutlooksession hit play (nothing happens) then nothing appears in the macros dropdown list. If I click it macros it wants me to make a macro and creates a module.... STUCK!

    thanks for help...

    p.s. I'm using the print attachments script, that I hope to use for BYOD...

  63. Stu says

    Mahalo (thank you) for the code. This is exactly what i was looking for. However, for reasons beyond me..i can't seem to get the macro to run. I have Outlook 2013, macros are enabled. I copied and pasted the code into "ThisOutlookSession". I attempted to run the code "As-Is", but nothing happens. I also edited to reflect
    strFileAs = .LastNameAndFirstName & " (" & .Email1Address & ")"

    I also went through your tutorial and still can't get it to run. No error messages or prompts.

    I would really appreciate your assistance..

    • Diane PoremskyDiane Poremsky says

      Add the following line after the strFileAs = line
      msgbox strfileas

      Then run it, preferably on a folder with only a few contacts or a small selection of contacts (depending on the exact code you are using) - it'll pop up a message box with the strFileAs value. If you don't see the message box, then the macro dies before that point.

      Also, remove or comment out the error handling line (add ' in front of it) so it stops if it hits an error.
      ' On Error Resume Next

  64. ria says

    Hi Diane how do you forward an email.I had never experienced this on my Nokia account.I knew how to do everything on my Nokia email

  65. Micheal says

    Is there a VBA that will BCC an address based on the Email Subject title. I know that Outlook has this function available, but it only allows CC. While this is great, I don't want clients to see the other address.

    Any help or advice on this matter would be greatly appreciated.

    Thanks

  66. Gerald says

    Hi Diane,
    Its a real shame there's no macro recorder in Outlook like there is in Excel - I can follow all your great instructions but I don't know how to write the code I need! Maybe you can help?
    I want to run the macro as a rule on specific incoming emails. the Macro will do the following: (as if):
    Open the message, select Actions, Edit Message, Highlight all text, Select text direction Right-to-left, close message saving changes.

    Can you point me in the right direction?
    Many thanks
    GS

Leave a Reply

Please post long or more complicated questions at OutlookForums by Slipstick.com.

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