Rafiul Haq
Often, we need to send emails to our customers when a condition is fulfilled. This article will show you 3 methods to send an email if conditions are met in Excel. To demonstrate our methods, we’ve selected a dataset with 3 columns: “Name”, “Email”, and “Payment Due”.
Download Practice Workbook
3 Ways to Send Email If Conditions Met in Excel
1. Using VBA to Send Email If a Cell Value Changes in Excel
For the first method, we’ll apply an Excel VBA code to send an email when a condition is met. Firstly, we’re gonna bring up the VBA Module window, and then we’ll type our code and execute it to send emails. Moreover, in this case, our code execution criteria will be when a cell value changes.
Steps:
- Firstly, Right-Click on the “Cell Value Change” Sheet.
- Secondly, select View Code.
- Thirdly, type this code.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("D5"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 700 Then
Call Send_Email_Condition_Cell_Value_Change
End If
End If
End SubVBA Code Breakdown
Here, we’ll be using a Private Sub. Because we’ll not execute this code via the Macro window. This code will run itself when there is a cell value change.
- Firstly, we’re using Private Sub where the event is Worksheet_Change.
- Secondly, we’re limiting the number of cells to 1 and that cell is D5.
- Thirdly, we’re checking if it is more than 700.
- Finally, if the condition is met then the Sub Procedure Send_Email_Condition_Cell_Value_Change will execute.
- Finally, Save and close this window.
Now, we’ll type codes into the Module window. To bring up the VBA Module, follow these –
- Firstly, from the Developer tab >>> select Visual Basic.
Alternatively, you can press ALT + F11 to display the VBA window.
- Secondly, from Insert >>> select Module.
In this window, we’ll type our code.
- Type the following code.
Sub Send_Email_Condition_Cell_Value_Change()
Dim pApp As Object
Dim pMail As Object
Dim pBody As String
Set pApp = CreateObject("Outlook.Application")
Set pMail = pApp.CreateItem(0)
pBody = "Hello, " & Range("B5").Value & vbNewLine & _
"You've Payment Due." & vbNewLine & _
"Please Pay it to avoid extra fees."
On Error Resume Next
With pMail
.To = Range("C5").Value
.CC = ""
.BCC = ""
.Subject = "Request For Payment"
.Body = pBody
.Display 'We can use .Send to Send the Email
End With
On Error GoTo 0
Set pMail = Nothing
Set pApp = Nothing
End SubVBA Code Breakdown
- Firstly, we’re calling our Sub Procedure Send_Email_Condition_Cell_Value_Change.
- Secondly, we’re declaring the Variable types.
- Thirdly, we’re selecting Outlook as our Mail Application.
- Then, the email content is set in our code.
- After that, “.Display” is used here to display our email. Therefore, we’ll need to press Send manually to send the emails. Moreover, we can use “.Send” to send email without displaying.
- After that, Save and close the Module.
Now, in our dataset, we can type 699, and nothing will happen.
However, if we type 801 (more than 700), then our code will execute.
The Outlook email sending option will be shown. We can press Send to send the email address.
Read More: How to Send Email from Excel List (2 Effective Ways)
2. Use of VBA to Send Email If Multiple Conditions Met
For the second method, we’ve changed our dataset. We’ll send emails when multiple conditions are met in this method. Moreover, we’ll be using 2 Sub Procedures in a single Module for this. If our code works as intended, then we’ll send emails to 2 people. Moreover, we’ll attach the file to our email.
Steps:
- Firstly, as shown in the first method, bring up the Module window and type this code.
Option Explicit
Sub Send_Email_Condition()
Dim xSheet As Worksheet
Dim mAddress As String, mSubject As String, eName As String
Dim eRow As Long, x As Long
Set xSheet = ThisWorkbook.Sheets("Conditions")
With xSheet
eRow = .Cells(.Rows.Count, 5).End(xlUp).Row
For x = 5 To eRow
If .Cells(x, 4) >= 1 And .Cells(x, 5) = "Yes" Then
mAddress = .Cells(x, 3)
mSubject = "Request For Payment"
eName = .Cells(x, 2)
Call Send_Email_With_Multiple_Condition(mAddress, mSubject, eName)
End If
Next x
End With
End Sub
Sub Send_Email_With_Multiple_Condition(mAddress As String, mSubject As String, eName As String)
Dim pApp As Object
Dim pMail As Object
Set pApp = CreateObject("Outlook.Application")
Set pMail = pApp.CreateItem(0)
With pMail
.To = mAddress
.CC = ""
.BCC = ""
.Subject = mSubject
.Body = "Mr./Mrs. " & eName & ", Please pay it within the next week."
.Attachments.Add ActiveWorkbook.FullName 'Send The File via Email
.Display 'We can use .Send here too
End With
Set pMail = Nothing
Set pApp = Nothing
End SubVBA Code Breakdown
- Firstly, we’re calling our first Sub Procedure Send_Email_Condition.
- Secondly, we’re declaring the Variable types and setting “Conditions” as our Sheet.
- Thirdly, the last row number is found. Moreover, our value starts from row 5, hence we’ve put row 5 to the last row in our code.
- Then, call our second Sub Procedure Send_Email_With_Multiple_Condition.
- After that, we’re selecting Outlook as our Mail Application.
- Then, set the email content in our code.
- Here, we’re attaching the Excel file with the email using the Attachment method.
- After that, “.Display” is used here to display our email. Therefore, we’ll need to press Send manually to send the emails. Moreover, we can use “.Send” to send email without displaying.
- Secondly, Save and close the Module.
Now, we’ll bring the Macro window to execute our code.
- Firstly, from the Developer tab >>> select Macros.
The Macro window will pop up.
- Secondly, select “Send_Email_Condition”.
- Finally, press Run.
This will execute our code. Two people met our condition, hence we’ll see two emails window here.
Read More: How to Send Email Automatically When Condition Met in Excel
Similar Readings
- How to Enable Share Workbook in Excel
- How to Send Bulk Email from Outlook Using Excel (3 Ways)
- Macro to Send Email from Excel with Body (3 Useful Cases)
- How to Apply Macro to Send Email from Excel with Attachment
- How to Share Excel File Online (2 Easy Methods)
3. Send Email in Excel Based on Date Conditions
For the last method, we’ll send emails if the deadline is within one week period of the current date. Today is 19 May 2022 as of writing this article. Hence, there is only one row that falls within seven days. That is row 5. We’ll send an email to that person using the VBA code.
Steps:
- Firstly, as shown in the first method, bring up the Module window and type this code.
Public Sub Send_Email_Date_Condition()
Dim rDate, rSend, rText As Range
Dim pApp, pItem As Object
Dim LRow, x As Long
Dim lineBreak, pBody, rSendValue, mSubject As String
On Error Resume Next
Set rDate = Application.InputBox("Select Deadline Range:", "Exceldemy", , , , , , 8)
If rDate Is Nothing Then Exit Sub
Set rSend = Application.InputBox("Select Email Range:", "Exceldemy", , , , , , 8)
If rSend Is Nothing Then Exit Sub
Set rText = Application.InputBox("Select Email Topic Range:", "Exceldemy", , , , , , 8)
If rText Is Nothing Then Exit Sub
LRow = rDate.Rows.Count
Set rDate = rDate(1)
Set rSend = rSend(1)
Set rText = rText(1)
Set pApp = CreateObject("Outlook.Application")
For x = 1 To LRow
rDateValue = ""
rDateValue = rDate.Offset(x - 1).Value
If rDateValue <> "" Then
If CDate(rDateValue) - Date <= 7 And CDate(rDateValue) - Date > 0 Then
rSendValue = rSend.Offset(x - 1).Value
mSubject = rText.Offset(x - 1).Value & " on " & rDateValue
lineBreak = "<br><br>"
pBody = "<HTML><BODY>"
pBody = pBody & "Dear " & rSendValue & lineBreak
pBody = pBody & rText.Offset(x - 1).Value & lineBreak
pBody = pBody & "</BODY></HTML>"
Set pItem = pApp.CreateItem(0)
With pItem
.Subject = mSubject
.To = rSendValue
.HTMLBody = pBody
.Display 'We can also use .Send here
End With
Set pItem = Nothing
End If
End If
Next
Set pApp = Nothing
End SubVBA Code Breakdown
- Firstly, we’re calling our first Sub Procedure Send_Email_Date_Condition.
- Secondly, we’re declaring the Variable types and setting “Conditions” as our Sheet.
- Thirdly, we’re using InputBox to set the range of our values.
- After that, we’re selecting Outlook as our Mail Application.
- Then, we’re using the VBA CDate function to check if a date is within seven days from the current date.
- Then, set the email content in our code.
- After that, use “.Display” to show our email. Therefore, we’ll need to press Send manually to send the emails. Moreover, we can use “.Send” to send email without displaying.
- Secondly, Save and close the Module.
- Thirdly, as shown in method 2, bring up the Macro window.
- Then, select “Send_Email_Date_Condition” and press Run.
- Firstly, select the date column and press OK.
- Secondly. select the email column and press OK.
- Thirdly, select the email content column and press OK.
- Then we’ll see the email dialog box. We can press Send to achieve our goal.
Read More: How to Automatically Send Email from Excel Based on Date
Things to Remember
- In all our methods, Outlook was our default email Application. You may need to use different codes for separate Applications.
Practice Section
We’ve added practice datasets for each method in the Excel file.
Conclusion
We’ve shown you 3 methods to send an email if conditions met in Excel. Thanks for reading, keep excelling!
Related Articles
- How to Send Email from Excel with Body Using a Macro (with Easy Steps)
- How to Send Automatic Email from Excel to Outlook (4 Methods)
- Automatically Send Emails from Excel Based on Cell Content (2 Methods)
- How to Send Excel File to Email Automatically (3 Suitable Methods)
- Send Reminder Email Automatically from an Excel Worksheet Using VBA
- How to See Who Is in a Shared Excel File (With Quick Steps)
Hello!
I am following step – 1. Using VBA to Send Email If a Cell Value Changes in Excel.
But how can I create this without limiting it to only one cell – D5. How to edit range lets say from D5 to D10?
Thank you Edijs for your wonderful question. To edit the cell range follow these steps:
Firstly, enable the Microsoft Forms 2.0.
Secondly, type this code on to sheet 2.
Thirdly, type change the code from Module 1.
This should solve your problem. You can see the output from the following animated image.
If you have any further question, please let us know.
Regards
Bishawajit, on behalf of ExcelDemy
Hi! Thank you for this. However, the email cannot be sent automatically even when i use .Send. Any Idea?
Hi OZZY,
Thanks for your comment.
If the given codes are not working for the .Send function,
• Use the following code in your worksheet by clicking on View Code.
• Then, use the following code in your Module.
We hope this will solve your problem. Please let us know if you face any further problems.
Regards,
Arin Islam,
ExcelDemy
Hey guys, how would I also add a condition in the code from step 1 to only send once a day? For instance, in the code below, I know the target value will go below 72 more than once a day, but I do not want it sending 100 emails a day.
Thanks!
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range(“C15”), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value < 72 Then
Call Send_Email_Condition_Cell_Value_Change
End If
End If
End Sub
Hello, HUNTER!
Thanks for sharing your problem with us!
To add a condition in the code from step 1 to only send once a day, you can use a global variable to keep track of the last time an email was sent. Here’s an example of how you can modify the code:
In this modified code, the lastSentTime variable is used to keep track of the last time an email was sent. When a cell is changed and meets the criteria for sending an email, the code checks if at least one day has passed since the last email was sent before sending a new email. If less than a day has passed, the code skips sending the email. Once an email is sent, the lastSentTime variable is updated with the current time.
Hope this will help you.
Good Luck!
Regards,
Sabrina Ayon
Author, ExcelDemy.