CODEWITHSUNDEEP
excelvbaoutlook
10101
10101

Reputation: 2402

Copy text from Excel to Outlook message as it is

I am trying to create an automated Outlook message from Excel. The problem is that with my current code text in Outlook message is not formatted like it was originally typed in Excel (see screenshots below). Is there any way to make it at least change lines (keep line breaks)?

So if in Excel I type:

This is text
and it continues
on the next line.

One line break in between also.

It will appear the same in Outlook message? Not one liner as it is now...

With olMail
    .To = "[email protected]"
    .Subject = olSubject
    .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>" & _
    "MESSAGE" & "<br>" & _
    "Number:" & ThisWorkbook.Worksheets("Other Data").Range("P14").Value & "-" & ThisWorkbook.Worksheets("Other Data").Range("I140").Value & "<br>" & _
    "Include attachments." & "<br>" & _
    "Total:" & ThisWorkbook.Worksheets("Other Data").Range("P38").Value & "<br>" & _
    "Cover notes:" & ThisWorkbook.Worksheets("MAIN").Range("B90").Value & "<br>" & _
    "§§§" & _
    "</BODY>" '& .HTMLBody

I have data in Excel in this format:

enter image description here

Then it appears to Outlook like this:

enter image description here

Upvotes: 0

Views: 40

Answers (1)

Domenic
Domenic

Reputation: 8104

Try replacing the line feeds with its HTML equivalent. So, for example, replace vbLf with <br>...

"Cover notes:" & Replace(ThisWorkbook.Worksheets("MAIN").Range("B90").Value, vbLf, "<br>") & "<br>" & _

Upvotes: 1

Related Questions