receive email automatically when excel file is updated

**kiran654** · 08-22-2008, 12:14 AM

i would like to know if there is a way to generate automated email whenever excel file is updated by any one.

Thanks,
Kiran

**royUK** · 08-22-2008, 02:02 AM

What do you mean by updated? You can't send an email for each change, so you would need to determine what changes should trigger the macro.

**kiran654** · 08-22-2008, 09:51 AM

Hi RoyUK,

I mean when ever the file is saved by someone i would like to receive an email automatically.

Thanks,
Kiran

**royUK** · 08-22-2008, 03:12 PM

Try this

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim OutApp As Object
    Dim OutMail As Object
    Const SendTo As String = "your email address"

    Set OutApp = CreateObject("Outlook.Application")

    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = SendTo
        .Subject = ThisWorkbook.Name & " has been amended"
        .Body = " add a message here"
        .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Right click on the Excel icon, top left & select view code. When the VB Editor opens, set the left hand drop down to Workbook, & find BeforeSave in the right hand drop down. Copy & paste the code into that procedure.

**kiran654** · 08-22-2008, 04:04 PM

Hi RoyUK,

Sorry for being so dumb, but I could not get where to paste the code. I was able to open a VB editor from the excel sheet by the following menus Tools-->Macro-->Visual Basic Editor.

From here I could not follow your instructions. "set the left hand drop down to Workbook, & find BeforeSave in the right hand drop down".

Help is greatly appreciated.

Thanks,
kiran

**Leith Ross** · 08-22-2008, 07:48 PM

Hello kiran654,

Here is how to save a Workbook Event macro...

How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

**kiran654** · 08-23-2008, 01:01 AM

Hi Leith,

I was able to save the macro.
In the code provided by RoyUK, i have placed my email address. Then what should I do? i tried to update that excel sheet and then save again, but i did not receive an email.
just want to make sure if i am not doing something which needs to be done or vice-versa?
By the way thanks for helping me out in saving the macro

**Leith Ross** · 08-23-2008, 01:57 AM

Hello kiran654,

If your Outlook email account hasn't been setup, the email won't go out. Did you set the constant SendTo to your email address?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim OutApp As Object
    Dim OutMail As Object
    Const SendTo As String = "your email address"

If you still have problems, post your workbook for review.

Sincerely,
Leith Ross

**kiran654** · 08-23-2008, 10:57 AM

I created a sample work book named sample08.xls
I have created two rows and saved it. Then i created the macro as you said.
Then i tried to add another row into the sheet and saved it.
Then i checked my email, but no emails.
yes, i checked my out look email(my work email is already configured to outlook and i gave my work email in the code below).

Do you mind checking if the code actually is working?

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim OutApp As Object
    Dim OutMail As Object
    Const SendTo As String = "myname@company.com"

    Set OutApp = CreateObject("Outlook.Application")

    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = SendTo
        .Subject = ThisWorkbook.sample08 " has been amended"
        .Body = " add a message here"
        .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

**royUK** · 08-23-2008, 01:59 PM

Please read the Forum Rules & use Code Tags in future

Youv'e changed this line unnecessarily ThisWorkBook.Name will pick up the workbook name, your code will error & should be red in the VB Editor

       .Subject = ThisWorkbook.sample08 " has been amended"

It should be {code] .Subject = ThisWorkbook.Name & " has been amended"[/code]

If you amend it and have MS OutLook on your sending PC and placed the code in the correct place then it works.

**kiran654** · 08-24-2008, 12:34 AM

Hi RoyUK,

I tried it but it did not work for me. Probably I am not doing it right.

Thanks,
Kiran654

**royUK** · 08-24-2008, 02:42 AM

Attach the workbook that you have ut the code in.

**kiran654** · 08-25-2008, 10:26 AM

Hi RoyUK,

Here is the excel file with the code attached.
Thank you very much for your help.

Thanks,
Kiran

**royUK** · 08-25-2008, 11:57 AM

You've placed the code in he worksheet not the workbook code module.

**kiran654** · 08-25-2008, 07:06 PM

hi RoyUK,

I saved the file on my desktop. Then i added a row and then saved again, but did not receive any email. Only other thing i changed is the existing in the code to my work email id since it is configured to outlook.
Did it work for you?

Thanks,
Kiran

**royUK** · 08-26-2008, 04:17 AM

The code sends an email each time the workbook is saved, no problem here.

**kiran654** · 08-26-2008, 02:53 PM

Hi RoyUK,

I am not sure why but I am not able to get an email when i save it.
Thanks for your help.

Thanks,
Kiran

**kiran654** · 08-27-2008, 12:02 PM

Hi RoyUK,

I pasted your code in the right place now and when i saved it, it asked me if it can send an email. I clicked YES and it did send an email.
I closed the excel file, re-opened and updated osmething in the file and re-saved it.This time i didn to get any email.
I thought when i open the file, update something and save it, an email issent automatically.
help is greatly appreciated

**SKH** · 07-28-2014, 11:16 AM

Hi,

How can i send to more than 1 email with using the code below ?

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim OutApp As Object
Dim OutMail As Object
Const SendTo As String = "your email address"

Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = SendTo
.Subject = ThisWorkbook.Name & " has been amended"
.Body = " add a message here"
.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub