VBA Send Email When Cell Changes

Question

This works great but only if the cells are changed directly erasing the formula. Each of the cells in my range contains a VLOOKUP formula that fetches a value from a long list of data in a separate sheet. These values are imported from a database and are refreshed every so often. Basically I need an alternative that triggers the email when cells change through the formula. I hope that makes sense.

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Target, Range("N1:N999"))
    If xRg Is Nothing Then Exit Sub
    If (Range("N45") = Range("F45")) Then
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
          xRg.Offset(0, -12) & " has reached its target"

    On Error Resume Next
    With xOutMail
        .To = "***@****.com"
        .CC = ""
        .BCC = ""
        .Subject = "Target Reached"
        .Body = xMailBody
        .Send   'or use .Display
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Is this an option for you https://stackoverflow.com/questions/44127072/trigger-macro-when-any-cell-containing-formula-changes — Marcucciboy2, Aug 29 '18 at 11:55

score 1 · Answer 1 · answered Aug 29 '18 at 13:38

1

I hope it helps. I tried code as much as i understand the problem but in case of errors, you can modify it

Private Sub Worksheet_Calculate()

Static OldVal() As Variant
Dim cll As Range

ReDim OldVal(1 To Range("N1:N999").Cells.Count)

i = 1
For Each cll In Range("N1:N999")
    If cll.Value <> OldVal(i) Then
        OldVal(i) = cll.Value
        i = i + 1
    End If
Next cll

End Sub

answered Aug 29 '18 at 13:38

MehmetCanbulat

23
7

This looks great. I need to compare the changed cell in range N1:N999 with the correspoding row value for column F. So if Range("N1) = Range ("F1") then. How would I modify this? Cheers – Premier Hottubs Aug 29 '18 at 18:46
OldVal array holds the previous values and the cll range object holds the new value. You may embed your if statement inside for each loop construction. If you clarify your problem a little bit more, we can modify code. Right now i modified as much as i understand – MehmetCanbulat Sep 04 '18 at 05:48

score 0 · Answer 2 · answered Aug 29 '18 at 11:00

0

you could use Worksheet_Calculate() event. each time your formulas are calculate, the event will be triggered

answered Aug 29 '18 at 11:00

Wouter

383
1
12

Yes but I don't think you can use Set xRg = Intersect(Target, Range("N1:N999")) in the same way. And Target isn't an object for Worksheet_Calculate() – Premier Hottubs Aug 29 '18 at 11:06

score 0 · Answer 3 · answered Sep 04 '18 at 05:49

Private Sub Worksheet_Calculate()

Static OldVal() As Variant
Dim cll As Range

ReDim OldVal(1 To Range("N1:N999").Cells.Count)

i = 1
For Each cll In Range("N1:N999")
    If cll.Value <> OldVal(i) Then
        OldVal(i) = cll.Value
        i = i + 1
        'This part compares your new value to F column value
        'Change cll.value statement to oldval(i) to compare oldvalue and F column value
        If cll.Value = Cells(cll.Row, "F").Value Then
            'Your Code
        End If
    End If
Next cll

End Sub

VBA Send Email When Cell Changes

3 Answers3