How to Send Email in Excel Using VBA?

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. 

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

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 = "xxxxx750@yahoo.in”

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

NewEmailItem.CC = "xxxxxx94@gmail.com"

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

NewEmailItem.BCC = "xxxyyzz@gmail.com”

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

Become a Data Scientist with Hands-on Training!

Data Scientist Master’s ProgramExplore Program

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!