Email Alert Excel Using VBA

[daddyfoxuk]

Hi all, I am using the below code with one issue... "J" will turn to sent when "I" has reached its value and the email does send but its sending all the previous emails as well. I only need it to send as each individual cell changes and not everything that's previously changed. Any help would be fantastic! Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
    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 = 200
    
    Set FormulaRange = Me.Range("I2:I100")


    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 = SentMsg Then
                        Call Mail_with_outlook2
                    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

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1074484-vba-not-running-automatically.html#post5164099

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).