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]

Ok, killer, now I know what you want.

 

[DacEasy]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = True
    If Target.Address = "$H$6" Then
    
    
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim SentMsg1 As String
    Dim SentMsg2 As String
    Dim MyMsg As String
    Dim MyLimit1 As Double
    Dim MyLimit2 As Double
    Dim FormulaCell As Range
 
    NotSentMsg = "Not Sent":
    SentMsg1 = "Sent1"
    SentMsg2 = "Sent2"
    MyLimit1 = 400000
    MyLimit2 = 200000
    
    Set FormulaRange = 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 >= MyLimit1 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 < MyLimit2 And Range("I6").Value = "Sent1" Then
                MyMsg = SentMsg2
                .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 < MyLimit1 And Range("I6").Value = "Not Sent" Or Range("I6").Value = "" Then
                MyMsg = SentMsg1
                .Offset(0, 1).Value = MyMsg
                Call Mail_with_outlook1
             
            End If
        End With
    Next FormulaCell
    
ExitMacro:
    Exit Sub
    
EndMacro:
        
    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description

    End If
End Sub

Put the above code in the sheet you are using, the picture below shows where to add. It will keep watching H6, whenever it changes below 400k or 200k and email will be sent but only a max of 2, unless you go back over the limit then it resets.

 

[smythcounty]

Thanks for all your work on this. It works but I have to manually enter the amnounts in H6 for it to send the email. By manually, I mean type the amount in H6. I have formula in H6 that calcuates the amount for that cell. For whatever reason when the amount goes below 400,000 from the formula it doesn't activate the email.

 

[DacEasy]

You have a formula in H6? What is the formula, where does it pull from?

 

[smythcounty]

You have a formula in H6? What is the formula, where does it pull from?

its just '=F6-G6' It taking amount ordered (F6) minus the total amount shipped (G6). G6 is also a formula that computes automatically after each shipment is sent.

 

[DacEasy]

How did you automate G6, you should be able to do the same to H6.

 

[smythcounty]

How did you automate G6, you should be able to do the same to H6.

It is by a formula. G6 is =SUMIF('Sheet1'!$G$51:$G$100,"TRUE",'Sheet1'!$L$1:$L$100). Sheet1 is a different sheet in the workbook than where this VBA code is pointing. This computes each time a check box is ticked beside a row. When the check box is ticked it places a "TRUE" in the G column for that row, if its "TRUE" then it sums the corresponding row in L Column.