Excel Macro - Automatic email

Question

I'm setting up a macro in excel to automatically send an email when a cell in updated. Is it possible to include in the email body the contents of a cell? For example if cell G7 is updated, include cell B7 content in email? The cell row would be the same, the column would change.

This is the code that I'm using:

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("G2:G17")
    Set xRgSel = Intersect(Target, xRg)
    ActiveWorkbook.Save
    If Not xRgSel Is Nothing Then
        Set xOutApp = CreateObject("Outlook.Application")
        Set xMailItem = xOutApp.CreateItem(0)
        xMailBody = "Hello," & vbCrLf & vbCrLf & "The worksheet " & Chr(34) & ActiveWorkbook.Sheets(3).Name & Chr(34) & "has been completed and ready for 1st level review."

        With xMailItem
            .To = ""
            .Subject = ""
            .Body = xMailBody
            .Display
        End With
        Set xRgSel = Nothing
        Set xOutApp = Nothing
        Set xMailItem = Nothing
    End If
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

The macro works perfectly well but I want to get rid of worksheet name and instead display the message in cell B2 (if cell G2 has been updated.

Answer 1

Just to close this question out: you can get the row using Target.Row.

So following your example, you can refer to B2 using

Me.Cells(Target.Row, 2)

or perhaps

Me.Range("B" & Target.Row)