Vba excel send email outlook

Updated: August 2021; 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? 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.

Writing VBA for sending Outlook emails

In the rather simple example below, we’ll cover the following steps:

1. First off, we’ll use the VBA Outlook object model to create a new email item.
2. Then we’ll go ahead and set the message importance, recipients (separated by semi colons) and body text style and content.
3. Then we’ll display the message in your Microsoft Outlook application.
4. Last, we’ll go ahead and send it to the 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

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.

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.

Microsoft Excel has many features, and one of the most useful features in Excel is sending automated emails. In this tutorial, you will learn how to send an email in excel using VBA scripts. You will get an idea of how you can automate Outlook and send several emails with attachments from Excel, marking people in Cc and Bcc. 

Why Use Excel to Send Emails?

There could be multiple reasons for which you might want to send an email in excel. A few of them are:

• Suppose you have a team that updates spreadsheets or documents monthly, and you want to get an alert message when those updates are complete. This is a simple case where you can use VBA to send automated emails.
• Let's say you have a list of customer names in a column in Excel, and you have the file path of a related file for each of the customers in another column. Your task is to send one email to each customer and attach the correct file. This is another example of using VBA to trigger automated emails. 

How to Send Email in Excel Using VBA?

You need to write a few sample scripts to send emails in excel. Now, understand the steps to send emails through Outlook from MS Excel using VBA.

1. In the Developer Tab, select Visual Basic to open the VB code editor.

Fig: Developer tab and Visual Basic

2. Now, to access Outlook with VBA, you need to activate the Microsoft Outlook Object Library. For this, you must go to Tools in the VBA editor > References.

      Fig: Tools

3. Under References, select Microsoft Office 16.0 Object Library, OLE Automation, and Microsoft Outlook 16.0 Object Library and click on OK.

        Fig: References

4. Now, navigate to the Insert tab and choose a new Module.

   Fig: Module

5. Create the subprocedure in VBA.

Sub SendEmail_Demo()

End Sub

6. Initially, you had given reference to Outlook. Now, you can access the properties of Outlook in VBA. Next, declare an object variable called Outlook.Application to refer to the outlook object.

Dim EmailApp As Outlook.Application

7. Next, create an instance of a new object separately.

Set EmailApp = New Outlook.Application

8. To send an email, you need to declare another variable that will use MS Outlook's property to refer to a new email.

Dim NewEmailItem As Outlook.MailItem

9. In the next set, you need to create an instance for a new email that will open the new email using the set keyword.

Set NewEmailItem = EmailApp.CreateItem(olMailItem)

10. Use the ‘To property’ and assign the email address to whom you want to send the email to.

NewEmailItem.To = "”

11. If you want to CC anyone in the email, you can use the CC property.

NewEmailItem.CC = ""

12. Similarly, you can also use the BCC property.

NewEmailItem.BCC = "”

13. The next step in the process of writing an email is to give a subject.

NewEmailItem.Subject = "Test Email Demo”

14. Now, you can write the body of the email by using HTMLBody type.

NewEmailItem.HTMLBody = "Hi," & vbNewLine & vbNewLine & 

"This is a test email from Excel" & _

vbNewLine & vbNewLine & _

"Regards," & vbNewLine & _

"Angela"

To insert a new line, vbNewLine is used.

15. Now, if you want to attach the current workbook in your mail, you must first declare a variable source as a string. 

Dim Src As String

After the mail body, assign ThisWorkbook.FullName to Source variable.

Src = ThisWorkbook.FullName

Here, ThisWorkbook refers to the current workbook and .FullName refers to the full name of the worksheet.

Then, you can send the attachment using the attachment’s property.

NewEmailItem.Attachments.Add Src

16. If you want to send any other attachment, you have to pass in the file's location on your computer along with the extension of the file. 

EmailItem.Attachments.Add ("C:\Users\xxx\Desktop\Formula.pdf")

17. If you want to display the email to edit the email body manually or ensure everything looks correct, you can use the .Display method. 

NewEmailItem.Display True

18. Finally, to send the email to the mentioned email addresses, you can use the .Send method.

NewEmailItem.Send

Below is the complete code sample to send one email to multiple recipients with a .pdf attachment from the computer.

Fig: VBA Email Code Sample

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Conclusion

After reading this tutorial, you have learned how to send an email in Excel. This guide makes you understand how to write VBA scripts to send emails from MS Excel with attachments along with a hands-on example. If you want to learn more about Microsoft Excel, you can have a look at this course provided by Simplilearn: Business Analytics Certification Course with Excel. This Business Analytics certification course with Excel teaches you the basic concepts of data analysis and statistics. A few of the skills covered in this course are Excel analytics functions, Excel conditional formatting, Pivot tables, and slicers. 

Do you have any questions regarding this tutorial on sending an email in Excel? If you do, please put them in the comments section of this article; our team will be happy to assist you.

Happy learning!