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

This article provides 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”.

Method 1 – Using VBA to Send Email If a Cell Value Changes in Excel

Steps:

• Right-Click on the “Cell Value Change” Sheet.
• Select View Code.

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

• Save and close this window.

Now we enter code into the Module window. To bring up the VBA Module:

• From the Developer tab, select Visual Basic.
• Alternatively, press ALT + F11.

• From the Insert tab, select Module.

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

• Save and close the Module.

Now, in our dataset, we can type 699 and nothing will happen.

However if we type 801 (or any value greater than 700), then our code will execute.

The Outlook email sending option will be shown. Press Send to send the email address.

Read More: How to Send Excel File to Email Automatically

Method 2. Using VBA to Send Email If Multiple Conditions are Met

For the second method, we’ve changed our dataset. Moreover, we’ll be using 2 Sub Procedures in a single Module. If our code works as intended, we’ll send emails to 2 people, and attach the file to our email.

Steps:

• As shown in the first method, bring up the Module window and enter 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

• Save and close the Module.

Bring up the Macro window to execute our code.

• From the Developer tab, select Macros.

The Macro window will pop up.

• Select “Send_Email_Condition”.
• Click Run.

This will execute our code. Two people met our condition, hence we’ll see two email windows.

Read More: Automatically Send Emails from Excel Based on Cell Content

Method 3 – Send Email Based on Date Conditions

For the last method, we’ll send emails if the deadline is within one week of the current date. Assuming 19 May 2022 is the current date, there is only one row that falls within seven days of it, row 5. We’ll send an email to that person using the VBA code.

Steps:

• As shown in the first method, bring up the Module window and enter 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

• Save and close the Module.
• As shown in method 2, bring up the Macro window.
• Select “Send_Email_Date_Condition” and press Run.

The macro dialog box opens.

• Select the date column and click OK.

• Select the email column and press OK.

• Select the email content column and click OK.

The email dialog box opens.

• 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 will need to use different codes for separate Applications.

Download Practice Workbook

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