Macro to Send Email from Excel (5 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we’ll show you 5 Macro to send email from Excel. To demonstrate our methods, we’ve selected a dataset with 3 columns: “Name”, “Email”, and “City”.

Table of Contents hide

Download Practice Workbook

5 Ways to Use Macro to Send Email from Excel

1. Use of Outlook Object Library to Send Email

2. Macro for Sending Email from Gmail Account in Excel

3. Send Email to a List of Recipients from a Column

4. Macro to Send Single Sheet Using Email

5. Macro to Send Email Based on Cell Value

Practice Section

Conclusion

Download Practice Workbook

Using Macro to send Email.xlsm

5 Ways to Use Macro to Send Email from Excel

1. Use of Outlook Object Library to Send Email

For the first Macro, we’re going to enable “Microsoft Outlook 16.0 Object Library” to send an email from Excel. Moreover, we need to log in to our Outlook account in Excel.

Steps:

In the beginning, we’re going to bring up the Visual Basic window.

Firstly, from the Developer tab >>> select Visual Basic.

Alternatively, you can press ALT + F11 to display the VBA window.

Secondly, from Tools >>> select “References…”.

A new dialog box will appear.

Thirdly, select “Microsoft Outlook 16.0 Object Library”, and press OK.

Thus, we’ll enable Outlook Object Library.

Them from Insert >>> select Module.

We’ll type our code here.

After that, type the following code.

Sub Macro_Send_Email()
Dim eApp As Outlook.Application
Dim eSource As String
Set eApp = New Outlook.Application
Dim eItem As Outlook.MailItem
Set eItem = eApp.CreateItem(olMailItem)
eItem.To = Range("C5").Value
'These items are optional
'eItem.CC = "[email protected]"
'etem.BCC = "[email protected]"
eItem.Subject = "Sending Email using VBA from Excel"
eItem.Body = "Hello," & vbNewLine & "Hope this email finds you well." & _
vbNewLine & vbNewLine & _
"Sincerely," & vbNewLine & "Exceldemy"
'If you want to attach this workbook, then uncomment these two lines from below
'Source = ThisWorkbook.FullName
'eItem.Attachments.Add Source
eItem.Display 'can use .Send
End Sub

VBA Code Breakdown

Firstly, we’re calling our Sub Procedure Macro_Send_Email.
Secondly, we’re declaring the variable types.
Thirdly, we’re selecting Outlook as our Mail Application.
Then, we’re selecting our email sending address from cell C5.
After that, the email content is set in our code.
Finally, “VBA Display Property” is used here to display our email. Therefore, we’ll need to press Send manually to send the emails. Moreover, we can use “Send Property” to send emails without displaying.

After that, Save and close the Module.

Now, we’ll Run the code.

Firstly, from the Developer tab >>> select Macros.

The Macro dialog box will appear.

Secondly, select our Sub Procedure “Macro_Send_Email”.
Finally, press Run.

After executing the code, we’ll see the email window. We can click on Send. Thus, we’ve shown you the first method of sending an email from excel using VBA.

2. Macro for Sending Email from Gmail Account in Excel

For this method, we need less secure app access from the Gmail account. Additionally, we’ll need to enable Microsoft CDO from the References menu.

Steps:

Firstly, as shown in the first method, bring up the References dialog box.
Secondly, select “Microsoft CDO for Windows 2000 Library” and press OK.

Thirdly, go to Security from your Google Account settings.
Finally, turn on Less secure app access.

Now, we’ll input our Macro code.

Firstly, as shown in method 1, bring up the Module window and type this code.

Option Explicit
Sub Send_Gmail_Macro()
Dim cMail As Object
Dim cConfig As Object
Dim sConfig As Variant
Dim cSubject As String
Dim cFrom As String
Dim cTo As String
Dim cCC As String
Dim cBcc As String
Dim cBody As String
cSubject = "Macro to Send Gmail"
cFrom = "[email protected]"
cTo = "[email protected]"
cBody = "Hello. This is an automated message. Please don't reply"
Set cMail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set cConfig = CreateObject("CDO.Configuration")
cConfig.Load -1
Set sConfig = cConfig.Fields
With sConfig
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "SenderGmailPassword"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    .Update
    End With
    With cMail
    Set .Configuration = cConfig
    End With
    cMail.Subject = cSubject
    cMail.From = cFrom
    cMail.To = cTo
    cMail.TextBody = cBody
    cMail.CC = cCC
    cMail.BCC = cBcc
    cMail.Send
Error_Handling:
    If Err.Description <> "" Then MsgBox Err.Description
    End Sub

VBA Code Breakdown

Firstly, we’re calling our Sub Procedure Send_Gmail_Macro.
Secondly, we’re declaring the variable types.
Thirdly, we’re setting the email content in our code.
Then, we’re providing our login credentials. You need to type your own ID and Password here.
After that, we’ve set out port to 465.
Finally, we’re sending our email.

Then, Save and Run this code.

We’ve successfully sent an email to our address.

3. Send Email to a List of Recipients from a Column

For the third method, we’re going to send emails to 7 people using Macro from Excel. We’ll find the last row of our dataset, hence our code will work for a longer list. We’ll send emails from the cell C5:C10 range.

Steps:

Firstly, as shown in method 1, bring up the Module window and type this code.

Sub Macro_Send_Email_From_A_List()
   Dim pApp As Object
   Dim pMail As Object
   Dim z As Integer
   Dim eList As String
   Dim eRow As Long
   Set pApp = CreateObject("Outlook.Application")
   Set pMail = pApp.CreateItem(0)
   eRow = Range("C:C").SpecialCells(xlCellTypeLastCell).Row - 1
   With pMail
       eList = ""
       For z = 5 To eRow
           If eList = "" Then
               eList = Cells(z, 3).Value
           Else
               eList = eList & ";" & Cells(z, 3).Value
           End If
       Next z
       .BCC = eList
       .Subject = "Hello There"
       .Body = "This Message is brought to you by Exceldemy."
       .Display 'You can use .Send here
   End With
   Set pMail = Nothing
   Set pApp = Nothing
End Sub

VBA Code Breakdown

Firstly, we’re calling our Sub Procedure Macro_Send_Email_From_A_List.
Secondly, we’re declaring the variable types.
Thirdly, we’re selecting Outlook as our Mail Application.
Then, we’re finding the last row, which is 10 for our dataset.
After that, as our email starts from row 5 we’ve input 5 as the starting value for “variable z”. Moreover, our emails are on the C column, hence we’ve input 3 inside the Cells property.
Then, we’re setting the email content in our code.
Finally, “.Display” is used here to display our email. Therefore, we’ll need to press Send manually to send the emails. Moreover, we can use “.Send” to send email without displaying.

Then, Save and Run the Module.

We can see that all our emails are displayed in BCC. In conclusion, we can simply press Send to complete our task.

Similar Readings

4. Macro to Send Single Sheet Using Email

In this section, we’ll send the Active Worksheet to our target person. Here, we’ll need to pick the location of our Excel file.

Steps:

Firstly, as shown in method 1, bring up the Module window and type this code.

Sub Macro_Email_Single_Sheet()
    Dim pApp As Object
    Dim pMail As Object
    Dim zBook As Workbook
    Dim fxName As String
    'Dim zSheet As Worksheet
    'Dim shtName As String
    Application.ScreenUpdating = False
    ActiveSheet.Copy
    Set zBook = ActiveWorkbook
    fxName = zBook.Worksheets(1).Name
    On Error Resume Next
    Kill "C:\Users\Rafi\OneDrive\Desktop\Softeko\47\" & fxName
    On Error GoTo 0
    zBook.SaveAs FileName:="C:\Users\Rafi\OneDrive\Desktop\Softeko\47\" & fxName
    Set pApp = CreateObject("Outlook.Application")
    Set pMail = pApp.CreateItem(0)
    With pMail
        .To = "[email protected]"
        .Subject = "Macro to Send Single Sheet via Email"
        .Body = "Dear RecieverName," & vbCrLf & vbCrLf & _
          "Your requested file is attached"
        .Attachments.Add zBook.FullName
        .Display
    End With
    zBook.ChangeFileAccess Mode:=xlReadOnly
    Kill zBook.FullName
    zBook.Close SaveChanges:=False
    Application.ScreenUpdating = True
    Set pMail = Nothing
    Set pApp = Nothing
End Sub

VBA Code Breakdown

Firstly, we’re calling our Sub Procedure Macro_Email_Single_Sheet.
Secondly, we’re declaring the variable types.
Thirdly, we’re copying the Active Sheet and saving it as a separate Workbook.
After that, we’re selecting Outlook as our Mail Application.
Then, we’re setting the email content in our code.
After that, we’ve attached the Sheet to the email.
Finally, use “.Display” to display our email. Therefore, we’ll need to press Send manually to send the emails. Moreover, we can use “.Send” to send email without displaying.

Then, Save and Run the Module.

We’ll see the Sheet name in the window. Press Send to complete the task.

We can open the file and verify our code is working.

5. Macro to Send Email Based on Cell Value

For the last method, we’ve changed our dataset a little bit. We’ve added the “Payment Due” column to the dataset. Here, we’ll send an email that contains the city “Obama”. We can clearly see that row 5 contains it, hence we’re gonna send an email to that person only.

Steps:

Firstly, as shown in method 1, bring up the Module window and type this code.

Option Explicit
Sub Send_Email_Condition()
    Dim xSheet As Worksheet
    Dim mAddress As String, mSubject As String, eName As String
    Dim eRow As Long, x As Long
    Set xSheet = ThisWorkbook.Sheets("Conditions")
    With xSheet
        eRow = .Cells(.Rows.Count, 5).End(xlUp).Row
        For x = 5 To eRow
            If .Cells(x, 4) >= 1 And .Cells(x, 5) = "Obama" Then
                mAddress = .Cells(x, 3)
                mSubject = "Request For Payment"
                eName = .Cells(x, 2)
                Call Send_Email_With_Multiple_Condition(mAddress, mSubject, eName)
            End If
        Next x
    End With
End Sub
Sub Send_Email_With_Multiple_Condition(mAddress As String, mSubject As String, eName As String)
    Dim pApp As Object
    Dim pMail As Object
    Set pApp = CreateObject("Outlook.Application")
    Set pMail = pApp.CreateItem(0)
    With pMail
        .To = mAddress
        .CC = ""
        .BCC = ""
        .Subject = mSubject
        .Body = "Mr./Mrs. " & eName & ", Please pay the due amount within the next week." _
        & vbNewLine & "The exact amount is attached with this email."
        .Attachments.Add ActiveWorkbook.FullName 'Send The File via Email
        .Display 'We can use .Send here too
    End With
    Set pMail = Nothing
    Set pApp = Nothing
End Sub

VBA Code Breakdown

Firstly, we’re calling our first Sub Procedure Send_Email_Condition.
Secondly, we’re declaring the Variable types and setting “Conditions” as our Sheet.
Thirdly, the last row number is found. Moreover, our value starts from row 5, hence we’ve put row 5 to the last row in our code.
Then, call our second Sub Procedure Send_Email_With_Multiple_Condition.
After that, we’re selecting Outlook as our Mail Application.
Then, the email content is set in our code.
Here, we’re attaching the Excel file with the email using the Attachment method.
After that, “.Display” is used here to display our email. Therefore, we’ll need to press Send manually to send the emails. Moreover, we can use “.Send” to send email without displaying.