CODEWITHSUNDEEP
excelvbaemailoutlook
Damian
Damian

Reputation: 5174

Keep carriage returns on pasting string from Excel cell to Outlook mail

I want to paste a single cell to Outlook keeping the carriage returns. I can't use RangeToHTML because it pastes as a table and I need it to be plain text so I've tried this:

For Each C In wsEnv.Range("A1:A17")
    If C.Row = 1 Then
        wsEnv.Cells(19, 1) = C & vbCrLf
    ElseIf C.Row < 17 Then
        wsEnv.Cells(19, 1) = wsEnv.Cells(19, 1) & C & vbCrLf
    Else
        wsEnv.Cells(19, 1) = wsEnv.Cells(19, 1) & C
    End If
Next C

On Error Resume Next

With OutMail
    .Display
    .To = Para
    .CC = CC
    .BCC = ""
    .Subject = Asunto
    .HTMLBody = wsEnv.Cells(19, 1)
    .Send
End With
On Error GoTo 0

The result is this:
Result

Is there any way to keep the format on the cell or use the RangeToHTML function to paste as text and not a table?

Upvotes: 2

Views: 513

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You are using a HTML mail .HTMLBody and carriage returns have no effect in HTML code. Actually .HTMLBody is waiting for HTML code not for plain text.

  • Either use a "text only" mail:

    Set the MailItem.BodyFormat property to olFormatPlain (see OlBodyFormat enumeration) and use .Body instead of .HTMLBody

    .BodyFormat = olFormatPlain
.Body = wsEnv.Cells(19, 1).Value

  • or replace all vbCrLf with the HTML code for a linebreak <br>. 

    .HTMLBody = Replace$(wsEnv.Cells(19, 1).Value, vbCrLf, "<br>")

Note that the linebreaks you enter in cells using Alt+Enter are vbLf not vbCrLf!

Upvotes: 3

Related Questions