locked
Macro to send information when modified RRS feed

  • Question

  • Dear all,

     

    I would like to know if we could create a macro, which will send information to a list of email ids, when a particular excel file is modified manually or updated using other macros in it.

     

    This way i wish to save an excel file in a shared folder where authorised people can access - read/write and when they modify, a list of people who would actually be using the information can understand that there is an update.

     

    So less work to send emails, attachments, sometimes even missing important people and there by unncessary pressure and conflicts.

     

    I believe if we could solve, this using a macro this would be useful and it will defenitely be contributing to professional efficiency at work.

     

    Thank you in advance,

     

    SREE!

    Wednesday, June 6, 2007 12:03 PM

Answers

  • Hi

     

    I have done a simple example to get you started. Open the Visual Basic Editor (Under Tools - Macros on the menu). In the VBE select tools and then references on the menu. Go down the list and tick "Microsoft Outlook 11 Object Library" ( it might not be 11 if you do not have office 2003). Then select "This workbook" in the project explorer, on the right hand pane in the first dropdown box select Workbook, and in the second drop down select BeforeSave, then paste in the below code. The code below pops up a message box asking if to notify changes when the file is being saved, if Ok is pressed then an e-mail is sent. Try this on a new workbook, but remember to change the line .To = "Joe.Bloggs@Microsoft.com" for a valid e-mail address. Hope this gets you started.

     

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Dim Responce

    Responce = MsgBox("Notify changes to others?", vbOKCancel, "File being Saved")

    If Responce = vbOK Then
        Set olApp = Outlook.Application
        'Create e-mail item
        Set objMail = olApp.CreateItem(olMailItem)

        With objMail
            .To = "
    Joe.Bloggs@Microsoft.com"
           .Subject = "My Spreadsheet"
           .Body = "The Spreadsheet has changed"
           .Send
        End With
        Set objMail = Nothing
        Set olApp = Nothing
    End If
    End Sub

    Thursday, June 7, 2007 7:49 AM

All replies

  • Hi Sree

     

    Many ways to do this. I would declare a global variable of type boolean, which I would set to False with the Workbook Open event, for each sheet in the workbook I would use the Worksheet change event to set the variable to true. You can then use the Workbook  BeforeSave event to check if the variable is True then generate your e-mails.

     

    Regards

     

    ADG

    Wednesday, June 6, 2007 12:26 PM
  • Dear ADG,

     

    I understand some what, what you said - but i am not at all, programmar. It was too technical for me to do it myself with what you said. I tried to follow up based on your instructions. I am sorry i could not make it.

     

    Please help.

     

    Thank you in advance,

     

    SREE!

     

    Wednesday, June 6, 2007 1:15 PM
  • Hi

     

    I have done a simple example to get you started. Open the Visual Basic Editor (Under Tools - Macros on the menu). In the VBE select tools and then references on the menu. Go down the list and tick "Microsoft Outlook 11 Object Library" ( it might not be 11 if you do not have office 2003). Then select "This workbook" in the project explorer, on the right hand pane in the first dropdown box select Workbook, and in the second drop down select BeforeSave, then paste in the below code. The code below pops up a message box asking if to notify changes when the file is being saved, if Ok is pressed then an e-mail is sent. Try this on a new workbook, but remember to change the line .To = "Joe.Bloggs@Microsoft.com" for a valid e-mail address. Hope this gets you started.

     

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Dim Responce

    Responce = MsgBox("Notify changes to others?", vbOKCancel, "File being Saved")

    If Responce = vbOK Then
        Set olApp = Outlook.Application
        'Create e-mail item
        Set objMail = olApp.CreateItem(olMailItem)

        With objMail
            .To = "
    Joe.Bloggs@Microsoft.com"
           .Subject = "My Spreadsheet"
           .Body = "The Spreadsheet has changed"
           .Send
        End With
        Set objMail = Nothing
        Set olApp = Nothing
    End If
    End Sub

    Thursday, June 7, 2007 7:49 AM
  • Dear ADG,

    the response you wrote here is helpful for me...
    Thank you...

    Regards
    Repath.


    Love the Love that Loves the Love and Hate the Love that Loves the Hate.

    • Proposed as answer by Repath Athyala Saturday, September 8, 2012 8:30 AM
    Saturday, September 8, 2012 8:30 AM