VBA Code to Send Emails From Excel

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Send Email from Excel (wallstreetmojo.com)

Set Reference to Microsoft Office Library

Now, we can access Outlook objects in VBA coding.

  • 1.     In VBA, Go to Tools > References. Now, we will see the object reference library. In this window, we need to set the reference to “Microsoft Outlook 16.0 Object Library.” After setting the object reference, click on “OK.”

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.

Follow the below steps to write your first email excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more.

Step #1

Start the sub procedure in VBASubprocedure In VBASUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more.

Code:

Sub SendEmail_Example1()

End Sub

Step #2

Declare the variable Outlook.Application

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

Step #3

The above variable is an object variable. Therefore, we need to create an instance of a new object separately. Below is the code to create a new instance of the external object.

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

Step #4

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

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

Step #5

To launch a new email, we need to set the reference to our previous variable as “CreateItem.”

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

Now, the variable “EmailApp” will launch outlook. In the variable “EmailItem,” we can start writing the email.

Step #6

We need to be aware of our items while writing an email. First, we need to decide to whom we are sending the email. So for this, we need to access the “TO” property.

Step #7

Enter the email ID of the receiver in double quotes.

EmailItem.To = “[email protected]”

Step #8

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

EmailItem.CC = “[email protected]”

Step #9

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

EmailItem.BCC = “[email protected]”

Step #10

We need to include the subject of the email we are sending.

EmailItem.Subject = “Test Email From Excel VBA”

Step #11

We need to write the email body using HTML body type.

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

Step #12

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

Dim Source As String

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

Source = ThisWorkbook.FullName

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.

EmailItem.Attachments.Add Source

Step #13

Finally, we need to send the email to the mentioned email IDs. We can do this by using the “Send” method.

EmailItem.Send

We have completed the coding part.

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 = “[email protected]” EmailItem.CC = “[email protected]” EmailItem.BCC = “[email protected]” 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. It will send the email with the mentioned body with the current workbook as the attachment.

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

  • VBA TimeValue FunctionDictionary in VBACreate Progress Bar in VBAGetOpenFilename VBA