Last updated: October 2019
Applicable to: Office 365 subscriptions. Office 2019, 2016, 2013, 2010 ; Windows 10, 8, 7 operating systems.
In Microsoft Office, we use Visual Basic for Applications (VBA) for creating custom programs that enhance Office’s basic functionality and saving user time by automating repetitive tasks. We refer to these VBA program as Macros.
In this tutorial, we will focus on learning the basics of Outlook VBA for beginners:
Unlike other Office apps, Outlook does not offer a simple Macro recorder to capture user actions and auto-generate the required VBA code for us in the background. Therefore, if we want to add our own custom functionality to Outlook, we’ll need to learn to write simple VBA programs and incorporate them into our Outlook session.
Before you start writing your code, you may need to set up your Office development environment. Sounds fancy isn’t it? Actually it’s quite a simple procedure:
This simple snippet creates a new email message item programmatically; then it sets the to, subject, body text and CC fields. Finally it displays the message in Outlook so you can review and send it.
Public Sub CreateMail() Dim MyEmail As MailItem ' Create a new Outlook message item programatically Set MyEmail = Application.CreateItem(olMailItem) 'Set your new message to, subject, body text and cc fields. With MyEmail .To = "email@example.com" .Subject = "Insert your message subject here" .Body = "Insert your email text body here" .CC = "firstname.lastname@example.org" End With MyEmail.Display End Sub
Advanced Note: You are able to use predefined Outlook templates when defining new emails with VBA. In order to do that, you’ll need to leverage the method CreateItemFromTemplate and specify the location of your oft file.
If you want Outlook to send out the email you have just created automatically, you can add the following line to the code posted above. Paste the following line before the End Sub statement.
In many instances you would like to automate sending an email with a file attached to it. This could be a Word document, Excel spreadseheet, Powerpoint presentation, PDF documents etc’. If you want to send an email with attachments programmatically, use this code:
Public Sub CreateMailWithAttachments() Dim MyEmail As MailItem ' Create a new Outlook message item programatically Set MyEmail = Application.CreateItem(olMailItem) 'Define your attachment folder path and file name - modify this part as needed Dim AttachFolder, AttachFile As String AttachFolder = "C:\" AttachFile = "test.txt" 'Set your new message to, subject, body text and cc fields. With MyEmail .To = "recipient@my_server.com; mysecondrecipient@my_server.com" .Subject = "This is your message subject" .Body = "Insert your email text body here" .CC = "cc@my_server.com" .Attachments.Add AttachFolder & AttachFile End With MyEmail.Display End Sub
The following snippet, creates a task and assigns it to another individua and sets the task subject and body text.
Public Sub CreateTask() Dim MyTask As TaskItem ' Create a new Outlook task Set MyTask = Application.CreateItem(olTaskItem) 'Set your new task recipients and information fields With MyTask .Assign .Recipients.Add "taskrecipient@my_server.com" .Subject = "This is your task subject" .Body = "Insert a thorough explanation of your task here." End With MyTask.Display End Sub
Few readers asked how to automate saving emails to HTML. The code below accomplishes exactly that.
Note: Ensure that you launch this code only on open emails.
Public Sub SaveAsHTML() On Error Resume Next Dim MyWindow As Outlook.Inspector Dim MyItem As MailItem Dim FilePath As String FilePath = Environ("HOMEPATH") & "\Documents\" & "\" Dim ItemName As String Set MyWindow = Application.ActiveInspector If TypeName(MyWindow) = "Nothing" Then MsgBox ("Kindly open an email to save") Else Set MyItem = MyWindow.CurrentItem ItemName = MyItem.Subject ' File name will be identical to the message subject With MyItem .SaveAs FilePath & ItemName & ".html", olHTML End With End If End Sub
Our next step will be to insert one of the VBA code snippets displayed above, or other code, into your Outlook session. Here are the steps to follow:
You might be wandering where eactly your used Macro is stored. Well, unlike in Excel for example, which allows you to store macros in the personal.xlb file or in specific spreadsheets; all Microsoft Outlook macros are stored in one global file in your file system. Specifically, the file storing all you Macros is named VBAProject.otm. The file can be found at: C:\users\<your_user_name>\AppData\Roaming\Microsoft\Outlook.
Our next step is to insert a small shortcut button which will allow us to easily run the macro. For simplicity, we’ll insert this button to the Quick Access toolbar.
Note: You can use a similar procedure to place your icon in the Ribbon itself instead of in the Quick Access Toolbar.
This step is optional and should be followed only if you receive an error message when running your newly created macro.
Last but definitely not least, now it’s time to run your newly created macro.
Several of our readers asked whether we could specify further ideas for Outlook Automation. Here are a few ones to get started.