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!

 

[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

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]

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

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

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