Email Notification When Spreadsheet Is Saved
I created a spreadsheet a few months back. It went from just one person using it, to 3 people. The spreadsheet lives in the network on the people using it asked me to add a feature to it. I looked in the forum for something similar, but couldn't find anything. Maybe my search criteria was off. Anyway...
The ladies who use it would like it to generate an email to the group (3 static email addresses) when saved. I'm not sure the goal, but I said I would look into it. Is something like that possible?
Here is what I got, but it doesn't work. Any assistance would be really awesome.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim answer As String
answer = MsgBox("Do you want to save?", vbYesNo, "Save")
If answer = vbNo Then Cancel = True
If answer = vbYes Then
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
newmsg.Recipients.Add ("matt@mysef.com")
newmsg.Subject = "Project Tracking Form Was Saved"
newmsg.Body = "Just an FYI"
newmsg.Display
newmsg.Send
MsgBox "insert confirmation box test here", , "title of confirmation box"
End If
Me.Worksheets.Save
End Sub
Private Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
newmsg.Recipients.Add ("a@b.com")
newmsg.Subject = "Project Tracking Form Was Saved"
newmsg.Body = "Just an FYI"
newmsg.Send
MsgBox "insert confirmation box test here", , "title of confirmation box"
End Sub
- Excel already provides confirmation of whether you want to save
... as a process, I don't think this is a good one.
my recommendation is to get into the habit of saving a shared workbook with an incremental number each time
- this way you have a full audit of the changes...
Alternatively (and far better from a design perspective), separate the data out and use the Excel as the data entry and modifier..
example here:
http://excelexperts.com/Share-Trading-System