Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2014
    Posts
    15
    Thanks
    7
    Thanked 0 Times in 0 Posts

    How to send email automatically when cell value changes

    Hi all, I've been searching through the lounge, and have found some posts that at least get me thinking..... but nothing that actually provides me with enough to continue on. Trying to set things up so that if a cell value changes, an automatic email is sent to a group of people.
    Situation.
    5 various units share a workbook where they will indicate status as Red, Yellow or Green (by unit) What we'd like to be able to do, is if the status changes, an email is automatically generated to notify everyone that a change has occurred. In other words there are 5 status columns, one for each unit. Notification of a change is status, by any unit, needs to be sent to all users of the shared workbook.

    Very much a newbie and learning as I go. Not really good with vba, slowly starting to understand, but can steal responses with the best of them, though I give credit where credit is due. All users have outlook as their email, tied to an exchange server. All help and thoughts appreciated.

    Thanks

    Fred

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Fred

    ..when do you (specifically) want the emails sent?
    i.e. each and every time the relevant status change occurs (i.e. instantly), or
    ..when the workbook is saved?
    Are the status cells all on the same sheet??
    can anyone change any status?

    zeddy

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    In addition to Zeddy's question, here are a few more. Will the status change by the inputting of a value into the status column or solely changing the color. If changing the color only, will this happen manually, by code, or by conditional formatting? Where are the email addresses stored? Can you post a sample?

    Maud

  4. #4
    New Lounger
    Join Date
    Apr 2014
    Posts
    15
    Thanks
    7
    Thanked 0 Times in 0 Posts

    send email reply

    Thanks for all the great questions. To answer: email to be sent each and every time relevant status change occurs. all status cells are on the same sheet. anyone with access to the sheet (on a shared storage server) status will change by person inputting a new "status" (red, yellow or green - word) into the status column for their work group. email addresses currently are stored in outlook. I can make a distribution group in Outlook (managed by exchange server - all members through this server) if that would help.

    Thanks again. Hopefully the attached helps.
    Attached Files Attached Files

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Fred,

    The attached modified workbook will detect a change in the status columns then email your distribution group notification of the change. The code uses a class that I developed for rapid email and task reminder creation. Below is a copy of the email generated.

    Status1.png

    The sport, unit, and status are indicated. Data validation has been added to aid in status selections.

    HTH,
    Maud

    In the worksheet module:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("StatusCells")) Is Nothing Then
        SendEmail Target
    End If
    End Sub
    In a standard module:
    Code:
    Public Sub SendEmail(rng As Range)
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim Email As ClsTaskEmail
    Dim body As String, row As Long, col As Long
    Dim sport As String, unit As String
    Set Email = New ClsTaskEmail
    '--------------------------------
    'BUILD EMAIL COMPONENTS
    row = rng.row
    col = rng.Column
    With ActiveSheet
    sport = .Cells(row, 1)
    unit = .Cells(2, col - 1)
    body = "A change in staus has occurred:" & Chr(13) & Chr(13) & _
            "Sport- " & sport & Chr(13) & _
            "Unit- " & unit & Chr(13) & _
            "Status- " & rng.Value
    End With
    '--------------------------------
    'SETUP EMAIL
    If Email.OutlookCheck = False Then Exit Sub
    Email.EmailTo = "Distribution_Group@gmail.com"
    Email.EmailCopyTo = ""
    Email.EmailBlindCopyTo = ""
    Email.EmailSubject = "Status Change"
    Email.EmailBody = body
    Email.EmailReadReceipt = True
    Email.EmailFollowUpDueDate = vbnothing
    Email.EmailAttachmentFile = ""
    Email.EmailCellComment = Nothing
    Email.EmailCommentInhibit = False
    Email.EmailCreate
    End Sub
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Apr 2014
    Posts
    15
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Thumbs up Thanks

    Big thank yous to zeddy and Maudibe for their help. The code provided works great. I modified it to take into account all of the data and currently having others (that even have less knowledge than I about how things work) testing to make sure they can use it. Seems pretty fool proof though. Thanks again for all of the assistance. I learned a ton.

    Thanks to everyone in the lounge for being so willing to share and help others, regardless of their skill level. It is just a great community.

    Thanks again,

    Fred

  7. #7
    New Lounger
    Join Date
    Jun 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Folks,

    is it possible to send that email in background without user pressing the send button?

    thanks

    Mudassar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •