Excel how to add in my email body the value of a certain cell.

[AerowindowsRobin]

Hello,

Below is my VBA code that when a number is above 5 in my "U" column it generates an email to my company. The email of my body currently says "Workflow in the CRF Tracker has passed 5 business days on PO#" and I want it to display the PO# that's in Column "A". Example, if "U5" > 5, an email body would display "Workflow in the CRF Tracker has passed 5 business days on PO#" & [A5].

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("U:U"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 5 Then
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)
strbody = "Workflow in the CRF Tracker has passed 5 business days on PO#" & ???
On Error Resume Next
With xOutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Workflow in CRF"
.HTMLBody = strbody
.Display
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[Logit]

strbody = "Workflow in the CRF Tracker has passed 5 business days on PO#" & Range("A5").Value

 

[AerowindowsRobin]

strbody = "Workflow in the CRF Tracker has passed 5 business days on PO#" & Range("A5").Value

Thanks for the response, but I don't want it for that specific cell. It was an example, if my cell in "U8" goes above 5 I want to generate the value from "A8". The next day is my cell in "U9" goes above 5 I want to generate the value from "A9" onto my email body and so on.

 

[Logit]

Post your workbook for download.

 

[AerowindowsRobin]

Sorry, how do I post my workbook?

 

[Logit]

This Forum does not provide that service. You'll need to post on a Cloud website and provide the link here.