email body to reference a specific cell in sheet

[Ray707]

Hi guys,

I have a code that sends out the excel worksheet via a command button. I want to amend the code so that the text in the body of the email references a specific cell. In other words, I have a job number in cell A9 of my worksheet so I want the '.body' part of the code to reference it, along the lines of 'please find spreadsheet attached for A9'. How would I amend the bold part of the code below?

Any help appreciated :thumb


http://icons.iconarchive.com/icons/double-j-design/ravenna-3d/24/File-Copy-icon.png
private sub commandbutton1_click()
'update 20131209

dim wb1 as workbook, wb2 as workbook
dim sfilepath as string, sfilename as string
dim iformat as integer

with application
.screenupdating = false
.displayalerts = false
end with

set wb1 = application.activeworkbook
activesheet.copy
set wb2 = application.activeworkbook

sfilepath = environ$("temp")
sfilename = sfilepath & "\ " & wb1.name
iformat = wb1.fileformat
wb2.saveas sfilename, iformat
wb2.close

with createobject("outlook.application").createitem(0)
.to = "xxxxx"
.cc = ""
.bcc = ""
.subject = "excel sheet test"
.body = "Hello" & vbLf & vbLf & "Please find spreadsheet attached for A9." & vbLf & vbLf & "Regards" & vbLf & "Ray"
.attachments.add sfilename
.send
end with

kill sfilename

with application
.displayalerts = true
.screenupdating = true
end with

end sub

[Paul_Hossler]

Top of my head




.body = "Hello" & vbLf & vbLf & "Please find spreadsheet attached for " & Range("A9").Value & "." & vbLf & vbLf & "Regards" & vbLf & "Ray"

[Ray707]

Top of my head




.body = "Hello" & vbLf & vbLf & "Please find spreadsheet attached for " & Range("A9").Value & "." & vbLf & vbLf & "Regards" & vbLf & "Ray"


you sir, are a genius. Thank you!