# 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.


		Code:
__


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

salguoD said:


> 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?



		Code:
__


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

Jaryd said:


> 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?


----------



## 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!


----------



## nhanke

i was able to get this working, needed more than 1 cell in range.


----------



## johnyboy57

hi, i want to set this up so it sends the e-mail without the body of the excel spreadsheet but whatever i do it is not working, can you help please,

thanks in advance 



PeteRooneyHome said:


> 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


----------



## Valley

PeteRooneyHome said:


> 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.
> 
> 
> Code:
> __
> 
> 
> 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



Hello Pete,

This is very helpful, but I need some function, for a shared excel file, once someone changed and saved file, one email can be received by me, and this person also receive an email once I response this change, could you please to help to program codes, highly appreciated your contribution!

Best regards,
Valley


----------



## Gyurrri

Hi Pete,

I am using Office 2013 and the code does not work. I am new to VBA so it all my fault, but maybe the 2013 has some changes which effects this?
 I also want to have this code to send an email which includes the information of the modified cells i.e which cells were modified. Would you please be so kind to help me out on this?

Many thanks.

Cheers
Gyurrri


----------



## joshadambrown

I used this code a few days ago, and was thrilled with the outcome!! Grateful for your help!

Quick question... I went to update my sheet today (still testing) and now the preview in outlook comes out in all sorts of crazy colors. First the background was the same as some of my title blocks, and now it all comes through black, everything except for what has been filled with a color. Not sure if it is an Excel issue or Outlook issue. But I would love some help here.

Thanks in advance!!



NewOrderFac33 said:


> 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.
> 
> 
> Code:
> __
> 
> 
> 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


----------

