Excel Macro to Send Email Automatically (3 Suitable Examples)

This tutorial will illustrate how to use excel macro to send an email automatically. We can configure our mailing feature using VBA macros. So, using the VBA macro we can send an email to multiple users at the same time. We must have Outlook installed on our device to send email automatically with a macro. Because the code that we will insert will use Outlook to send emails to the recipients.


Download Practice Workbook

You can download the practice workbook from here.


3 Suitable Examples of Excel Macro to Send Email Automatically

Throughout this article, we will demonstrate 3 suitable examples of using excel macro to send an email automatically to the recipients. Before starting to illustrate the example we need to fix a thing in our excel sheet. Complete the below steps before applying macro to send email automatically.

STEPS:

3 Suitable Examples of Excel Macro to Send Email Automatically

  • Next, go to the Tool tab and select the option References.

3 Suitable Examples of Excel Macro to Send Email Automatically

  • A new dialogue box named ‘References – VBAProject’ will open.
  • Finally, check the option ‘Microsoft Office 16.0 Object Library’ and click on OK.


1. Apply Excel VBA Macro to Send Email Automatically Based on Cell Value

First and foremost, we will apply the excel VBA macro to send an email automatically based on a particular cell value in our dataset. To illustrate this example we will use the following dataset. We will write a code that will send an email automatically if the cell value in cell D6 is greater than 400.

Apply Excel VBA Macro to Send Email Automatically Based on Cell Value

Let’s see the steps to perform this action.

STEPS:

  • To begin with, rightclick on the sheet ‘Based on Cell’.
  • In addition, select the option ‘View Code’.

Apply Excel VBA Macro to Send Email Automatically Based on Cell Value

  • The above action will open a blank VBA code window for that worksheet. Another way to open that code window is to press Alt + F11.
  • Furthermore, type the following code in that code window:
Dim rg As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set rg = Intersect(Range("D6"), Target)
If rg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 400 Then
Call send_mail_outlook
End If
End Sub
Sub send_mail_outlook()
Dim z As Object
Dim y As Object
Dim b As String
Set z = CreateObject("Outlook.Application")
Set y = z.CreateItem(0)
b = "Hello!" & vbNewLine & vbNewLine & _
"Hope you are well" & vbNewLine & _
"Visit our site Exceldemy"
On Error Resume Next
With y
.To = "Address"
.cc = ""
.BCC = ""
.Subject = "send mail based on cell value"
.Body = b
.Display
End With
On Error GoTo 0
Set y = Nothing
Set z = Nothing
End Sub
  • Then, click the Run button or press the F5 key to run the code.

Apply Excel VBA Macro to Send Email Automatically Based on Cell Value

  • A new dialogue box named Macros will appear.
  • After that, in the Macro Name field select the macro ‘send_mail_outlook’.
  • Now click on the Run button.

  • Finally, from now when the cell value in cell D6 > 400 an email in Outlook will generate automatically with specific recipients. We have to just click on the Send button to send the email.

Read More: Automatically Send Emails from Excel Based on Cell Content (2 Methods)


2. Automatically Sending Email Based on Due Date with VBA Macro

In the second method, we will use the Excel VBA macro to send an email automatically if the due date of any project is close. This is something like a reminder. We use the following dataset to illustrate this example. The dataset contains emails of different salespeople, messages, and the due date of their project.

Automatically Sending Email Based on Due Date with VBA Macro

Follow the below steps to perform this method.

STEPS:

  • First, right-click on sheet Date.
  • Next, select the option ‘View Code’.

Automatically Sending Email Based on Due Date with VBA Macro

  • It opens a blank VBA code window for the active worksheet. We can also press Alt + F11 to get that code window.
  • Then, insert the following code in that code window:
Public Sub Based_on_Date()
Dim aRgDate As Range
Dim aRgSend As Range
Dim aRgText As Range
Dim aRgDone As Range
Dim aOutApp As Object
Dim aMailItem As Object
Dim aLastRow As Long
Dim CrLf As String
Dim aMailBody As String
Dim zRgDateVal As String
Dim zRgSendVal As String
Dim aMailSubject As String
Dim j As Long
On Error Resume Next
Set aRgDate = Application.InputBox("select the column of due date:", _
"Send Mail Base on Date", , , , , , 8)
If aRgDate Is Nothing Then Exit Sub
Set aRgSend = Application.InputBox("select the email recipients column:", _
"Send Mail Base on Date", , , , , , 8)
If aRgSend Is Nothing Then Exit Sub
Set aRgText = Application.InputBox("Select the content column of email:", _
"Send Mail Base on Date", , , , , , 8)
If aRgText Is Nothing Then Exit Sub
aLastRow = aRgDate.Rows.Count
Set aRgDate = aRgDate(1)
Set aRgSend = aRgSend(1)
Set aRgText = aRgText(1)
Set aOutApp = CreateObject("Outlook.Application")
For j = 1 To aLastRow
zRgDateVal = ""
zRgDateVal = aRgDate.Offset(j - 1).Value
If zRgDateVal <> "" Then
If CDate(zRgDateVal) - Date <= 7 And CDate(zRgDateVal) - Date > 0 Then
zRgSendVal = aRgSend.Offset(j - 1).Value
aMailSubject = aRgText.Offset(j - 1).Value & " on " & zRgDateVal
CrLf = "<br><br>"
aMailBody = "<HTML><BODY>"
aMailBody = aMailBody & "Hello " & zRgSendVal & CrLf
aMailBody = aMailBody & "Message: " & aRgText.Offset(j - 1).Value & CrLf
aMailBody = aMailBody & "</BODY></HTML>"
Set aMailItem = aOutApp.CreateItem(0)
With aMailItem
.Subject = aMailSubject
.To = zRgSendVal
.HTMLBody = aMailBody
.Display
End With
Set aMailItem = Nothing
End If
End If
Next
Set aOutApp = Nothing
End Sub
  • Now, use the Run button or the F5 key to run the code.

