Email When Shared Spreadsheet is Updated

[jdu007]

Hi Folks.

OK, this may or may not be a simple one!

I have a spreadsheet on a shared drive, that a number of people have access to. One of the sheets has a range of dates, and I use it to allow people to 'book in' to give presentations on that particular date. I find myself checking it daily, to see if and when someone has made a booking. More often than not, they haven't.

I'd like a piece of code, if possible, that would email me a simple message if the spreadsheet gets updated. I'm not fussed about knowing what the change is (which I guess would be quite difficult anyway) - I simply want to know if it's changed, so I don't have to keep checking unnecessarily.

Any help greatly appreciated.

Thanks in advance,

John.

 

[SQUIDD]

Hi John

How about something like this to get you started.
This does not email you when the workbook changes.
It emails you when the workbook gets closed, so assuming people only open it to change it, make their changes, save it, then close it, each time to workbook is closed it will let you know.

the code needs to go into the THISWORKBOOK module, so not an standard module.
change the email address to suit obviously

oh, if it does not suit on workbook close, im sure there are other options to explore.

let me know if you need any more help

dave

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = "john@blar.com"
        .Subject = "workbook change notification"
        .Body = "Hi John" & vbNewLine & vbNewLine & "Someone just changed your workbook"
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub