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