'How do I pass a cell value, for the Subject line, to code called to create mail?

I am gluing pieces together to automate sending emails with each new line added to an Excel sheet.

I want the subject line to contain the text of a cell in column E - E2, E3, etc. as I add new lines.

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Range("E2:E600"), Target)
    If xRg Is Nothing Then
        Exit Sub
    Else
        Call Mail_small_Text_Outlook
    End If
End Sub

Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hello sir," & vbNewLine & vbNewLine & _
              "New request has been added." & vbNewLine & vbNewLine & _
              "Thank you in advance and have a great day."
    On Error Resume Next
    With xOutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = **Range("E2")**
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source