Excel 2010:  Notification Macro

Posted on 2012-08-31
Last Modified: 2012-08-31
I have a spreadsheet that is nothing more than a change request spreadsheet.  User's simply enter in any changes they'd like to see in this one application I work with.  I then go out to the shared location on the network periodically and look at this spreadsheet for any new entries made.

    I'm curious, if we could add a macro control button in column H starting on row 3.  I can then just copy that button down to H4, H5, H6 etc.  When they finish entering their request, they can simply click this "Notify" control button it then emails me making me aware a change has been made to the spreadsheet.  Is this possible?
Question by:itsmevic
    LVL 24

    Accepted Solution

    If you modify this code to select the range with the request, it will do what you want:

    Sub Send_Range()
       ' Select the range of cells on the active worksheet.
       ' Show the envelope on the ActiveWorkbook.
       ActiveWorkbook.EnvelopeVisible = True
       ' Set the optional introduction field thats adds
       ' some header text to the email body. It also sets
       ' the To and Subject lines. Finally the message
       ' is sent.
       With ActiveSheet.MailEnvelope
          .Introduction = "This is a sample worksheet."
          .Item.To = "E-Mail_Address_Here"
          .Item.Subject = "My subject"
       End With
    End Sub

    Open in new window

    I think you could even drop the range alltogether and it would work.

    Author Closing Comment

    Flawless victory! Thank you again S!

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now