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]

Note that since you are using a "Worksheet_Change" event, this will only run as the value in column E is manually changed to "Yes".
I hope that is how you are intending it to work.

If so, then you should to change this row:

       X = Range("G" & ActiveCell.Row).Value

to this:

       X = Range("G" & Target.Row).Value

 

[ryeire]

@Joe4 Thanks for your help.

Yes that was my intention, but if you have any other suggestions please do share as I am still getting myself familiar with VB. The "yes" is entered using a userform on the main sheet and this code would be in sheet 2.

I tried that code but it still doesn't work. The email is blank where the "X" should be. Do you have any other ideas?

 

[Joe4]

The "yes" is entered using a userform on the main sheet and this code would be in sheet 2.

The way your code is written, it only runs when a value in column E on that particular sheet is manually updated to "Yes".
It is not triggered off a change to a UserForm, nor any formulas in column E whose values may change.

 

[ryeire]

The userform has a checkbox with yes or no as the options. The value of the checkbox is then inserted into column E. As of now, when I enter yes into the column via the userform it triggers the email to pop up.

 

[Joe4]

Right after this line in your code:

X = Range("G" & Target.Row).Value

temporarily add this Message Box:

MsgBox "Row updated: " & Target.Row & vbCrLf & "Value in column G: " & X

and see what it returns to the screen when run.

 

[ryeire]

The message box the pops up doesn't include the value thats in G unfortunately

 

[Joe4]

That seems to suggest that there is nothing in cell G9.

Are you sure that there is something in there?
Is it part of a merged cell?

What happens if you try this line:

MsgBox "G9: " & Range("G9").Value

 

[ryeire]

The value in column G gets entered in the same userform as the value in column G. Would this be the issue?

It is not a merged cell. This is the result of your new line

 

[Joe4]

That does not seem to make any sense to me. Can you re-post your current version of the code, exactly as you have it?