Including a cell value in the body of an email

[ryeire]

Hi all,

I have a code here for logging data and sending emails automatically.

I want to be able to send an email when column E has 'Yes' in it and I want the body of an email to include the corresponding cell in column G.

I have code to send the email I just can't find a way to get the corresponding cell value in column G to be part of the body. I have been able to include an exact cell (e.g. cell G2) but this is not what I need. I have also tried to use ActiveCell but this wont work as the user fills in the data with a userform. I will include the code below.

I hope this makes sense and someone is able to help me with it. Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 5 Then Exit Sub
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object
    If Target = "Yes" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        X = Range("G" & ActiveCell.Row).Value
        With OutMail
            .To = ""
            '.CC = ""
            .Subject = "ESD Station Needs Repair"
            .HTMLBody = "Hi,<br>" & "<br>" & "The following stations need repair:  " & X
            .Display    'change to .send
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

Excellent! You are welcome.
Glad to hear that we were finally able to identifty and fix the issue.