Automatically sending email allows us to design messages that are one-size-fits-all and deliver them to users. By generating and sending automatic emails, we may save time. Since it enables you to send emails at particular times, email automation is an excellent approach to engaging with potential consumers. The capacity to send email notifications to the right people at the right moment is the most valuable feature of email automation. In this article, we will demonstrate different VBA Macros of excel that automatically send an email when the condition is met.
How to Send Email Automatically When Condition Met in Excel: 3 Methods
Whenever a condition is met, we frequently need to send emails to our customers. Using VBA macros, we can customize our mailing functionality. As a result, we may send an email to numerous people at the same time using the VBA technique. To send an email automatically with a macro, we must have Outlook preinstalled on our computer. So the code we will put will send emails to the recipients using Outlook.
1. Excel VBA Macro to Send Email Automatically Based on a Cell Value
We will use an excel VBA macro to automatically send an email based on a certain column value in our dataset. The dataset below will be used to demonstrate this example. If the cell value in cell D5 is larger than 10, we’ll develop a function that sends an email automatically.
Suppose, we have the following dataset of some customer details of a super shop. The dataset contains some customers’ names in column B, their email addresses in column C, and some dues for their purchasing products in column D. Now, we want to send emails to them to request for paying the due bills, but here we have a condition which we follow: if the customer’s bill is greater than 10 only then we will send them emails. So, let’s see the procedures to send email automatically based on a cell value when the condition is met.
STEPS:
- Firstly, go to the Developer tab from the ribbon.
- Secondly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.
- Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.
- This will appear in the Visual Basic Editor where we write our codes to create a table from range.
- And, copy and paste the VBA code shown below.
VBA Code:
Dim r As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set r = Intersect(Range("D5"), Target)
If r Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 10 Then
Call Send_Mail_Automatically1
End If
End Sub
Sub Send_Mail_Automatically1()
Dim ob1 As Object
Dim ob2 As Object
Dim str As String
Set ob1 = CreateObject("Outlook.Application")
Set ob2 = ob1.CreateItem(0)
str = "Hello!" & vbNewLine & vbNewLine & "To prevent further costs," _
& vbNewLine & "please pay before the deadline."
On Error Resume Next
With ob2
.To = Range("C5").Value
.cc = ""
.BCC = ""
.Subject = "Request to Pay Bill"
.Body = str
.Send
End With
On Error GoTo 0
Set ob2 = Nothing
Set ob1 = Nothing
End Sub- After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.
- Then, a Macros dialog will show up. Click on the right Macros then, hit the Run button.
- Now, if you go to your Outlook application and check the inbox, we will find the mail that you just sent via VBA Macro from Excel.
VBA Code Explanation
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Su
Set r = Intersect(Range("D5"), Target)
If r Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 10 Then
Call Send_Mail_Automatically1
End If
End SubWe will be using a Private Sub for this. Because we won’t be using the Macro window to run this code. When the value of a cell change, this code will execute automatically. We use Private Sub with the Worksheet Change event. Then, reduce the number of cells to one, which is D5. After that, see if it’s more than 10. Finally, if the condition is fulfilled, the Send_Email_Automatically1 Sub Procedure will run.
Sub Send_Mail_Automatically1()
Dim ob1 As Object
Dim ob2 As Object
Dim str As String
Set ob1 = CreateObject("Outlook.Application")
Set ob2 = ob1.CreateItem(0)
str = "Hello!" & vbNewLine & vbNewLine & "To prevent further costs," & vbNewLine & "please pay before the deadline."
On Error Resume Next
With ob2
.To = Range("C5").Value
.cc = ""
.BCC = ""
.Subject = "Request to Pay Bill"
.Body = str
.SendHere, we use the Send_Email_Automatically1 Sub Procedure. Then, we declare the variable types. We use Outlook as our email client. Then, we enter the email content into our code; str is showing the email body, and then we want to send the email to the cell value C5, where our customers’ emails are stored, we put the subject on ‘.Subject’. Following that, ‘.Send’ is utilized to send our email.
Read More: How to Send Excel File to Email Automatically
2. Send Email Automatically Based on a Due Date Using VBA Code
In this method, we will utilize an Excel VBA macro to automatically send an email as the bill’s due date approaches. This serves as a kind of reminder. To demonstrate this, we will use the dataset below. And the dataset contains some customer names in column B, their email address in column C, the message we want to send in column D, and the deadline of the due payment in column E. So, let’s look at the steps for automatically sending email depending on the date when a condition is satisfied.
STEPS:
- First, go to the Developer tab from the ribbon.
- Second, click on Visual Basic to open the Visual Basic Editor.
- Another way to open the Visual Basic Editor is simply to press Alt + F11.
- Or, right-click on the sheet, then select View Code.
- And, this will open up the visual basic window.
- After that, copy and paste the VBA code below.
VBA Code:
Public Sub Send_Email_Automatically2()
Dim rngD, rngS, rngT As Range
Dim ob1, ob2 As Object
Dim LRow, x As Long
Dim l, strbody, rSendValue, mSub As String
On Error Resume Next
Set rngD = Application.InputBox("Deadline Range:", "Exceldemy", , , , , , 8)
If rngD Is Nothing Then Exit Sub
Set rngS = Application.InputBox("Email Range:", "Exceldemy", , , , , , 8)
If rngS Is Nothing Then Exit Sub
Set rngT = Application.InputBox("Email Topic Range:", "Exceldemy", , , , , , 8)
If rngT Is Nothing Then Exit Sub
LRow = rngD.Rows.Count
Set rngD = rngD(1)
Set rngS = rngS(1)
Set rngT = rngT(1)
Set ob1 = CreateObject("Outlook.Application")
For x = 1 To LRow
rngDValue = ""
rngDValue = rngD.Offset(x - 1).Value
If rngDValue <> "" Then
If CDate(rngDValue) - Date <= 7 And CDate(rngDValue) - Date > 0 Then
rngSValue = rngS.Offset(x - 1).Value
mSub = rngT.Offset(x - 1).Value & " on " & rngDValue
l = "<br><br>"
strbody = "<HTML><BODY>"
strbody = strbody & "Hello! " & rngSValue & l
strbody = strbody & rngT.Offset(x - 1).Value & l
strbody = strbody & "</BODY></HTML>"
Set ob2 = ob1.CreateItem(0)
With ob2
.Subject = mSub
.To = rSendValue
.HTMLBody = strbody
.Send
End With
Set ob2 = Nothing
End If
End If
Next
Set ob1 = Nothing
End Sub- Further, press the F5 key or click on the Run Sub button to run the code.
- Now, select the deadline column range and click OK.
- Similarly, select the email column range and hit OK to continue.
- Then, choose the message column range and click OK.
- And, that’s it. The messages are now sent to the email address. You can check your Outlook inbox to ensure that.
VBA Code Explanation
Public Sub Send_Email_Automatically2()
Dim rngD, rngS, rngT As Range
Dim ob1, ob2 As Object
Dim LRow, x As Long
Dim l, strbody, rSendValue, mSub As String
On Error Resume Next
Set rngD = Application.InputBox("Deadline Range:", "Exceldemy", , , , , , 8)
If rngD Is Nothing Then Exit Sub
Set rngS = Application.InputBox("Email Range:", "Exceldemy", , , , , , 8)
If rngS Is Nothing Then Exit Sub
Set rngT = Application.InputBox("Email Topic Range:", "Exceldemy", , , , , , 8)
If rngT Is Nothing Then Exit Sub
LRow = rngD.Rows.Count
Set rngD = rngD(1)
Set rngS = rngS(1)
Set rngT = rngT(1)
Set ob1 = CreateObject("Outlook.Application")Here, we again use a Private Sub, Send_Email_Automatically2 is the name of our Sub. Procedure. We declare the variable kinds. Then, we use InputBox to provide the value range. After that, we choose Outlook as our mail client.
For x = 1 To LRow
rngDValue = ""
rngDValue = rngD.Offset(x - 1).Value
If rngDValue <> "" Then
If CDate(rngDValue) - Date <= 7 And CDate(rngDValue) - Date > 0 Then
rngSValue = rngS.Offset(x - 1).Value
mSub = rngT.Offset(x - 1).Value & " on " & rngDValue
l = "<br><br>"
strbody = "<HTML><BODY>"
strbody = strbody & "Hello! " & rngSValue & l
strbody = strbody & rngT.Offset(x - 1).Value & l
strbody = strbody & "</BODY></HTML>"
Set ob2 = ob1.CreateItem(0)
With ob2
.Subject = mSub
.To = rSendValue
.HTMLBody = strbody
.SendThen we use the VBA CDate function to see if a date is within seven days of the current date. Then, in our code, set the email content. Finally, we use ‘.Send’ to send our email.
Read More: How to Automatically Send Email from Excel Based on Date
3. Automatically Send Email with Excel VBA When Multiple Conditions Met
In this method, again we are going to use a VBA Macro to send an email, but in this time when multiple conditions will be satisfied only then the messages will be sent to the customer. So, let’s look at the processes for sending emails automatically.
STEPS:
- To begin, click the Developer tab on the ribbon.
- Second, launch the Visual Basic Editor by clicking on Visual Basic.
- Alternatively, you may access the Visual Basic Editor by pressing Alt + F11.
- Or, right-click on the sheet and choose View Code from the menu.
- And the visual basic window will appear.
- Write the code there.
VBA Code:
Sub Send_Email_Automatically3()
Dim wrksht As Worksheet
Dim add As String, mSub As String, N As String
Dim eRow As Long, x As Long
Set wrksht = ThisWorkbook.Sheets("Multiple Conditions")
With wrksht
eRow = .Cells(.Rows.Count, 5).End(xlUp).Row
For x = 5 To eRow
If .Cells(x, 4) >= 1 And .Cells(x, 5) = "Yes" Then
add = .Cells(x, 3)
mSub = "Request to Pay Bill"
N = .Cells(x, 2)
Call Multiple_Conditions(add, mSub, N)
End If
Next x
End With
End Sub
Sub Multiple_Conditions(mAddress As String, mSubject As String, eName As String)
Dim ob1 As Object
Dim ob2 As Object
Set ob1 = CreateObject("Outlook.Application")
Set ob2 = ob1.CreateItem(0)
With ob2
.To = add
.CC = ""
.BCC = ""
.Subject = mSub
.Body = "Hello!" & N & ", To prevent further costs, please pay before the deadline."
.Attachments.add ActiveWorkbook.FullName
.Send
End With
Set pMail = Nothing
Set pApp = Nothing
End Sub- Finally, press the F5 key to run the code.
- A Macros dialog will appear after that. After that, choose the appropriate Macros and press the Run button.
- Likewise, in the previous methods, if you open Outlook and look in your mailbox, you will see the email that you just sent through VBA Macro from Excel.
VBA Code Explanation
Sub Send_Email_Automatically3()
Dim wrksht As Worksheet
Dim add As String, mSub As String, N As String
Dim eRow As Long, x As Long
Set wrksht = ThisWorkbook.Sheets("Multiple Conditions")
With wrksht
eRow = .Cells(.Rows.Count, 5).End(xlUp).Row
For x = 5 To eRow
If .Cells(x, 4) >= 1 And .Cells(x, 5) = "Yes" Then
add = .Cells(x, 3)
mSub = "Request to Pay Bill"
N = .Cells(x, 2)
Call Multiple_Conditions(add, mSub, N)Here, we use two different procedures. Send_Email_Automatically3 is the name of our first Sub Procedure. We set ‘Multiple Conditions’ as our sheet and declare the Variable kinds. Then, we discover the last row number. Furthermore, because our value begins on row 5, we have moved row 5 to the end of our code.
Sub Multiple_Conditions(mAddress As String, mSubject As String, eName As String)
Dim ob1 As Object
Dim ob2 As Object
Set ob1 = CreateObject("Outlook.Application")
Set ob2 = ob1.CreateItem(0)
With ob2
.To = add
.CC = ""
.BCC = ""
.Subject = mSub
.Body = "Hello!" & N & ", To prevent further costs, please pay before the deadline."
.Attachments.add ActiveWorkbook.FullName
.SendThen, we call Multiple_Conditions, our second Sub Procedure. We choose Outlook as our mail client. Then, in our code, set the email content. Using the Attachment technique, we are adding the Excel file to the email. Following that, we utilize our email to send with ‘.Send’.
Read More: Automatically Send Emails from Excel Based on Cell Content
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above methods will assist you to Send Email Automatically When the Condition is Met in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.
Related Articles
- How to Send an Editable Excel Spreadsheet by Email
- How to Send Bulk Email from Outlook Using Excel
- How to Send Automatic Email from Excel to Outlook
- How to Send Email If Conditions Met in Excel
- How to Send Multiple Emails from Excel Spreadsheet
<< Go Back To Send Email from Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
The email isnt being sent. ???
Hello, TREY!
Try to do it in a new worksheet and go to the Visual Basic Application using the Developer tab instead of the View Code option.
If it does not work!
Please mail me the dataset.
[email protected]