Tutorial Playlist

Excel Tutorial

Overview

Your One-Stop Solution to Know About Excel Worksheets

Lesson - 1

Your One-Stop Solution For Excel UserForms

Lesson - 2

The Best Guide on How to Send an Email in Excel

Lesson - 3

Your One-Stop Solution That Will Help You Learn How to Convert PDF to Excel

Lesson - 4

A One-Stop Solution for Acing the Excel MIS Report

Lesson - 5

All You Need to Know About Barcode in Excel

Lesson - 6

Your One-Stop Solution to Learn Mail-Merge in Excel and Its Implementation

Lesson - 7

A Comprehensive Guide on Excel Flash Fill

Lesson - 8

Slicers in Excel: The Ultimate Guide to Help You Design and Develop Excel Slicers

Lesson - 9

One-Stop Solution to Learn Everything About Excel Budget Template

Lesson - 10

All You Need to Learn About Match Function in Excel and Its Real-Time Implementation

Lesson - 11

Everything You Need to Learn on How to Create a Pivot Table From Multiple Sheets

Lesson - 12

The Major Fundamentals About XLookUp in Excel Along With Practical Examples

Lesson - 13

The Ultimate Guide to Learn How to Compare Two Columns in Excel

Lesson - 14

The Best Guide and Your One-Stop Solution to Master the Fill Series in Excel

Lesson - 15

What Is the DATE Function in Excel and How to Implement It?

Lesson - 16

Your One-Stop Solution to Designing and Implementing Hyperlinks in Excel

Lesson - 17

One-Stop Solution to Master Everything You Need to Know About Auto-Sum in Excel

Lesson - 18

DAX in Excel: The Complete Guide for DAX Functions and Formulas in Excel

Lesson - 19

A Perfect Guide for All You Need to Know About Data Formatting in Excel

Lesson - 20

The Perfect Guide to Learn How to Add Columns in Excel

Lesson - 21

The Perfect Guide That Will Explain to You How to Remove Page Break in Excel

Lesson - 22

The Perfect Guide to Explain to You How to Remove Blank Rows in Excel

Lesson - 23

The Perfect Guide to Learning How to Highlight Duplicates in Excel

Lesson - 24

One-Stop Solution Understand the Fundamentals of Indirect Function in Excel

Lesson - 25
The Best Guide on How to Send an Email in Excel

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.

EmailInExcel_1

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.

EmailInExcel_2.

      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.

EmailInExcel_3

        Fig: References

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

EmailInExcel_4.

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

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.

   EmailInExcel_5.    

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!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.