How to Send Email Automatically When Condition Met in Excel

Automatically sending email allows us to design messages that are one-size-fits-all and deliver them to users. By generating and sending automatic emails, we may save time. Since it enables you to send emails at particular times, email automation is an excellent approach to engaging with potential consumers. The capacity to send email notifications to the right people at the right moment is the most valuable feature of email automation. In this article, we will demonstrate different VBA Macros of excel that automatically send an email when the condition is met.

Download Practice Workbook

You can download the workbook and practice with them.

3 Methods to Send Email Automatically When Condition Met in Excel

Whenever a condition is met, we frequently need to send emails to our customers. Using VBA macros, we can customize our mailing functionality. As a result, we may send an email to numerous people at the same time using the VBA technique. To send an email automatically with a macro, we must have Outlook preinstalled on our computer. So the code we will put will send emails to the recipients using Outlook.

1. Excel VBA Macro to Send Email Automatically Based on a Cell Value

We will use an excel VBA macro to automatically send an email based on a certain column value in our dataset. The dataset below will be used to demonstrate this example. If the cell value in cell D5 is larger than 10, we’ll develop a function that sends an email automatically.

Suppose, we have the following dataset of some customer details of a super shop. The dataset contains some customers’ names in column B, their email addresses in column C, and some dues for their purchasing products in column D. Now, we want to send emails to them to request for paying the due bills, but here we have a condition which we follow: if the customer’s bill is greater than 10 only then we will send them emails. So, let’s see the procedures to send email automatically based on a cell value when the condition is met.

STEPS:

• Firstly, go to the Developer tab from the ribbon.
• Secondly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.

• Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.

• This will appear in the Visual Basic Editor where we write our codes to create a table from range.
• And, copy and paste the VBA code shown below.

VBA Code:

Dim r As Range
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 > 10 Then
Call Send_Mail_Automatically1
End If
End Sub
Sub Send_Mail_Automatically1()
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
On Error GoTo 0
Set ob2 = Nothing
Set ob1 = Nothing
End Sub

• After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

• Then, a Macros dialog will show up. Click on the right Macros then, hit the Run button.

• Now, if you go to your Outlook application and check the inbox, we will find the mail that you just sent via VBA Macro from Excel.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Su
Set r = Intersect(Range("D5"), Target)
If r Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 10 Then
Call Send_Mail_Automatically1
End If
End Sub

Sub Send_Mail_Automatically1()
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

Read More: Automatically Send Emails from Excel Based on Cell Content (2 Methods)

2. Send Email Automatically Based on a Due Date Using VBA Code

In this method, we will utilize an Excel VBA macro to automatically send an email as the bill’s due date approaches. This serves as a kind of reminder. To demonstrate this, we will use the dataset below. And the dataset contains some customer names in column B, their email address in column C, the message we want to send in column D, and the deadline of the due payment in column E. So, let’s look at the steps for automatically sending email depending on the date when a condition is satisfied.

STEPS:

• First, go to the Developer tab from the ribbon.
• Second, click on Visual Basic to open the Visual Basic Editor.
• Another way to open the Visual Basic Editor is simply to press Alt + F11.
• Or, right-click on the sheet, then select View Code.
• And, this will open up the visual basic window.
• After that, copy and paste the VBA code below.

VBA Code:

Public Sub Send_Email_Automatically2()
    Dim rngD, rngS, rngT As Range
    Dim ob1, ob2 As Object
    Dim LRow, x As Long
    Dim l, strbody, rSendValue, mSub As String
    On Error Resume Next
    Set rngD = Application.InputBox("Deadline Range:", "Exceldemy", , , , , , 8)
    If rngD Is Nothing Then Exit Sub
    Set rngS = Application.InputBox("Email Range:", "Exceldemy", , , , , , 8)
    If rngS Is Nothing Then Exit Sub
    Set rngT = Application.InputBox("Email Topic Range:", "Exceldemy", , , , , , 8)
    If rngT Is Nothing Then Exit Sub
    LRow = rngD.Rows.Count
    Set rngD = rngD(1)
    Set rngS = rngS(1)
    Set rngT = rngT(1)
    Set ob1 = CreateObject("Outlook.Application")
    For x = 1 To LRow
        rngDValue = ""
        rngDValue = rngD.Offset(x - 1).Value
        If rngDValue <> "" Then
        If CDate(rngDValue) - Date <= 7 And CDate(rngDValue) - Date > 0 Then
            rngSValue = rngS.Offset(x - 1).Value
            mSub = rngT.Offset(x - 1).Value & " on " & rngDValue
            l = "<br><br>"
            strbody = "<HTML><BODY>"
            strbody = strbody & "Hello! " & rngSValue & l
            strbody = strbody & rngT.Offset(x - 1).Value & l
            strbody = strbody & "</BODY></HTML>"
            Set ob2 = ob1.CreateItem(0)
            With ob2
                .Subject = mSub
                .To = rSendValue
                .HTMLBody = strbody
                .Send
            End With
            Set ob2 = Nothing
        End If
    End If
    Next
    Set ob1 = Nothing
End Sub

• Further, press the F5 key or click on the Run Sub button to run the code.

• Now, select the deadline column range and click OK.

• Similarly, select the email column range and hit OK to continue.

• Then, choose the message column range and click OK.

• And, that’s it. The messages are now sent to the email address. You can check your Outlook inbox to ensure that.

