'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 |
---|