Automatically Sending Email Based on Due Date with VBA Macro

  • A new dialogue box will pop up.
  • Afterward, in the input field of that dialogue box select the due date column range D$5:$D$9. Then, click on OK.

Automatically Sending Email Based on Due Date with VBA Macro

  • One more dialogue box will pop up.
  • Furthermore, in the input field select the column range B$5:$B$9 that contains the email addresses and click on OK.

  • Moreover, one more window will pop up. Select the message range $C$5:$C$9 in the input field of the pop window.

  • In the end, we can see results like the following image. We get 3 emails that are automatically created in 3 different windows of Outlook. This will not create mail for the first two email addresses. Because the due date of those two projects is over.

Read More: How to Automatically Send Email from Excel Based on Date


Similar Readings


3. Use Excel Macro to Send Email Automatically with Attachments

In the last example, we will see how we can develop an excel macro to send an email automatically with attachments. Suppose we have an attachment in the following image. We want to send this attachment through an email using excel VBA macro. To do this we need the path of this excel file. Here are the steps for that:

  • Select the file Attachment.xlsx’.
  • Click on the option ‘Copy Path’.

Use Excel Macro to Send Email Automatically with Attachments

  • So, the path of the file that we get:
E:\Exceldemy\Attachment.xlsx

We will insert this path in our macro code to send this file by email. Follow the below steps to do this.

STEPS:

  • Firstly, go to the Developer tab and select the option Visual Basic.

Use Excel Macro to Send Email Automatically with Attachments

  • A new window named ‘Project – VBAProject will open’.
  • Secondly, right-click on the sheet name.
  • Then, select Insert > Module.

Use Excel Macro to Send Email Automatically with Attachments

  • The above command will open a blank VBA
  • Thirdly, type the following code in that Module:
Sub send_Email_complete()
Dim MyOutlook As Object
Set MyOutlook = CreateObject("Outlook.Application")
Dim MyMail As Object
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = "[email protected]"
MyMail.cc = "[email protected]"
MyMail.BCC = "[email protected]"
MyMail.Subject = "Sending Email with VBA."
MyMail.Body = "This is a Sample Mail."
Attached_File = "E:\Exceldemy\Attachment.xlsx"
MyMail.Attachments.Add Attached_File
MyMail.send
End Sub
  • Then, hit the F5 key or click the Run button to run the code.

  • Lastly, the code will send the attachment to the provided emails in the code. The code sends emails by Outlook. So, click on the Allow button to let Outlook send the attachment to the given emails.

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


Conclusion

In conclusion, this article shows 3 examples of using excel VBA macro to send mail automatically. Download the sample worksheet given in this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to reply to your message as soon as possible. Keep an eye out for more inventive Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

4 Comments
  1. Hello, is there a way to add multiple email addresses to the .to or .cc list through the macro/code vs an cell list/range?

  2. I have a spreadsheet that I want to send email from depending upon the date. I have it doing everything I want except it’s not putting in who the email should go to. This is the code I have been using. My email addresses begin in Cell AK6. Can you tell me how to fix it? Thank you.

    Sub Macro1()
    Dim rngCell As Range
    Dim rngMyDataSet As Range
    Dim Rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim EmailSubject As String
    Dim EmailSendTo As String
    Dim MailBody As String
    Dim EmailRecipient As String
    Dim Signature As String
    Application.ScreenUpdating = False
    With ActiveSheet
    If .FilterMode Then .ShowAllData
    Set Rng = .Range(“AK6”, .Cells(.Rows.Count, 1).End(xlUp))
    End With
    For Each rngCell In Rng
    If rngCell.Offset(0, 6) > 0 Then

    ElseIf rngCell.Offset(0, 5) > Evaluate(“Today() +7”) And _
    rngCell.Offset(0, 5).Value <= Evaluate("Today() +30") Then
    rngCell.Offset(0, 6).Value = Date

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "According to my records, your contract " & Range("A1").Value & " is due for review on " & rngCell.Offset(0, 5).Value & vbNewLine & _
    "Please review this contract prior to the pertinent date and email me with any changes you make to this contract. If it is renewed, please fill out the Contract Cover Sheet which can be found in the Everyone folder and send me the new original contract."
    EmailSendTo = rngCell.Offset(0, 0).Value
    EmailSubject = Sheets("sheet1").Range("A6").Value
    EmailRecipient = rngCell.Offset(0, 1).Value
    Signature = "C:\Documents and Settings\" & Environ("rmm") & _
    "\Application Data\Microsoft\Signatures\rm.htm"
    On Error Resume Next
    With OutMail
    .to = EmailSendTo
    .CC = "[email protected]"
    .BCC = ""
    .Subject = EmailSubject
    .Body = strbody
    .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

    End If

    Next rngCell
    Application.ScreenUpdating = True
    End Sub

Leave a reply

ExcelDemy
Logo