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
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"
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.
When we need to auto notify based on a excel sheet data, the VBA code needs outlook. I mean to say. Excel sheet with the help of outlook send a mail or reminder to the respective mail ID. I think outlook express don't have that kind of support to send a reminder.
Though I can send you a VBA that sends mail reminder on the basis of date of project but it needs outlook, I hope it will not work with outlook express. If you need that excel sheet with macro. let me know... my mail id is satiya03@gmail.com
I am trying to send mail to individual on a defined date in excel sheet as a reminder using excel and outlook. Can you help me ragarding this? My mail ID is kulkarnihd@indiatimes.com
hi.. I am kiru.
i am impressed by the concept of your codings.
by the way, can you please explain me where exactly to paste your coding and what are the basic steps that I need to follow before I do this.?
it will be really helpful if this code works for me.
Just in case you haven't gotten the answer to this question yet. I'm using excel 2007. Click on the office icon in the upper left corner of the excel window. At the very bottom of the drop down it will say excel options. click on that and it will open a menu. Check the box next to show developer tab in Ribbon. Click OK. Next click the developer tab on the ribbon (the tabs across the top of the document that says home, insert, page layout, etc.) once this is clicked there will be a button on the far left that says Visual Basic. Click on this and a new window will open up. under file you will see a list of sheets and an icon that says this workbook. Double click this image and paste the information into the window that pops up.
the code you paste was missing a needed comma I've added it below. you will need to save a copy of this document with the file extension .xlsm which is a macro enabled file. In addition you may need to go into the trust center under your excel options and check the enable macros using pop ups once you have this new document to get it to work. hope this helps. :-)
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"
I have stolen many answers to problems from the internet but never before been felt compelled to write a comment. You are a genius - I salute you. Many thanks :-)
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?
@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
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
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
6112 users have said thank you to us this month
This works like a charm in Office 2010 as well.
- Posts
- 1
- Registration date
- Friday October 26, 2012
- Status
- Member
- Last seen
- October 26, 2012
-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!
- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016
753 -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