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

Send Email If Conditions Met in Excel


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.

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

  • 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

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

VBA: Using VBA to Send Email If a Cell Value Changes in Excel

  • 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

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

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

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

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

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.

Use of VBA to Send Email If Multiple Conditions Met

Steps:

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

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

Macro Window: Use of VBA to Send Email If Multiple Conditions Met

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.

Email Output: Use of VBA to Send Email If Multiple Conditions Met

Read More: How to Send Email Automatically When Condition Met in Excel


Similar Readings


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:

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

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

Send Email in Excel Based on Date Conditions

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

Send Email in Excel Based on Date Conditions

  • Secondly. select the email column and press OK.

  • Thirdly, select the email content column and press OK.

Send Email in Excel Based on Date Conditions

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

Practice Sheet: Send Email If Conditions Met Excel


Conclusion

We’ve shown you 3 methods to send an email if conditions met in Excel. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo