EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Input & Output Functions VBA Send Email From Excel
Secondary Sidebar
VBA Input & Output Functions
  • VBA Input & Output
    • VBA Login
    • VBA Outlook
    • VBA Print
    • VBA UserForm
    • VBA Close UserForm
    • VBA Send Email From Excel
    • VBA Debug Print

VBA Send Email From Excel

By Madhuri ThakurMadhuri Thakur

VBA Send Email From Excel

VBA Send Email From Excel

VBA is not only limited to data in excel. We can send emails through VBA and this is what we will learn in this article. It requires considerable knowledge of VBA and its methods to write the code to send emails from excel. Before we move to write and sending emails from excel let us know what this automation means. This type of automation means accessing a feature of another application using any other application. Now the mailing feature is offered by Microsoft in Outlook, we will use methods and properties of outlook in excel to send emails. To send an email we need to know the basic of email also. In layman’s term, what is the process and what is the requirement to send an email? An Email consists of an email address of the sender if there is a CC (Carbon Copy ) or a BCC and a subject line with an email body.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,704 ratings)

How to Send Emails From Excel VBA?

Let us learn how to send emails through outlook from excel in VBA by an example. In this example, we will also send an attachment to the receiver, the same excel file we will be writing the code.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA Send Email Excel Template here – VBA Send Email Excel Template

Follow the below steps to send email from Excel Using VBA code:

Step 1: In the Developer Tab click on Visual Basic to open the VB Editor.

VBA Send Email From Excel 1

Before we move into writing codes to send an email, we need to know this that outlook is an external object and we need to refer it in the VBA.

Step 2: Go to Tools and then select References.

Select References

Step 3: Clicking on the reference will open a wizard box for us, find a reference for Microsoft Outlook Object library and check it and then click on Ok.

Microsoft Outlook Object library

Step 4: Click on insert tab and insert a module in the VBA project.

VBA Send Email From Excel 4

Step 5: Define a subprocedure as shown below.

Code:

Sub EmailExample()

End Sub

VBA Send Email From Excel 5

Step 6: By giving reference to the outlook above, we can now access the properties of outlook in VBA. Now let us declare a variable as an outlook application.

Code:

Dim Email As Outlook.Application

VBA Send Email From Excel 6

Step 7: Like FSO, this variable is an object variable, so to access any other application we need to create some instances, create an instance as shown below using the SET keyword.

Code:

Set Email = New Outlook.Application

VBA Send Email From Excel 7

Step 8: Since we want to send an attachment to the receiver we need to declare one variable as String which will hold the path for the attachment.

Code:

Dim Sr As String

VBA Send Email From Excel 8

Step 9: Now let us start with the mailing part in this code, to send an email we need to define another variable which will use the property of outlook to refer to a new email as shown below.

Code:

Dim newmail As Outlook.MailItem

VBA Send Email From Excel 9

Step 10: Similar to above with using another application in the example we need to create instances, now we need to create an instance for a new email which will open the new email using the set keyword.

Code:

Set newmail = Email.CreateItem(olMailItem)

VBA Send Email From Excel 10

Before we move further let me explain our progress so far, the first instance will open outlook for us while the second instance will open the new email for us.

Step 11: As I have explained above about what is the requirement to send an email. The first requirement is a receiver which is “To” in an email. So let us use the To property of outlook in excel as follows.

Code:

newmail.To = "abc@gmail.com"

VBA Send Email From Excel 11

Step 12: Since we have used the To property we have some another feature to use such as the Carbon Copy or the CC property of outlook.

Code:

newmail.CC = "xyz@gmail.com"

Carbon Copy

Similarly, we can use the BCC property.

Note: BCC property is used when we want to hide the email address of the BCC receiver from the other receivers.

What is the next step in sending an email?

Step 13: It is subject. When we write the instance name with a dot operator we can see the option for a subject as follows.

Subject IntelliSense

Step 14: Press Tab on the subject IntelliSense and write a random subject as shown below.

Code:

newmail.Subject = "This is an automated Email"

VBA Send Email From Excel 14

Step 15: The next step in writing an email is a body for the email. Like the properties, we used above with the instance let us use the body property of the outlook to write the body as follows.

Code:

newmail.HTMLBody = "Hi," & vbNewLine & vbNewLine & "This is a test email from Excel" & _
vbNewLine & vbNewLine & _
"Regards," & vbNewLine & _
"VBA Coder"

Body for Email

Step 16: Now we have created an email with a body and subject line in it. The next step is to add an attachment to the email. Since we want to send the current worksheet to the receiver we will use the path as follows,

Code:

Sr = ThisWorkbook.FullName

VBA Send Email From Excel 16

Step 17: Now we can send the attachment using the attachment property as shown below.

Code:

newmail.Attachments.Add Sr

Attachment property

Step 18: Now finally we need to send the email. Like in outlook we press the send button to send an email, similarly, we will use the send properties of outlook as follows.

Code:

newmail.Send

VBA Send Email From Excel 18

Final Full code

So below is the final code on how to send an email from Excel with the help of VBA.

Code:

Sub EmailExample()

Dim Email As Outlook.Application
Set Email = New Outlook.Application
Dim Sr As String
Dim newmail As Outlook.MailItem
Set newmail = Email.CreateItem(olMailItem)

newmail.To = "abc@gmail.com"
newmail.CC = "xyz@gmail.com"
newmail.Subject = "This is an automated Email"
newmail.HTMLBody = "Hi," & vbNewLine & vbNewLine & "This is a test email from Excel" & _
vbNewLine & vbNewLine & _
"Regards," & vbNewLine & _
"VBA Coder"
Sr = ThisWorkbook.FullName
newmail.Attachments.Add Sr
newmail.Send

End Sub

When we run the above code we need to wait for a few seconds for the code to execute and we can check out sent box in outlook that the email has been sent through excel.

Things to Remember

  • We use another application to send an email from excel.
  • To use another application we create instances.
  • Before using outlook as another application we need to refer to Outlook objects from the reference tab.
  • We need to know the requirements of an email to send an email.

Recommended Articles

This is a guide to VBA Send Email From Excel. Here we discuss how to send emails with attachments from excel using VBA code along with an example and downloadable excel template. Below are some useful excel articles related to VBA –

  1. VBA Hyperlink
  2. VBA Outlook
  3. VBA Get Cell Value
  4. VBA Web Scraping
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more