Public Sub Send_Email_Automatically2()
    Dim rngD, rngS, rngT As Range
    Dim ob1, ob2 As Object
    Dim LRow, x As Long
    Dim l, strbody, rSendValue, mSub As String
    On Error Resume Next
    Set rngD = Application.InputBox("Deadline Range:", "Exceldemy", , , , , , 8)
    If rngD Is Nothing Then Exit Sub
    Set rngS = Application.InputBox("Email Range:", "Exceldemy", , , , , , 8)
    If rngS Is Nothing Then Exit Sub
    Set rngT = Application.InputBox("Email Topic Range:", "Exceldemy", , , , , , 8)
    If rngT Is Nothing Then Exit Sub
    LRow = rngD.Rows.Count
    Set rngD = rngD(1)
    Set rngS = rngS(1)
    Set rngT = rngT(1)
    Set ob1 = CreateObject("Outlook.Application")

 For x = 1 To LRow
        rngDValue = ""
        rngDValue = rngD.Offset(x - 1).Value
        If rngDValue <> "" Then
        If CDate(rngDValue) - Date <= 7 And CDate(rngDValue) - Date > 0 Then
            rngSValue = rngS.Offset(x - 1).Value
            mSub = rngT.Offset(x - 1).Value & " on " & rngDValue
            l = "<br><br>"
            strbody = "<HTML><BODY>"
            strbody = strbody & "Hello! " & rngSValue & l
            strbody = strbody & rngT.Offset(x - 1).Value & l
            strbody = strbody & "</BODY></HTML>"
            Set ob2 = ob1.CreateItem(0)
            With ob2
                .Subject = mSub
                .To = rSendValue
                .HTMLBody = strbody
                .Send

Read More: How to Automatically Send Email from Excel Based on Date

Similar Readings

• How to See Who Is in a Shared Excel File (With Quick Steps)
• How to Enable Share Workbook in Excel
• Send Reminder Email Automatically from an Excel Worksheet Using VBA
• How to Send Bulk Email from Outlook Using Excel (3 Ways)
• How to Apply Macro to Send Email from Excel with Attachment

3. Automatically Send Email with Excel VBA When Multiple Conditions Met

In this method, again we are going to use a VBA Macro to send an email, but in this time when multiple conditions will be satisfied only then the messages will be sent to the customer. So, let’s look at the processes for sending emails automatically.

STEPS:

• To begin, click the Developer tab on the ribbon.
• Second, launch the Visual Basic Editor by clicking on Visual Basic.
• Alternatively, you may access the Visual Basic Editor by pressing Alt + F11.
• Or, right-click on the sheet and choose View Code from the menu.
• And the visual basic window will appear.
• Write the code there.

VBA Code:

Sub Send_Email_Automatically3()
    Dim wrksht As Worksheet
    Dim add As String, mSub As String, N As String
    Dim eRow As Long, x As Long
    Set wrksht = ThisWorkbook.Sheets("Multiple Conditions")
    With wrksht
        eRow = .Cells(.Rows.Count, 5).End(xlUp).Row
        For x = 5 To eRow
            If .Cells(x, 4) >= 1 And .Cells(x, 5) = "Yes" Then
                add = .Cells(x, 3)
                mSub = "Request to Pay Bill"
                N = .Cells(x, 2)
                Call Multiple_Conditions(add, mSub, N)
            End If
        Next x
    End With
End Sub
Sub Multiple_Conditions(mAddress As String, mSubject As String, eName As String)
    Dim ob1 As Object
    Dim ob2 As Object
    Set ob1 = CreateObject("Outlook.Application")
    Set ob2 = ob1.CreateItem(0)
    With ob2
        .To = add
        .CC = ""
        .BCC = ""
        .Subject = mSub
        .Body = "Hello!" & N & ", To prevent further costs, please pay before the deadline."
        .Attachments.add ActiveWorkbook.FullName
        .Send
    End With
    Set pMail = Nothing
    Set pApp = Nothing
End Sub

• Finally, press the F5 key to run the code.

• A Macros dialog will appear after that. After that, choose the appropriate Macros and press the Run button.

• Likewise, in the previous methods, if you open Outlook and look in your mailbox, you will see the email that you just sent through VBA Macro from Excel.

Sub Send_Email_Automatically3()
    Dim wrksht As Worksheet
    Dim add As String, mSub As String, N As String
    Dim eRow As Long, x As Long
    Set wrksht = ThisWorkbook.Sheets("Multiple Conditions")
    With wrksht
        eRow = .Cells(.Rows.Count, 5).End(xlUp).Row
        For x = 5 To eRow
            If .Cells(x, 4) >= 1 And .Cells(x, 5) = "Yes" Then
                add = .Cells(x, 3)
                mSub = "Request to Pay Bill"
                N = .Cells(x, 2)
                Call Multiple_Conditions(add, mSub, N)

Sub Multiple_Conditions(mAddress As String, mSubject As String, eName As String)
    Dim ob1 As Object
    Dim ob2 As Object
    Set ob1 = CreateObject("Outlook.Application")
    Set ob2 = ob1.CreateItem(0)
    With ob2
        .To = add
        .CC = ""
        .BCC = ""
        .Subject = mSub
        .Body = "Hello!" & N & ", To prevent further costs, please pay before the deadline."
        .Attachments.add ActiveWorkbook.FullName
        .Send

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

Conclusion

The above methods will assist you to Send Email Automatically When the Condition is Met in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!

Related Articles

• How to Send Automatic Email from Excel to Outlook (4 Methods)
• How to Send Email from Excel with Body Using a Macro (with Easy Steps)
• Excel Macro: Send Email to an Address in Cell (2 Easy Ways)
• How to Send Multiple Emails from Excel Spreadsheet (2 Easy Methods)
• Macro to Send Email from Excel with Body (3 Useful Cases)
• How to Send an Editable Excel Spreadsheet by Email (3 Quick Methods)