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.
- Next, go to the Tool tab and select the option References.
- 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.
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’.
- 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.
- 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.
Follow the below steps to perform this method.
STEPS:
- First, right-click on sheet Date.
- Next, select the option ‘View Code’.
- 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.
- 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.
- 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’.
- So, the path of the file that we get:
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.
- A new window named ‘Project – VBAProject will open’.
- Secondly, right-click on the sheet name.
- Then, select Insert > Module.
- 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.
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?
Hello, JOSHUA KROGER!
Please Check the first and the third example. I drop the link here.
https://www.exceldemy.com/excel-macro-to-send-email-automatically/#1_Apply_Excel_VBA_Macro_to_Send_Email_Automatically_Based_on_Cell_Value
https://www.exceldemy.com/excel-macro-to-send-email-automatically/#3_Use_Excel_Macro_to_Send_Email_Automatically_with_Attachments
Hope you will get the solution.
Else you can try this! To use this code, first, you need to create a button.
Private Sub CommandButton1_Click()
On Error GoTo ErrHandler
Dim obj As Object
Set obj = CreateObject(“Outlook.Application”)
Dim objE As Object
Set objE = obj.CreateItem(olMailItem)
Dim rng As Range
Set rng = Range(“A4:A8” & Cells(Rows.Count, “A”).End(xlUp).Row)
Dim rng1 As Range
Dim int As Integer
Dim mailID, CCmailID As String
For Each cell In rng
If Trim(mailID) = “” Then
mailID = cell.Offset(1, 0).Value
Else
If Trim(CCmailID) = “” Then
CCmailID = cell.Offset(1, 0).Value
Else
CCmailID = CCmailID & vbCrLf & “;” & cell.Offset(1, 0).Value
End If
End If
Next cell
Set rng = Nothing
With objE
.To = mailID
.CC = CCmailID
.Subject = “Sending Email with VBA.”
.Body = “This is a Sample Mail.”
.Display
End With
Set objE = Nothing: Set obj = Nothing
ErrHandler:
‘
End Sub
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
Hello, DIANA!
There is no problem with your code. What’s the problem actually?
Can you please email me the dataset here; [email protected]
Or you can visit the following article, this may help you to fix your problem.
https://www.exceldemy.com/automatically-send-email-from-excel-based-on-date/
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.