Excel Macro to send Outlook Email

[bthumble]

Hello,

I have created a macro to send email from my excel spreadsheet. But need help with adding more than one line for the body. Below is my code, but I need the macro to copy the body from C7-C13 (total of seven lines). Have attempted to do a range such as C7-C13 or C7,C13 but no success. Thanks for your help.

Sub send_email()

Dim OutApp As Object
Dim Outmail As Object
Set OutApp = CreateObject("Outlook.Application")
Set Outmail = OutApp.CreateItem(0)

With Outmail
.To = Range("C3").Value
.Subject = Range("C5").Value
.Body = Range("C7").Value

.display
End With

Set Outmail = Nothing
Set OutApp = Nothing

End Sub

 

[jkpieterse]

Note: Only works if you have Office 365

.Body = Application.WorksheetFunction.TextJoin(vbNewLine, True, Range("C17:C13").Value)

 

[bthumble]

Thanks, it works!

 

[Joe4]

Thank you for the reply. But when I did this, it only brings in the very last line of the range. Not sure if I am doing something incorrectly.

It looks like there was a typo in Jan's response.

This part:

Range("C17:C13")

should have been:

Range("C7:C13")

 

[BigDawg15]

change the Range portion to:

Range("C7:C13").Value)

 

[Joe4]

change the Range portion to:

Range("C13:C17").Value)

I think they wanted C7:C13, as per original request:

Have attempted to do a range such as C7-C13

(see my previous post)

 

[jkpieterse]

Apologies for the typo!

 

[bthumble]

Thanks again for everyone's help.