VBA to send email to recipient list if a cell/cells change, trigger on file save?

[Guitarmageddon]

Hello folks, just like the subject states. Trying to figure out a way if I can have VBA that monitors a particular cell or cells for changes, and that check is executed upon file saving. If there is a change, it would email a preset list of contacts in outlook. Perhaps that email could even have a link to the file? Or maybe, just the file extension itself is ok. Im flexible on that. Does this sound doable? The file was kept on share point but I think the range of options for triggering emails when conditions are met isnt as granular as "look in the file at a particular cell" but rather looking at modified time stamps or something.

 

[rakesh seebaruth]

Sub check_cell_for_changes()
    
    Dim ws          As Worksheet
    Dim cell_range  As Range
    Dim old_value   As Variant
    
    Set ws = ActiveSheet
    Set cell_range = Range("A1")
    old_value = cell_range.Value
    
    Application.EnableEvents = TRUE
    
    Do While TRUE
        If cell_range.Value <> old_value Then
            Call send_email
        End If
        
        DoEvents
        old_value = cell_range.Value
        Application.Wait (Now + TimeValue("0:00:05"))
        
    Loop
    
End Sub

Private Sub send_email()
    Dim olApp       As Outlook.Application
    Dim myMail      As Outlook.MailItem
    Set olApp = CreateObject("Outlook.Application")
    Set myMail = olApp.CreateItem(olMailItem)
    
    myMail.Subject = "Cell A1 Changed"
    myMail.Body = "The value of cell A1 has changed."
    myMail.To = "recipient@example.com"
    myMail.Send
    
End Sub

 

[Guitarmageddon]

Sub check_cell_for_changes()
   
    Dim ws          As Worksheet
    Dim cell_range  As Range
    Dim old_value   As Variant
   
    Set ws = ActiveSheet
    Set cell_range = Range("A1")
    old_value = cell_range.Value
   
    Application.EnableEvents = TRUE
   
    Do While TRUE
        If cell_range.Value <> old_value Then
            Call send_email
        End If
       
        DoEvents
        old_value = cell_range.Value
        Application.Wait (Now + TimeValue("0:00:05"))
       
    Loop
   
End Sub

Private Sub send_email()
    Dim olApp       As Outlook.Application
    Dim myMail      As Outlook.MailItem
    Set olApp = CreateObject("Outlook.Application")
    Set myMail = olApp.CreateItem(olMailItem)
   
    myMail.Subject = "Cell A1 Changed"
    myMail.Body = "The value of cell A1 has changed."
    myMail.To = "recipient@example.com"
    myMail.Send
   
End Sub

Hello,
This just spun for a while and I ended up having to kill excel. Not sure if there is any other prep with the VBA settings I needed to do?

Another thing Im curious about is how this activates. Can it be activated upon file save? Or, does it always require a manual intervention of someone to run the macro?

 

[Guitarmageddon]

I have found this code works on running (currently with just the criteria dummied in of "does xyz cell not equal some number"

How to automatically send email based on cell value in Excel?

www.extendoffice.com

However, Im looking for two additonal features.
1)some way to run this without the user executing the macro (i would like to do it on file save)
2)can the email be told to indicate what cell has changed?

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(Range("D7"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value > 200 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 & _
              "This is line 1" & vbNewLine & _
              "This is line 2"
    On Error Resume Next
    With xOutMail
        .To = "Email Address"
        .CC = ""
        .BCC = ""
        .Subject = "send by cell value test"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

 

[Guitarmageddon]

Coming back to this to bump. I think the key may actual be a different mechanism to find the changes. how about the file having two sheets: "Sheet1" and a second "hidden reference" for example. Then, the code can execute upon file save and check to compare the hidden sheet. If it spots differences, it notes those, puts that data in an email i.e. "A5 changed to 'completed status' " or something to that effect. Once the email is created, it then copies the data into the hidden reference sheet, and then the file can be closed. Thoughts?