Auto notify or mail when Excel sheet updated.

Solved/Closed
Satya - Updated on Jan 30, 2019 at 06:28 AM
 cdb2013 - Feb 1, 2013 at 06:42 AM
Hello,

We have a Excel which is saved to a network drive and used by multiple users. What I need is when somebody updates the excel sheet or puts any value or comments on the sheet, it will notify me or auto mail me that the excel sheet is updated. I mean instead of going again and agian to check whether somebody changed the sheet or not it will notify or auto mail to me.

Any kind of help appreciated. Whether I need any software or any VB, Macro can solve the purpose.

Thank you,
Satya
System Configuration: Windows XP Internet Explorer 6.0

4 responses

Hi, I just had this problem and poked around until I found a solution, so here I am passing it on to you. This will automatically send an email to any address/addresses whenever the workbook is saved. The only problem I have with it is that if you save it, you get the prompt, and when you close, excel automatically prompts you again. As long as you don't do a redundant save then you won't get a redundant auto email notification :)





Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Dim answer As String

answer = MsgBox("This is where you put the text to prompt the user if he wants to save or not" vbYesNo, "here is the title of that box")

If answer = vbNo Then Cancel = True
If answer = vbYes Then
'open outlook type stuff
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
'newmsg.Recipients.Add ("Name Here")
newmsg.Recipients.Add ("sampleemailaddresshere@email.com")
'add subject
newmsg.Subject = "Subject line of auto email here"
'add body
newmsg.Body = "body of auto email here"
newmsg.Display 'display
newmsg.Send 'send message
'give conformation of sent message
MsgBox "insert confirmation box test here", , "title of confirmation box"



End If


'save the document
'Me.Worksheets.Save

End Sub
33
Hi Shark,

Hey its working fine, I have downloaded the Advanced Security Option from MAPI lab so it is not asking anything and able to send mail automatically.

Thanks a lot
0
Archana > satya
Sep 29, 2009 at 12:58 AM
Hi,
I am trying to create an excel sheet which has some data like meet HR on the 29th sept and so I need to recieve a mail reminder saying please meet HR. I am using Outlook express . Actual work of the excel sheet is to send email reminders about pending RFQ.
So please can u help me with the same.
0