VBA to Send Email Alert to Specific Person When Cell In Row is Updated

[njfulton]

We have a shared spreadsheet that tracks requests from customers - When a request has been filled, a value is inputted into a specific cell in that row...I'd like to try and find VBA that could send an email to that employee when one of their requests has been updated.

I've included example data below

• When Column K (Paciolan) is updated, an email alert should be sent
• Column I (requesting staff initials) would determine what email address the alert should be sent to (there are about 10 different staff members who submit requests)

[TABLE="width: 1000"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD="align: center"]Game[/TD]
[TD="align: center"]Priority Location 1[/TD]
[TD="align: center"]Priority Location 2[/TD]
[TD="align: center"]Member ID[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]#[/TD]
[TD="align: center"]Points[/TD]
[TD="align: center"]Upgrade?[/TD]
[TD="align: center"]Requesting Staff Initials[/TD]
[TD="align: center"]Request Notes[/TD]
[TD="align: center"]Paciolan[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Staff Outreach Initials[/TD]
[TD="align: center"]Outreach Notes[/TD]
[TD="align: center"]Result[/TD]
[/TR]
[TR]
[TD]Miami[/TD]
[TD]Main[/TD]
[TD][/TD]
[TD]123456[/TD]
[TD]Example 1[/TD]
[TD]10[/TD]
[TD]100[/TD]
[TD]No[/TD]
[TD]NF[/TD]
[TD]Example[/TD]
[TD]125:Y:1-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here is a link to the screenshot of the spreadsheet - https://ibb.co/gFNC1Hw

I've found some information on how to receive an alert when a cell is updated, but nothing for custom alerts based on the value of another cell in that row.

Thank you so much for your help!

 

[DanteAmor]

Try this

Change data in red by your information.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("K:K")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Target.Row < 2 Then Exit Sub
    Dim eMail As String, dam As Object
    '
    'Fill email address
    Select Case Cells(Target.Row, "I").Value
[COLOR=#ff0000]      Case "ES": eMail = "es@gmail.com"[/COLOR]
[COLOR=#ff0000]      Case "NF": eMail = "nf@gmail.com"[/COLOR]
[COLOR=#ff0000]      Case "BB": eMail = "bb@gmail.com"[/COLOR]
[COLOR=#ff0000]      Case Else: eMail = "dam@gmail.com"[/COLOR]
    End Select
    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    '
    'Mail Information
    dam.To = eMail
    dam.Subject = "[COLOR=#ff0000]One of their requests has been updated[/COLOR]"
    dam.Body = "[COLOR=#ff0000]Alert !!!![/COLOR]"
    dam.Display 'use [COLOR=#0000ff].Send to send[/COLOR]
  End If
End Sub