How to send email automatically when cell value changes

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

A clear example of when a sample spreadsheet would be most helpful. I know Zeddy has this already mentally solved. Depending if the color of the status changes using conditional formatting, I have a solution in mind as well. We need only two things: a spreadsheet with sample data and confirmation that Derfacnauck is still on board.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

41043-Status1

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)
[COLOR=”#008000″]’——————————–
‘DECLARE AND SET VARIABLES[/COLOR]
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
[COLOR=”#008000″]’——————————–
‘BUILD EMAIL COMPONENTS[/COLOR]
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
[COLOR=”#008000″]’——————————–
‘SETUP EMAIL[/COLOR]
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

Reply | Quote

Big thank yous to zeddy :fanfare: and Maudibe :fanfare: 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. :thewave:

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

Reply | Quote

Hi Folks,

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

thanks

Mudassar

Reply | Quote