Send Outlook Emails With VBA Macros

Applicable to Microsoft Outlook 365 / 2019 / 2016

Here’s a note we got from a reader:

Thanks for your very useful Outlook coding help. I am wondering if you can share some code to help automate sending an email in Outlook 365? I know that there are several ways to send recurring message but i am specifically interested in using VBA for my this task.

Thanks for the question. Automatically sending emails seem to be among the most useful and popular tasks you can automate using VBA. Read on for more details and a simple script to get you started here.

Sending Outlook emails automatically using VBA

To send out email messages in Outlook using Visual Basic for Applications, proceed as following:

1. First off, use the VBA Outlook object model to create a new email item.
2. Then go ahead and set the message importance, recipients (separated by semi colons) and body text style and content.
3. Next, display the message in your Microsoft Outlook application.
4. Last, go ahead and send it your email recipients.

Here’s the simple VBA code to automatically create and send simple emails. Note that you are to easily improve this script to include file attachments, tasks etc’, but let’s start with a very basic script.

Sub SendMail() Dim MyEmail As MailItem Set MyEmail = Application.CreateItem(olMailItem) With MyEmail .To = "<type your recipient email address/ess here>" .Importance = olImportanceHigh .Subject = "<type the subject of your email here>" .Body = "<type the email message text here>" .BodyFormat = olFormatHTML .Display End With 'I purposely commented the next line, uncomment it by removing the "'" sign to send your email. ' MyEmail.Send End Sub

Create a Outlook VBA macro shortcut

You might want to look into associating the VBA code you just wrote with a command button in Outlook. This will allow you to easily launch it from a shortcut placed in the Outlook Ribbon.

Here’s how to do that:

1. First, go ahead and open Microsoft Outlook.
2. Hit File then select Options
3. Now go ahead and select Customize Ribbon.
4. In the Customize Ribbon section, you’ll notice the Choose Commands from list box.
5. Select Macros
6. Then Select your newly created VBA code, in our case the name should be SendMail.
7. Then hit Add and then OK. Optionally you might want to assign an icon to your Macro.
8. Your Macro will be available in the Quick Access toolbar in the upper left side of your Outlook application.

Send multiple emails using Outlook Macro

Next use case is a bit more complicated – consists of sending several emails in a batch. The email addresses are kept in a text file. You can modify the script below to set the location of your text file in your computer, Onedrive or server.

Sub SendMultipleMails() Dim fso As Object Dim ts As Object Dim line As String Dim emailAddresses As Collection Dim emailAddress As Variant Dim MyEmail As MailItem ' Initialize FileSystemObject and Collection Set fso = CreateObject("Scripting.FileSystemObject") Set emailAddresses = New Collection ' Open the text file and read each line (email address), replace with the specific location in your computer Set ts = fso.OpenTextFile("C:\addresses.txt", 1) ' Add each email address to your file Do While Not ts.AtEndOfStream line = ts.ReadLine emailAddresses.Add line Loop ts.Close ' Now send email to each address in your file For Each emailAddress In emailAddresses Set MyEmail = Application.CreateItem(olMailItem) With MyEmail .To = emailAddress .Importance = olImportanceHigh .Subject = "Your Email Subject Here" .Body = "Your email message text here." .BodyFormat = olFormatHTML .Display ' Uncomment the next line to send your emails ' .Send End With Next emailAddress End Sub

Send emails with attachments with Outlook VBA

Next case consists of allowing your user to attach ore or multiple files to their outgoing Outlook message. Note that you can set the AllowMultiSelect property of your FileDialog object to False to allow single file selection.

Here is the code to automatically launch emails with one or more file attachments using a VBA macro:

Sub SendMailWithAttachments() Dim MyEmail As MailItem Dim fd As FileDialog Dim selectedItems As FileDialogSelectedItems Dim i As Integer ' Create a new email item Set MyEmail = Application.CreateItem(olMailItem) ' Initialize the FileDialog object Set fd = Application.FileDialog(msoFileDialogFilePicker) ' Set dialog title fd.Title = "Select File(s) to Attach" ' Allow multiple file selection fd.AllowMultiSelect = True ' Show the file selection dialog If fd.Show = -1 Then ' Get the selected items Set selectedItems = fd.SelectedItems ' Attach each selected file to the email For i = 1 To selectedItems.Count MyEmail.Attachments.Add selectedItems(i) Next i End If 'Here you define your email parameters With MyEmail .To = "<type your recipient email address/ess here>" .Subject = "<type the subject of your email here>" .Body = "<type the email message text here>" .BodyFormat = olFormatHTML .Display End With ' Uncomment the next line to send your email ' MyEmail.Send End Sub

Possible issues with VBA macro settings

Your Outlook macro settings might need to be tuned in order to run macros. If so, here’s how to do that.