Retriving cell value to include in email body

[Sha]

Hi there,

I am new at VB codes...hope someone could help me.

I am trying to retrieve a cell value to include in my email body. Below is my code thus far...hope you could assist.

 

[Sha]

'Update by Extendoffice 2018/3/7
'code to check if cell range in col R is more than 0 then trigger email

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("R3 :R3000"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 0 Then
Call Mail_small_Text_Outlook
End If
End Sub

'code to launch email app (outlook)
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)
Set theBody = Range("C" & C.Row)



'Code for email content

xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"You have a query" & vbNewLine & _
"Case Number: & vbNewLine & sheets("").range("").value &" _


'Code to auto fill addressess's email address
On Error Resume Next
With xOutMail
.To = "shariffa.nazirah.syed.h@nhcs.com.sg"
.CC = ""
.BCC = ""
.Subject = "Pending query processing"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

End Sub