Excel 2010 - Send Email When File Is Saved/Updated

[tviloria]

Dear All,

Im new to the forums and I need some help regarding placing a macro that will allow Excel to send a small email message to me whenever the file has been updated by the users who will access it. I've been checking the list of threads here regarding the same inquiry I have but not one of them is clear or helpful enough for what I need to do.

I have an .xlsx file (MS Excel 2010) that contains a training calendar and is stored on a shared network folder that is accessible to all users. I would like that each time a user modifies and "saves" his changes for an email alert to be sent to me.

Appreciate if you could walk me through as well as I am not well-versed with Excel macro editing/creation.

Thanks!

 

[NewOrderFac33]

Hi, Tviloria,

An easy way is to paste the following code into the "ThisWorkbook" module of your workbook (you may need to expand "Microsoft Excel Objects" under the workbook name) within the Visual Basic Editor.

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


    Sheets("TargetSheet").Range("TargetRange").Select '#
    ActiveWorkbook.EnvelopeVisible = True
    
    With ActiveSheet.MailEnvelope
        .Introduction = "Hello, Tvoloria! - the workbook was saved by " & Environ("USERNAME") & " at " & Format(Now(), "ddd dd mmm yy hh:mm")
        .Item.To = "Pete.Rooney@bet365.com"
        .Item.Subject = "Workbook Saved!"
        .Item.display
        '.Item.send
    End With


End Sub

You can either use the [Sheets("TargetSheet").Range("A1:D10").Select] line to include a specific worksheet range with your email, or simply comment the line out by prefixing it with a ' character if all you want is a notification.
It will include the Windows Username of the person who saved the workbook and the date/time when it was saved.
The code is currently set to display the email (the user needs to click the "Send" button), but once you've got it working as you want, comment out the .item.display line and uncomment the .item.send line.

You'll also need to re-save the workbook as a Macro-enabled workbook with a .xlsm file extension (just do a SaveAs and change the value in "Save as Type" at the bottom of the dialog box under the filename).

Hope this helps

Pete

 

[NewOrderFac33]

P.S. Code of this sort is known as "Event Code", as it runs when a particular even takes place (in this case, workbook save). It is pasted directly into either "ThisWorkbook" or one of your worksheets within the VB editor.
You paste this sort of code in this way:

1. Copy the code as shown
2. Open your workbook
3. [Alt]+[F11] to open the Visual Basic Editor
4. Within “Microsoft Excel Objects”, double click on the worksheet (or “This Workbook”) where you want the code to run.
5. Paste the code with [Control]+v
6. [Alt]+q to exit the Editor, and return to Excel

The other sort of code is pasted into a standard code module. You can run these procedures either via Alt+F8 of by attaching a procedure to a worksheet object, such as an AutoShape.
You can have more than one standard code module in a workbook, but workbooks don't them as standard - you need to insert them.
You paste code into a standard code module like this:

1. Copy the code
2. Open your workbook
3. [Alt]+[F11] to open the Visual Basic Editor
4. [Alt]+i to activate the Insert menu
5. If required, press m to insert a new standard code module (it will be named “Module1”)
6. Paste the code with [Control]+v
7. [Alt]+q to exit the Editor, and return to Excel
8. To run the macro from Excel, [Alt]+[F8] to display the Run Macro dialog
9. Double click the macro's name to run it.

Have fun!

Pete

 

[salguoD]

Firstly, this is very straightforward and easy to implement. Thank you!

Secondly, I have a question.

The purpose of my particular excel file is track a new product process, and I'm looking to send a project update to Sales members when say "Sheet 1 (project overview)" has been updated. Sheet 1 will pull information from "Sheets 2-10, i.e. (R&D, costing, packing, etc.)", but it IS possible that someone may update a sheet that does not flow information to Sheet 1 (Project Overview). In this instance, how can I only have the email be sent when there are modifications to Sheet 1(Project Overview), and not when other sheets have been updated or just the workbook saved.

Thank you in advance for any help on this question.

 

[NewOrderFac33]

Firstly, this is very straightforward and easy to implement. Thank you!

Secondly, I have a question.

The purpose of my particular excel file is track a new product process, and I'm looking to send a project update to Sales members when say "Sheet 1 (project overview)" has been updated. Sheet 1 will pull information from "Sheets 2-10, i.e. (R&D, costing, packing, etc.)", but it IS possible that someone may update a sheet that does not flow information to Sheet 1 (Project Overview). In this instance, how can I only have the email be sent when there are modifications to Sheet 1(Project Overview), and not when other sheets have been updated or just the workbook saved.

Thank you in advance for any help on this question.

This one may have to go to tomorrow as it's 23.53 here. I assume that you only want the email to be sent when changes occur in certain areas of Sheet 1? And do you want these changes to be changes that the user makes themselves, or changes that occur when the value of a formula changes as a result of a change in one of the other sheets?
Pete

 

[salguoD]

Knowing both would be helpful, but the latter is what I most interested in at the moment. Sheet 1 is just referencing cells from the other sheets, no data entry is happening on that sheet, it's just a Product Development Overview. The e-mail recipients do not need to know the details of the other sheets. Thanks for any help here!

 

[niklas24]

Bump. I am trying to have an email sent through gmail when the file is saved after changes occur to say column B in sheet 1 and sheet 2. I have created a module with the following code to generate the email, which is working if I manually run the macro. What do I need to add to this for it to send the email automatically after it is saved and any changes are made to column B on either sheet?

Sub CDO_Mail_Small_Text()

    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    '    Dim Flds As Variant


    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")


        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxx@xxx.gov"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
            .Update
        
        End With
    
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"
    With iMsg
        Set .Configuration = iConf
        .To = "xxx@xxx.gov"
        .CC = ""
        .BCC = ""
        .From = """myName"" <xxx@xxx.gov>"
        .Subject = "New figures"
        .TextBody = strbody
        .Send
    End With


End Sub

Also, is there a way to password protect or keep people from opening the VB code. Since I have to put an email password in there I don't want anyone to be able to see it or mess with the code.

Thanks</xxx@xxx.gov>

 

[Jaryd]

Pete, thanks for your explanation in response. It was very helpful to me

 

[NewOrderFac33]

Pete, thanks for your explanation in response. It was very helpful to me

You're welcome!

 

[nhanke]

If I just want "Workbook was updated by..." how would I remove the preview of the excel file?