Results 1 to 2 of 2

Thread: VBA to Send Email Alert to Specific Person When Cell In Row is Updated
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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)


    A B C D E F G H I J K L M N O
    Game Priority Location 1 Priority Location 2 Member ID Name # Points Upgrade? Requesting Staff Initials Request Notes Paciolan Price Staff Outreach Initials Outreach Notes Result
    Miami Main 123456 Example 1 10 100 No NF Example 125:Y:1-2

    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!



  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,303
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

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

    Try this

    Change data in red by your information.

    Code:
    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
          Case "ES": eMail = "es@gmail.com"
          Case "NF": eMail = "nf@gmail.com"
          Case "BB": eMail = "bb@gmail.com"
          Case Else: eMail = "dam@gmail.com"
        End Select
        Set dam = CreateObject("Outlook.Application").CreateItem(0)
        '
        'Mail Information
        dam.To = eMail
        dam.Subject = "One of their requests has been updated"
        dam.Body = "Alert !!!!"
        dam.Display 'use .Send to send
      End If
    End Sub
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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