Results 1 to 3 of 3

Thread: Send Email If Certain Cells are Modified In Excel

  1. #1
    Neophyte Switched-On's Avatar
    Join Date
    Jun 2020
    Posts
    2
    Articles
    0
    Excel Version
    365

    Send Email If Certain Cells are Modified In Excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have the below code which send an emails if the range M5:M9999 is changed. It works perfectly, but I also need an email sending to a different email address if L5:L9999 range is changed. I want a different message emailing to each address. Is someone able to help. Thank you


    Private Sub Worksheet_Change(ByVal Target As Range)Dim xRgSel As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xMailBody As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set xRg = Range("M5:M9999")
    Set xRgSel = Intersect(Target, xRg)
    ActiveWorkbook.Save
    If Not xRgSel Is Nothing Then
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailItem = xOutApp.CreateItem(0)
    xMailBody = "Cell(s) " & xRgSel.Address(False, False) & _
    xRgSel.Value & _
    " in the worksheet '" & Me.Name & "' were modified on " & _
    Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
    " by " & Environ$("username") & "."


    With xMailItem
    .To = "Email Address"
    .Subject = "Worksheet modified in " & ThisWorkbook.FullName
    .Body = xMailBody
    .Attachments.Add (ThisWorkbook.FullName)
    .Display
    End With
    Set xRgSel = Nothing
    Set xOutApp = Nothing
    Set xMailItem = Nothing
    End If
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    120
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    The simplest thing to do might be to simply duplicate the section that sends the message and modify it.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)Dim xRgSel As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xMailBody As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set xRg = Range("M5:M9999")
    Set xRgSel = Intersect(Target, xRg)
    ActiveWorkbook.Save
    If Not xRgSel Is Nothing Then
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailItem = xOutApp.CreateItem(0)
    xMailBody = "Cell(s) " & xRgSel.Address(False, False) & _
    xRgSel.Value & _
    " in the worksheet '" & Me.Name & "' were modified on " & _
    Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
    " by " & Environ$("username") & "."
    
    
    With xMailItem
    .To = "Email Address"
    .Subject = "Worksheet modified in " & ThisWorkbook.FullName
    .Body = xMailBody
    .Attachments.Add (ThisWorkbook.FullName)
    .Display
    End With
    Set xRgSel = Nothing
    Set xOutApp = Nothing
    Set xMailItem = Nothing
    End If
    ' **************************************
    Set xRg = Range("L5:L9999")
    Set xRgSel = Intersect(Target, xRg)
    ActiveWorkbook.Save
    If Not xRgSel Is Nothing Then
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailItem = xOutApp.CreateItem(0)
    xMailBody = "Cell(s) " & xRgSel.Address(False, False) & _
    xRgSel.Value & _
    " in the worksheet '" & Me.Name & "' were modified on " & _
    Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
    " by " & Environ$("username") & "."
    
    With xMailItem
    .To = "Email Address"
    .Subject = "Worksheet modified in " & ThisWorkbook.FullName
    .Body = xMailBody
    .Attachments.Add (ThisWorkbook.FullName)
    .Display
    End With
    Set xRgSel = Nothing
    Set xOutApp = Nothing
    Set xMailItem = Nothing
    End If
    '************************************
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

  3. #3
    Neophyte Switched-On's Avatar
    Join Date
    Jun 2020
    Posts
    2
    Articles
    0
    Excel Version
    365
    Thank you NormS, that's perfect.

Tags for this Thread

Posting Permissions

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