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