VBA to add multiple cells in the body of an email

[Twinkievizzio11]

I am trying to add Cell E5 in the body of the email below E4. I am new using VBA. This is what I have so far.

Sub CreateEmail()
Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Range
Dim rngBody As Range
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With ActiveSheet
Set rngTo = .Range("E2")
Set rngSubject = .Range("E3")
Set rngBody = .Range("E4")
End With
With objMail
.To = rngTo.Value
.Subject = rngSubject.Value
.body = rngBody.Value
.Display 'Instead of .Display, you can use .Send to send the email _
or .Save to save a copy in the drafts folder
End With
Set objOutlook = Nothing
Set objMail = Nothing
Set rngTo = Nothing
Set rngSubject = Nothing
Set rngBody = Nothing
End Sub

 

[NdNoviceHlp]

U didn't ask a question or say what's not working? I suspect that U are using ranges for variables. Trial changing them to strings. Dave
ie.

Dim rngTo As String
 Dim rngSubject As String
 Dim rngBody As String
 Set objOutlook = CreateObject("Outlook.Application")
 Set objMail = objOutlook.CreateItem(0)
 With ActiveSheet
 rngTo = .Range("E2")
 rngSubject = .Range("E3")
 rngBody = .Range("E4")
 End With

 

[Twinkievizzio11]

My questions is How do I add Cell E5 in a sheet called “email template” underneath the body of the email which is cell E4?

 

[Twinkievizzio11]

Can anyone help me write the above code to add cell E5 in the body of the email?

 

[NdNoviceHlp]

In the body is different than below the body...

rngBody = .Range("E4") & " " &  .Range("E5")
'or in the body below  .Range("E4")
rngBody = .Range("E4") & VbCrlf &  .Range("E5")

HTH. Dave

 

[Twinkievizzio11]

I would like Cell E5 in the body below .Range ("E4")


I tried the code below and it's still not working

Sub CreateEmail()
Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Range
Dim rngBody As Range
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With ActiveSheet
Set rngTo = .Range("E2")
Set rngSubject = .Range("E3")
Set rngBody = .Range("E4") & vbCrLf & .Range("E5")
End With
With objMail
.To = rngTo.Value
.Subject = rngSubject.Value
.body = rngBody.Value
.Display 'Instead of .Display, you can use .Send to send the email _
or .Save to save a copy in the drafts folder
End With
Set objOutlook = Nothing
Set objMail = Nothing
Set rngTo = Nothing
Set rngSubject = Nothing
Set rngBody = Nothing
End Sub

 

[Twinkievizzio11]

How can I add cell E5 in the body of the email under E4?


I tried the suggestions but none have worked.

Thank you.

 

[NdNoviceHlp]

Maybe..

.Range("E4") & vbNewLine & .Range("E5")

You can also set it as a range and then enter it as Html. Dave