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

 

[ryeire]

Yes no problem. Do you want me to include some of the userform code?

 

[ryeire]

This is the sheet 2 code

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" & Target.Row).Value
        'MsgBox "G9: " & Range("G9").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

 

[ryeire]

This is the userform code

Private Sub ComboBox1_Change()

End Sub

Private Sub cmdContinue_Click()
    Dim msgValue As VbMsgBoxResult
    
    msgValue = MsgBox("Do you want to save the data?", vbYesNo + vbInformation, "Confirmation")
    
    If msgValue = vbNo Then Exit Sub
    
    Call Submit
    Call Reset
End Sub

Private Sub cmdReset_Click()
    Dim msgValue As VbMsgBoxResult
    
    msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbInformation, "Confirmation")
    
    If msgValue = vbNo Then Exit Sub
    
    Call Reset
End Sub

Private Sub cmdYes_Click()

    FailedStationFormW.Show
    
End Sub

Private Sub Frame1_Click()

End Sub

Private Sub OptionButton1_Click()

End Sub

Private Sub TextBox2_Change()

End Sub

Private Sub Image1_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As MSForms.fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

End Sub

Private Sub UserForm_Initialize()
    
    Call Reset
    
End Sub

This is the module code

Option Explicit

Sub Reset()

    Dim iRow As Long
    
    iRow = [Counta(Weekly!A:A)] 'idetifying the last row
    
    With frmWeeklyForm
    
        .txtOp.Value = ""
        .txtArea.Value = ""
        .txtCom.Value = ""
        .txtDate.Value = Format(Date, "dd/mm/yyyy")
        .txtFails.Value = ""
        
        .cmbCheck.Clear
        
        .cmbCheck.AddItem "Yes"
        .cmbCheck.AddItem "No"
        
        .cmbFails.Clear
        
        .cmbFails.AddItem "Yes"
        .cmbFails.AddItem "No"
         
    
    End With



End Sub


Sub Submit()

    Dim sh As Worksheet
    Dim iRow As Long
    
    Set sh = ThisWorkbook.Sheets("Weekly")
    
    iRow = [Counta(Weekly!A:A)] + 1
    
    With sh
    
        .Cells(iRow, 1) = frmWeeklyForm.txtDate.Value
        
        .Cells(iRow, 2) = frmWeeklyForm.txtOp.Value
        
        .Cells(iRow, 3) = frmWeeklyForm.txtArea.Value
        
        .Cells(iRow, 4) = frmWeeklyForm.cmbCheck.Value
        
        .Cells(iRow, 5) = frmWeeklyForm.cmbFails.Value
        
        .Cells(iRow, 6) = frmWeeklyForm.txtCom.Value
        
        .Cells(iRow, 7) = frmWeeklyForm.txtFails.Value
        
    
    End With


End Sub


Sub Show_WeeklyForm()
    
    frmWeeklyForm.Show

End Sub

 

[ryeire]

Sorry earlier I meant column G gets entered in the same userform as column E

 

[Joe4]

It the grand scheme of things, it SHOULDN'T matter, but maybe try declaring your "X" variable (it is good practice to do so anyway). i.e.

    Dim OutApp As Object, OutMail As Object
    Dim X as String

You could also try changing this:

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

to this:

X = Target.Offset(0, 2)

 

[ryeire]

no that didn't work either . Are you able to define column E as the target column and then the offset should be column G?

 

[ryeire]

Just playing around with it; If I input a number into column G manually and then input "yes" via the userform, it works perfectly

 

[ryeire]

that is using

X = Target.Offset(0, 2)

 

[Joe4]

I think I might see what is going on. I think this section may be the culprit:

    With sh
    
        .Cells(iRow, 1) = frmWeeklyForm.txtDate.Value
        
        .Cells(iRow, 2) = frmWeeklyForm.txtOp.Value
        
        .Cells(iRow, 3) = frmWeeklyForm.txtArea.Value
        
        .Cells(iRow, 4) = frmWeeklyForm.cmbCheck.Value
        
        .Cells(iRow, 5) = frmWeeklyForm.cmbFails.Value
        
        .Cells(iRow, 6) = frmWeeklyForm.txtCom.Value
        
        .Cells(iRow, 7) = frmWeeklyForm.txtFails.Value
        
    
    End With

So your code is running when column E is updated, pulling the value from column G.
However, if column E is being updated BEFORE column G is, the code will be fired before a value has been placed in column G!
Maybe try changing the order of this, so column E is the last thing updated, i.e.

    With sh
    
        .Cells(iRow, 1) = frmWeeklyForm.txtDate.Value
        
        .Cells(iRow, 2) = frmWeeklyForm.txtOp.Value
        
        .Cells(iRow, 3) = frmWeeklyForm.txtArea.Value
        
        .Cells(iRow, 4) = frmWeeklyForm.cmbCheck.Value
        
        .Cells(iRow, 6) = frmWeeklyForm.txtCom.Value
        
        .Cells(iRow, 7) = frmWeeklyForm.txtFails.Value
        
        .Cells(iRow, 5) = frmWeeklyForm.cmbFails.Value
    
    End With

 

[ryeire]

@Joe4 That fixed it! Thank you so much for your patience and help. I owe you a drink