Being notified when a file is changed / updated.

[SRMPURCHASE]

What VBA would be needed to insert into a macro to notify an email recipient that a file has been modified?

 

[Yongle]

This is only part of the solution

- FileDateTime function returns latest update time for monitored file
-Application.OnTime Now + TimeValue("01:00"), "MonitorFile" triggers macro again in 1 hour (repeatedly)
- whenever workbook containing this VBA is open, latest updated time is verified and compared
- additional procedure to SendEmail required to do the email sending

In ThisWorkbook module

Private Sub Workbook_Open()
    Call MonitorFile
End Sub

In Standard Module

Sub MonitorFile()
    Dim x As Double:    x = FileDateTime("C:\TestArea\Analysis.xlsx")
    If x > Range("A1") Then
        Call [I][B]SendEmail[/B] '[COLOR=#006400]add this procedure yourself[/COLOR][/I]
        Range("A1") = x
    End If
    Application.OnTime Now + TimeValue("01:00"), "MonitorFile"
End Sub

 

[SRMPURCHASE]

I take it the 'add this procedure yourself note in green type is where the email address goes?

 

[Yongle]

I take it the 'add this procedure yourself noted in green type is where the email address goes?

No
- as explained, the above code is "part" of the solution
- you can test to see if it works by inserting a message box (instead of Call SendEmail ) to see if it is triggered correctly

You need to add a further procedure to generate the email itself
- perhaps you could search the forum for code to send an email
- after testing that procedure, rename it SendEmail and run it with Call SendEmail

 

[SRMPURCHASE]

Got it, thanks.

 

[SRMPURCHASE]

Perhaps someone else knows that answer about inserting the email address script?