Auto notify or mail when excel sheet updated. [Solved/Closed]

Satya - Jun 12, 2009 at 08:24 AM - Latest reply:  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
See more 

20 replies

Best answer
shark2000br - Jun 30, 2009 at 11:42 AM
30
Thank you
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

Thank you, shark2000br 30

Something to say? Add comment

CCM has helped 1910 users this month

Should this code work as is? I am a first time VBA user and tried to use this code but did not receive a prompt answer message or a confirmation email
Thanx a lot!
This works like a charm in Office 2010 as well.
readyt0race 1 Posts Friday October 26, 2012Registration date October 26, 2012 Last seen - Oct 26, 2012 at 12:57 AM
Hi Shark,

I'm glad I stumbled upon this thread after long attempts at trying to close in on my solution. Sorry to dig up this old thread, but can you please tell this n00b in more detail how to enable notification mails when an excel file is updated?

Highly appreciated, Cheers!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Dec 1, 2012 at 10:53 AM
@readyt0race
This is an event "Workbook_BeforeSave" that is defined in "ThisWorkbook" module and is triggered when workbook is saved.. So if you open your excel file, then press ALT + F11 at the same time, you would be opening VB editor. If you press CTRL + R at same time, you would see the project explorer. There you should see that says "ThisWorkbook". You need to use the code there
If you changed the event to open would you use "Workbook_Open" instead?
6
Thank you
Run time error '287'
Application -defined or object defined error
2
Thank you
I am getting above error from some of the systems on my network
May on that system Outlook is not configured
Can you please suggest necessary code changes for other web mail applications
As most the systems having Thunderbird
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jan 4, 2013 at 01:53 PM
0
Thank you
Thanks Sir,
I tried this piece of code it worked on Thunderbird 17.0 version in my network.
The code actually open Compose window
But does not sends the mail.
Is any addition code is required to send the mails
So I think there must one additional line of code after
Call Shell(strMozillaThunderbird , vbNormalFocus)
which does Send functionality