How do I send a notification email?

[stodd72]

Hello Everyone,

I have a workbook on a network drive that many employees have access to and update. How do I get a notification email sent to me after someone has saved the workbook? I do not know much about VB so I am coming here to the experts to see if you can help me out.

Thank you.
Stodd

[Richard Buttrey]

Hi,

Unless you have some way of capturing the name of the user then you won't know who has saved the workbook, but the following will tell you that it has been saved. It assumes you use Microsoft Outlook as the email client. If not you'll need to tell us.

The code goes in the BeforeSave workbook procedure

Please Login or Register  to view this content.

[stodd72]

Richard,

Thank you for the reply. Yes I am using outlook as the email client. This will send an email notification to me when the workbook has been saved? Is there something i need to do to get it to send the email?

Stodd

[pbigos]

You can add this to get the name of the user
Dim Name as String
Name = Application.userName

.Subject = "The xxxxx workbook has just been saved"
.Body = Name + "has just saved the workbook"

[stodd72]

I've tried to paste this code into VB for the workbook, however, I'm not getting any emails yet. Not sure what I am doing wrong.. I hit ALT+F11 to get to VB and on the left side under project - VBAProject i click on the "Thisworkbook" and then i go to "View" menu to view the code. Then under that in the code window I enter the code below for the workbook and beforesave are both selected from the drop downs.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = "Ihave my email address inside here"
.cc = ""
.BCC = ""
.Subject = "The xxxxx workbook has just been saved"
.Body = "email body text if applicable"
.Send

End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End

End Sub

[pbigos]

I copy pasted the code. Here is a screen shot of what mine looks like. It sounds like you are doing everything right.
Another thing to check is. Are macros disabled on their computers. Because if they are then well it wont run.

Capture.JPG

[pbigos]

I copy pasted the code. Here is a screen shot of what mine looks like. It sounds like you are doing everything right.
Another thing to check is. Are macros disabled on their computers. Because if they are then well it wont run.

Attachment 374766