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.

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.

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.

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.

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.

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

Step 5: Define a subprocedure as shown below.

Code:

Sub EmailExample()

End Sub

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

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

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

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

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)

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"

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"

Similarly, we can use the BCC property.

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.

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

Code:

newmail.Subject = "This is an automated Email"

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"

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

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

Code:

newmail.Attachments.Add Sr

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

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 with Examples
2. How to Use YEAR Function in Excel?
3. Tips on Using VBA Outlook
4. Tutorials on Excel Date Function