Send Second Email When Value Reaches Another Threshold

[smythcounty]

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

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

 

[DacEasy]

Hi, I updated the code to run a separate If statement in the loop when the value is less than 200k. This will generate a 2nd email.

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 = 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
                 Range("C1").Value = MyMsg
            End If
             
            If .Value < 200000 Then
                    Call Mail_with_outlook1
            End If

            Application.EnableEvents = False
            FormulaCell.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

 

[smythcounty]

Hi, I updated the code to run a separate If statement in the loop when the value is less than 200k. This will generate a 2nd email.

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 = 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
                 Range("C1").Value = MyMsg
            End If
            
            If .Value < 200000 Then
                    Call Mail_with_outlook1
            End If

            Application.EnableEvents = False
            FormulaCell.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

It runs without error but doesn't run the macro when <200000. Trying to figure out how you got C1 in this line
MyMsg = NotSentMsg
Range("C1").Value = MyMsg

Thanks for helping me with this.

 

[DacEasy]

Oh I was testing and needed to see how your code was working. Remove that line, it's not needed. Sorry about that.

 

[smythcounty]

It is still not sending the second email when the value is less than <2000000. I think is has something to do with the MyMsg not being reset at some point before?

 

[DacEasy]

Paste your new code, I think you said 200,000 before, that looks like 2,000,000. MyMsg is ignored in the new if statement, it's only looking at the 200,000 value.

 

[smythcounty]

Sorry that was a typo, it is 200,000.

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("H7")

    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

 

[DacEasy]

Hmmm,

That doesn't look like what I posted, you are missing the:

            If .Value < 200000 Then
                    Call Mail_with_outlook1
            End If

The above is what tells it to email no matter what when less than 200,000

 

[smythcounty]

Hmmm,

That doesn't look like what I posted, you are missing the:

            If .Value < 200000 Then
                    Call Mail_with_outlook1
            End If

The above it what tells it to email no matter what when less than 200,000

I may have lost it in the copy / paste. Let me try it again with that.

 

[smythcounty]

I don't know what happened with that. It appears to be working now. I'll keep testing. Thanks again!