Macro to Send Email from Excel (5 Suitable Examples)

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”.

Use Macro to Send Email from Excel: 5 Ways

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

• 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.

Read More: Send Email from Excel VBA without Outlook

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

• Then, Save and Run this code.

We’ve successfully sent an email to our address.

Read More: How to Send Email from Excel with Body Using a Macro

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

• 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.

Read More: How to Apply Macro to Send Email from Excel with Attachment

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

• 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.

Read More: Excel Macro to Send Email Automatically

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

• Then, Save and Run the Module.

In conclusion, we’ve shown you yet another method of sending an email using VBA Macro from Excel.

Read More: Send Reminder Email Automatically from an Excel Worksheet Using VBA

Practice Section

We’ve added practice datasets for each method in the Excel file.

Download Practice Workbook

Conclusion

We’ve shown you 5 methods to use Macro to send an email from Excel. If you have any queries you can share them in the comment section below. Thanks for reading, keep excelling!

Related Articles

• VBA to Generate Multiple Lines in Email Body in Excel
• Excel Macro: Send Email to an Address in Cell
• Print to PDF and Email Using VBA in Excel
• How to Use Excel VBA to Paste Range into Email Body