WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Send Email from Excel

VBA Send Email from Excel

VBA Code to Send Emails From Excel

In VBA to Send Email from Excel we can automate our mailing feature so that we can automatically send emails to multiple users at a time, to do so we need to remember that it is done by outlook another product of outlook so we need to enable outlook scripting in VBA to do so and once it is done we use .Application method to use outlook features.

VBA’s versatility is just amazing. The reason why VBA coders love excel because using VBA, we not only can work within excel. Rather we can also access other Microsoft tools as well. We can access PowerPoint, Word, and Outlook by using VBA. The thing which impressed me was when I heard of “sending email from excel itself.” Yes, it is true. We can send emails from excel. In this article, we will show you how to send emails from excel with attachments using VBA Coding.

vba-send-mail-from-excel

Set Reference to Microsoft Office Library

We need to send emails from Outlook. Since Outlook is an outside object first thing we need to do is to set the object reference to “Microsoft Outlook 16.0 Object Library”.

  • In VBA, Go to Tools > References.

vba send email references1

  • Now we will see the object reference library. In this window, we need to set the reference to “Microsoft Outlook 16.0 Object Library.”

vba send email references1

  • After setting the object reference, click on, Ok.

Now we can access Outlook object in VBA coding.

13 Easy Steps to Send Emails from Excel

Writing the code to send an email with an attachment from excel is quite complicated, but worth spending some time.

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

Follow the below steps to write your first email excel macro.

Step #1

Start the subprocedure in VBA.

Code:

Sub SendEmail_Example1()

End Sub

vba send email example 1.1

Step #2

Declare the variable Outlook.Application

Code:

Dim EmailApp As Outlook.Application 'To refer to outlook application

vba send email example 1.2

Step #3

The above variable is an object variable. We need to create an instance of a new object separately. To create a new instance of the outside object, below is the code.

Code:

Set EmailApp = New Outlook.Application 'To launch outlook application

vba send email example 1.3

Step #4

Now to write the email, we declare one more variable as “Outlook.MailItem”.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Dim EmailItem As Outlook.MailItem 'To refer new outlook email

vba send email example 1.4

Step #5

In order to launch a new email, we need to set the reference to our previous variable as CreateItem.

Code:

Set EmailItem = EmailApp.CreateItem(olMailItem) 'To launch new outlook email

vba send email example 1.5

Ok, now the variable “EmailApp” will launch the outlook, and in the variable “EmailItem,” we can start writing the email.

Step #6

Now we need to be aware of what are the items we have while writing an email. The first thing is we need to decide to whom we are sending the email. So for this, we need to access the “TO” property.

vba send email example 1.6

Step #7

Enter the email Id of the receiver in double-quotes.

Code:

EmailItem.To = "Hi@gmail.com"

vba send email example 1.7

Step #8

After addressing the main receiver, if you would like to CC anyone in the email, we can use the “CC” property.

Code:

EmailItem.CC = "hello@gmail.com"

vba send email example 1.8

Step #9

After the CC, we can set the BCC email id as well.

Code:

EmailItem.BCC = "hhhh@gmail.com"

vba send email example 1.9

Step #10

Now we need to include the Subject of the email we are sending.

Code:

EmailItem.Subject = "Test Email From Excel VBA"

example 1.10

Step #11

Now we need to write the body of the email by using HTML Body type.

Code:

EmailItem.HTMLBody = "Hi," & vbNewLine & vbNewLine & "This is my first email from Excel" & _
                     vbNewLine & vbNewLine & _
                     "Regards," & vbNewLine & _
                     "VBA Coder" 'VbNewLine is the VBA Constant to insert a new line

example 1.11

Step #12

If we want to add an attachment as the current workbook, we are working on. Then we need to use attachments property. First, declare a variable source as a string.

Code:

Dim Source As String

example 1.14

Then in this variable, write ThisWorkbook.FullName after Email body.

Code:

Source = ThisWorkbook.FullName

example 1.15

In this VBA Code, ThisWorkbook is used for the current workbook and .FullName is used to get the full name of the worksheet.

Then write the following code to attach the file.

Code:

EmailItem.Attachments.Add Source

example 1.16

Step #13

Now finally, we need to send the email to the mentioned email IDs. This can be done by using the “Send” method.

Code:

EmailItem.Send

example 1.17

Ok, we are done with the coding part.

Code:

Sub SendEmail_Example1()

Dim EmailApp As Outlook.Application
Dim Source As String
Set EmailApp = New Outlook.Application

Dim EmailItem As Outlook.MailItem
Set EmailItem = EmailApp.CreateItem(olMailItem)

EmailItem.To = "Hi@gmail.com"
EmailItem.CC = "hello@gmail.com"
EmailItem.BCC = "hhhh@gmail.com"
EmailItem.Subject = "Test Email From Excel VBA"
EmailItem.HTMLBody = "Hi," & vbNewLine & vbNewLine & "This is my first email from Excel" & _
vbNewLine & vbNewLine & _
"Regards," & vbNewLine & _
"VBA Coder"
Source = ThisWorkbook.FullName
EmailItem.Attachments.Add Source

EmailItem.Send

End Sub

Run the above code, and it will send the email with the mentioned body of the email with the current workbook as the attachment.

Recommended Articles

This has been a guide to VBA Send Email from Excel. Here we learn how to write VBA code to send emails from excel with attachment along with an example and downloadable excel template. You can learn more about VBA from the following articles –

  • VBA TimeValue Function
  • Dictionary in VBA
  • Create Progress Bar in VBA
  • GetOpenFilename VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download VBA Send Email Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More