How to Send Email If Conditions Met in Excel (3 Easy Methods)

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

How to Send Email If Conditions Met in Excel: 3 Easy Methods

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 Sub

• 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 Sub

• 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 Excel File to Email Automatically

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 Sub

• 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: Automatically Send Emails from Excel Based on Cell Content

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 Sub

• 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 Send Email Automatically When Condition Met in Excel

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.

Download Practice Workbook

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 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 Automatically Send Email from Excel Based on Date
• How to Send Multiple Emails from Excel Spreadsheet

<< Go Back To Send Email from Excel | Learn Excel