Excel Macro to Send Email Automatically (3 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

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:

  • First, from your dataset, go to the Developer tab. Select the option Visual Basic.

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. Applying 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, right–click 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: Excel Macro: Send Email to an Address in Cell


2. Sending Email Automatically 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 Apply Macro to Send Email from Excel with Attachment


3. Using 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: Send Email from Excel VBA without Outlook


Download Practice Workbook

You can download the practice workbook from here.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

6 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 DataMicrosoftSignaturesrm.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

  3. If I keep a running spreadsheet, how can I send only to those not sent to before?

    Thank you very much.
    You have been so very helpful.

    • Hi, Frank!
      You can create a helper column and input “Yes” for the sent mails. Then, you can run the VBA code to sent the values without the “Yes” values. That way, the mail will be sent only to those not sent to before.
      You can also email us your Excel file with detailed instructions to [email protected], so that we can give you a proper code to solve the problem.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo