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

Send Email If Conditions Met in Excel


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.

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

  • 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

VBA Code Breakdown

Here, we’ll be using a Private Sub, because we don’t execute this code via the Macro window. This code will run itself when there is a cell value change.

  • We define a Private Sub where the event is Worksheet_Change.
  • We limit the number of cells to 1, namely cell D5.
  • We check if its value is more than 700.
  • If the condition is met then the Sub Procedure Send_Email_Condition_Cell_Value_Change will execute.

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

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

  • 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

VBA Code Breakdown

  • We call our Sub Procedure Send_Email_Condition_Cell_Value_Change.
  • We declare the Variable types.
  • We select Outlook as our Mail Application.
  • The email content is set.
  • .Display” is used to display our email, meaning we’ll need to press Send manually to send the emails. Alternatively, we could use “.Send” here to send email automatically without displaying it first.

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

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

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

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.

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

  • We call our first Sub Procedure Send_Email_Condition.
  • We declare the Variable types and setting “Conditions” as our Sheet.
  • The last row number is found. Our values starts from row 5, so we assign row 5 as the last row in our code.
  • We call our second Sub Procedure Send_Email_With_Multiple_Condition.
  • We select Outlook as our Mail Application.
  • We set the email content in our code.
  • We attach the Excel file to the email using the Attachment method.
  • .Display” is used to display our email, meaning we’ll need to press Send manually to send the emails. We could use “.Send” to send email automatically without displaying.

  • Save and close the Module.

Bring up the Macro window to execute our code.

  • From the Developer tab, select Macros.

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

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.

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

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:

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

  • We call our first Sub Procedure Send_Email_Date_Condition.
  • We declare the Variable types and setting “Conditions” as our Sheet.
  • We use InputBox to set the range of our values.
  • We select Outlook as our Mail Application.
  • We use the VBA CDate function to check if a date is within seven days from the current date.
  • We set the email content in our code.
  • We use “.Display” to show our email, meaning we’ll need to press Send manually to send the emails. We could use “.Send” to send email automatically without displaying.

Send Email in Excel Based on Date Conditions

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

Send Email in Excel Based on Date Conditions

  • Select the email column and press OK.

  • Select the email content column and click OK.

Send Email in Excel Based on Date Conditions

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

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

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

12 Comments
  1. 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?

    • Reply Bishawajit Chakraborty
      Bishawajit Chakraborty Dec 12, 2022 at 2:37 PM

      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.

      Private Sub Worksheet_Change(ByVal Target As Range)
          If Target.Cells.Count > 1 Then Exit Sub
          If Not Application.Intersect(Range("D5:D10"), Target) Is Nothing Then
          Dim xDataObj As New MSForms.DataObject
          Dim rowNumber As Integer
          rowNumber = Split(Target.Address, "$")(2)
          xDataObj.SetText rowNumber
          xDataObj.PutInClipboard
              If IsNumeric(Target.Value) And Target.Value > 700 Then
                  Call Send_Email_Condition_Cell_Value_Change
              End If
          End If
      End Sub

      Thirdly, type change the code from Module 1.

      Sub Send_Email_Condition_Cell_Value_Change()
          Dim pApp As Object
          Dim pMail As Object
          Dim pBody As String
          Dim xDataObj As New MSForms.DataObject
          xDataObj.GetFromClipboard
          Set pApp = CreateObject("Outlook.Application")
          Set pMail = pApp.CreateItem(0)
          pBody = "Hello, " & Cells(xDataObj.GetText, 2).Value & vbNewLine & _
                    "You've Payment Due." & vbNewLine & _
                    "Please Pay it to avoid extra fees."
          On Error Resume Next
          With pMail
              .To = Cells(xDataObj.GetText, 3).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

      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

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

      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 > 700 Then
      Call Send_Mail_Automatically
      End If
      End Sub

      • Then, use the following code in your Module.

      Sub Send_Mail_Automatically()
      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
      End Sub

      We hope this will solve your problem. Please let us know if you face any further problems.

      Regards,
      Arin Islam,
      ExcelDemy

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

  4. 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:

    Private lastSentTime As Date
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        'Check if only one cell was changed
        If Target.Cells.Count > 1 Then Exit Sub
        
        'Check if the changed cell is C15
        If Not Application.Intersect(Range("C15"), Target) Is Nothing Then
            'Check if the new value is less than 72
            If IsNumeric(Target.Value) And Target.Value < 72 Then
                'Check if at least one day has passed since the last email was sent
                If Now - lastSentTime >= 1 Then
                    'Send the email
                    Call Send_Email_Condition_Cell_Value_Change
                    
                    'Update the last sent time
                    lastSentTime = Now
                End If
            End If
        End If
    End Sub

    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.

  5. Reply
    Jan Zákostelský Jan 26, 2024 at 7:26 PM

    Hello,
    I need to compare two rows. one stays the same, the other changes. I need to send an email when a number in any cell in the changing row falls below the value from the corresponding cell that stays the same. i.e A2 is fewer than B2 —> send email. How to do this for multiple cells ? thank you very much.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 7, 2024 at 11:32 AM

      Hello JAN ZÁKOSTELSKÝ

      Thanks for reaching out and sharing your problem. You want to compare two Excel rows: one that remains constant and another that changes. You need to receive an email whenever a number in any cell of the changing row becomes lower than the value in the corresponding cell of the constant row.

      I am delighted to inform you that I have developed an Excel VBA Sub-procedure and an Event Procedure. These will fulfil your requirements.

      Follow these steps:

      1. Right-click on the sheet name tab.

      2. Click on View Code.

      3. Insert the following code in the sheet module and Save.

      
      Private Sub Worksheet_Change(ByVal Target As Range)
          
          Dim staticRow As Range
          Dim changingRow As Range
          Dim cellStatic As Range
          Dim cellChanging As Range
          Dim emailSubject As String
          Dim emailBody As String
          Dim emailTo As String
          
          Set staticRow = Range("A2:D3")
          Set changingRow = Range("A3:D3")
          
          emailTo = "[email protected]"
          
          If Not Intersect(Target, changingRow) Is Nothing Then
      
              For Each cellChanging In Intersect(Target, changingRow)
                  Set cellStatic = staticRow.Cells(cellChanging.Column - changingRow.Column + 1)
      
                  If IsNumeric(cellStatic.Value) And IsNumeric(cellChanging.Value) Then
                      If cellChanging.Value < cellStatic.Value Then
                          emailSubject = "Value Below Threshold"
                          emailBody = "The value in cell " & cellChanging.Address & " is below the threshold (" & cellStatic.Value & ")."
                          SendEmail emailTo, emailSubject, emailBody
                          Exit Sub
                      End If
                  End If
              Next cellChanging
          End If
      End Sub
      
      Sub SendEmail(emailTo As String, subject As String, body As String)
      
          Dim outlookApp As Object
          Dim emailItem As Object
          
          Set outlookApp = CreateObject("Outlook.Application")
          Set emailItem = outlookApp.CreateItem(0)
          
          With emailItem
              .To = emailTo
              .subject = subject
              .body = body
              .Display
          End With
          
          Set emailItem = Nothing
          Set outlookApp = Nothing
      
      End Sub
      

      4. Return to the sheet and make changes to see an output like the following GIF.

      Hopefully, the idea will help you; good luck.

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

      • Reply
        Jan Zákostelský Feb 16, 2024 at 2:51 PM

        Hello, thank you very much for your answer it helped a lot. I have one more request. The information I am changing are in column H the static value is in column I and in column A there is an ID number. is it possible to write the ID number from column A when the value in column H falls below value of column I? thank you for your help

        kind regards
        zákostelský jan

        • Lutfor Rahman Shimanto
          Lutfor Rahman Shimanto Mar 19, 2024 at 2:57 PM

          Hello Zakostelsky Jan

          You can use the Worksheet_Change event in Excel VBA to make the code run automatically whenever column H changes.

          Here’s how you can modify the code:

          Private Sub Worksheet_Change(ByVal Target As Range)
          
              If Not Intersect(Target, Me.Columns("H")) Is Nothing Then
                  Call SendEmailOnValueChange
              End If
          
          End Sub
          
          Private Sub SendEmailOnValueChange()
          
              Dim ws As Worksheet
              Dim lastRow As Long
              Dim i As Long
              Dim OutlookApp As Object
              
              Set OutlookApp = CreateObject("Outlook.Application")
              
              Dim MItem As Object
              
              Set ws = ThisWorkbook.Sheets("Sheet1")
              
              lastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
              
              For i = 2 To lastRow
                  If ws.Cells(i, "H").Value < ws.Cells(i, "I").Value Then
                      
                      Set OutlookApp = CreateObject("Outlook.Application")
                      Set MItem = OutlookApp.CreateItem(0)
                      
                      With MItem
                          .To = "[email protected]" ' Change to your recipient email address
                          .Subject = "Value in column H has fallen below value in column I"
                          .body = "The value in row " & i & " of column H has fallen below the corresponding value in column I." & vbCrLf & _
                          "ID number from column A: " & ws.Cells(i, "A").Value
                          .Send
                      End With
                      
                      Set MItem = Nothing
                      Set OutlookApp = Nothing
                  
                  End If
              
              Next i
          
          End Sub

          I hope the idea will help you; good luck.

          Regards
          ExcelDemy

        • Jan Zákostelský Mar 15, 2024 at 12:53 PM

          Hello, thank you this works well, however I´d like to keep it as a private sub that executes automatically when there is a change. this code is very good for checking the whole sheet for past changes but needs to be run to send the email. Can it run itself whenever there is a change in the columm H ? thank you very much.

          Kindest regards
          Zakostelsky Jan

        • Lutfor Rahman Shimanto
          Lutfor Rahman Shimanto Feb 25, 2024 at 6:25 PM

          Hello JAN ZAKOS TELSKY,

          I hope you are doing well. Thank u so much for your query. Well, I can see you want to add the ID number (Column A)  in the mail body when the changing row falls below the value from the corresponding cell. You also added that the changing values are in column H, and the static value is in column I.

          Now, follow the below VBA code to write the ID number from column A in the email body when the value in column H falls below the value of column I.

          Code:

          Sub SendEmailOnValueChange()
          
          Dim ws As Worksheet
          Dim lastRow As Long
          Dim i As Long
          Dim OutlookApp As Object
          
          Set OutlookApp = CreateObject("Outlook.Application")
          
          Dim MItem As Object
          
          Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
          
          lastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
          
          For i = 2 To lastRow
          If ws.Cells(i, "H").Value < ws.Cells(i, "I").Value Then
          Set OutlookApp = CreateObject("Outlook.Application")
          Set MItem = OutlookApp.CreateItem(0)
          
          With MItem
          .To = "[email protected]" ' Change to your recipient email address
          .subject = "Value in column H has fallen below value in column I"
          .body = "The value in row " & i & " of column H has fallen below the corresponding value in column I." & vbCrLf & _
          "ID number from column A: " & ws.Cells(i, "A").Value
          .Send
          End With
          
          Set MItem = Nothing
          Set OutlookApp = Nothing
          
          End If
          
          Next i
          
          End Sub

          Once you apply the code, you will get an email as below.

          Hope this information will help you. Please let us know if there is any further query in the comment section.

          Best Regards,
          Afrina Nafisa 
          Exceldemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo