Send Second Email When Value Reaches Another Threshold

S

smythcounty

New Member
Joined
Jul 29, 2021
Messages
42
Office Version
  1. 365
Platform
  1. Windows
I'm wondering if it is possible to trigger a second email when the value of H6 is less than 200000? Like Call Mail_with_outlook2. Currently when the value of H6 is less than 400000 it runs the Macro.

Thanks

VBA Code: 
Private Sub Worksheet_Calculate()
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As Double
   

    NotSentMsg = "Not Sent"
    SentMsg = "Sent"
    MyLimit = 400000
    Set FormulaRange = Me.Range("H6")

    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            Else
                If .Value < MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook1
                    End If
                Else
               
                    MyMsg = NotSentMsg
                End If
           
            End If
            Application.EnableEvents = False
            .Offset(0, 1).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell

ExitMacro:
    Exit Sub

EndMacro:
    Application.EnableEvents = True

    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description

End Sub
 
Sort by date Sort by votes
Is there anyway to have it only email once after <200,000 is reached? Currently, each transaction once the value is under 200,000 sends another email.

Thanks again!
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
The code you are using, must have the bolded lines, or it will not know to email when it comes across <200,000

VBA Code: 
If .Value < MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook1
                    End If
                Else
               
                    MyMsg = NotSentMsg
                End If

[B]            If .Value < 200000 Then
                    Call Mail_with_outlook1
            End If[/B]
 
Upvote 0
Hmmm, bold doesn't work inbetween vba tags:

VBA Code: 
If .Value < MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook1
                    End If
                Else
               
                    MyMsg = NotSentMsg
                End If

       If .Value < 200000 Then
                    Call Mail_with_outlook1
            End If
 
Upvote 0
DacEasy said:
Hmmm, bold doesn't work inbetween vba tags:

VBA Code: 
If .Value < MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook1
                    End If
                Else
              
                    MyMsg = NotSentMsg
                End If

       If .Value < 200000 Then
                    Call Mail_with_outlook1
            End If
Click to expand...
I think what I need to do is say that mymsg = sent so it will stops sending the email over and over each time the amount decreases. It works when the value is below 200,000, but it keeps sending an email each time the value decreases. For instance, if the value is 199,999 it sends a email, when the value changes to 180,000 it sends another email. I just need it to send the email one time when the value is less than 200,000, like it does when the value is less than 400,000. Hope that makes sense.
 
Upvote 0
It sounds like you want it to send an email when it's below 400k, and a reminder at 200k. I would use like a sent2. So you can see who was warned twice. I will take another look at it.
 
Upvote 0
Ok this should do exactly what you described. It will only email twice, once when it goes below 400k and again when it goes below 200k.

VBA Code: 
Private Sub Worksheet_Calculate()
  Application.EnableEvents = False
    Dim FormulaRange As Range
    Dim NotSentMsg As String, MyMsg As String, SentMsg As String
    Dim MyLimit As Double
 
    NotSentMsg = "Not Sent": SentMsg = "Sent"
    MyLimit = 400000
    Set FormulaRange = Me.Range("H6")

    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
      
            'If H6 is not numeric
            If IsNumeric(.Value) = False Then  'I6 = Not numeric if not numeric
                MyMsg = "Not numeric"
                .Offset(0, 1).Value = MyMsg
            End If
          
            'If H6 greater than MyLimit, change to Not Sent
            If .Value >= MyLimit Then
                MyMsg = NotSentMsg
                .Offset(0, 1).Value = MyMsg
            End If
          
            'If H6 less than 200k and equal to Sent, make Sent2 and email
            'This must come before the other or it will email twice in a row
            If .Value < 200000 And Range("I6").Value = "Sent" Then
                MyMsg = "Sent2"
                .Offset(0, 1).Value = MyMsg
                Call Mail_with_outlook1
            End If
          
            'If H6 less than MyLimit and Not Sent or blank, change to Sent and email
            If .Value < MyLimit And Range("I6").Value = "Not Sent" Or Range("I6").Value = "" Then
                MyMsg = SentMsg
                .Offset(0, 1).Value = MyMsg
                Call Mail_with_outlook1
            End If
        End With
    Next FormulaCell

ExitMacro:
    Exit Sub

EndMacro:
  Application.EnableEvents = True

    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description

End Sub
 
Upvote 0
This is working If Imanually change the values in H6 below the limits and force the macro to run. Its not automatically triggering the emails. I've attempted to figure this out myself without any luck.
 
Upvote 0
Strange man, is this suppose to happen when you run the macro or is it suppose to run constantly and check on the limits?
 
Upvote 0
I am working on another project, I should be done by Thursday. Describe exactly what you want to happen and when, also post the code you are using. If no one jumps on it, I will dedicate Thursday and Friday to it.
 
Upvote 0
DacEasy said:
I am working on another project, I should be done by Thursday. Describe exactly what you want to happen and when, also post the code you are using. If no one jumps on it, I will dedicate Thursday and Friday to it.
Click to expand...
VBA Code: 
Option Explicit

Private Sub Worksheet_Calculate()
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As Double
    

    NotSentMsg = "Not Sent"
    SentMsg = "Sent"
    MyLimit = 400000
    Set FormulaRange = Me.Range("H6")

    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            
                ElseIf .Value < MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook1
                    End If
                Else
                    MyMsg = NotSentMsg
                End If
            
                If .Value < 200000 Then
                    Call Mail_with_outlook1
            End If
            Application.EnableEvents = False
            .Offset(0, 1).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell

ExitMacro:
    Exit Sub

EndMacro:
    Application.EnableEvents = True

    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description

End Sub

H6 contains a formula that provides the output number which starts out at 1,000,000. When this amount reaches less than 400,000 then the first email should trigger automatically. This should occur only once until the second email is triggered when the amount reaches less than 200,000. Which again, should occur only once even though the number in H6 will continue to decrease. The above code works as it should until the amount reaches less than 200,000 in H6, then each time the amount keeps decreasing another email is triggered, instead of just the initial email once the H6 amount falls below 200,000.

VBA Code: 
Private Sub Worksheet_Calculate()
  Application.EnableEvents = False
    Dim FormulaRange As Range
    Dim NotSentMsg As String, MyMsg As String, SentMsg As String
    Dim MyLimit As Double
 
    NotSentMsg = "Not Sent": SentMsg = "Sent"
    MyLimit = 400000
    Set FormulaRange = Me.Range("H6")

    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
      
            'If H6 is not numeric
            If IsNumeric(.Value) = False Then  'I6 = Not numeric if not numeric
                MyMsg = "Not numeric"
                .Offset(0, 1).Value = MyMsg
            End If
          
            'If H6 greater than MyLimit, change to Not Sent
            If .Value >= MyLimit Then
                MyMsg = NotSentMsg
                .Offset(0, 1).Value = MyMsg
            End If
          
            'If H6 less than 200k and equal to Sent, make Sent2 and email
            'This must come before the other or it will email twice in a row
            If .Value < 200000 And Range("I6").Value = "Sent" Then
                MyMsg = "Sent2"
                .Offset(0, 1).Value = MyMsg
                Call Mail_with_outlook1
            End If
          
            'If H6 less than MyLimit and Not Sent or blank, change to Sent and email
            If .Value < MyLimit And Range("I6").Value = "Not Sent" Or Range("I6").Value = "" Then
                MyMsg = SentMsg
                .Offset(0, 1).Value = MyMsg
                Call Mail_with_outlook1
            End If
        End With
    Next FormulaCell

ExitMacro:
    Exit Sub

EndMacro:
  Application.EnableEvents = True

    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description

End Sub

This code above works when H6 is less than 400,000 and 200,000 but I have to manually run the macro each time for it to trigger the email.

Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
How to combine similar Private Sub Worksheets?
Replies
3
Views
421
Rita9091
R
S
  • Solved
Insert Cell Value Into Email
Replies
2
Views
809
smythcounty
S
H
  • Question Question
Send auto email based on two factors, date falls within 90days & cell value B6
2 3 4
Replies
32
Views
2K
Candyman8019
C
B
  • Question Question
VBA Code to Send Mail to skip blank cells
Replies
1
Views
269
Micron
M
crook_101
  • Question Question
VBA to loop when sending email
Replies
1
Views
203
crook_101
crook_101

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top