CODEWITHSUNDEEP
vbaexcelemail
Mariusz Adamczewski
Mariusz Adamczewski

Reputation: 107

New line in email by creating message from a cell value with line breaks: VBA

I am facing with the issue related to mail in VBA.

For Example. In cell A1 I have: 

Test test test

test


test test

And when I am generating the mail from a cell A1, the string appears without the line breaks, I mean 

Test test test test test test.

I tried to use Replace from "\r\n" to "<br/>" but that does not work.

Content = Replace(Content , "\r\n", "<br/>")

Can anybody help me resolve this?

Upvotes: 2

Views: 3098

Answers (1)

Wolfie
Wolfie

Reputation: 30047

The key is replacing Excel's newline character Chr(10) with the HTML line break <br>

Email code:

Sub mail()
    Dim OutApp As Object, OutMail As Object, strbody As String

    ' Get string from cell, replace Chr(10) (the cell new line) with a html <br>
    strbody = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
    strbody = Replace(strbody, Chr(10), "<br>")

    ' Create email and add string as HTMLBODY item
    On Error Resume Next
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .to = ""
        .CC = ""
        .Subject = "Subject here"
        .htmlbody = strbody
        .Display
    End With
    On Error GoTo 0
End Sub

Ouput:

test

Upvotes: 4

Related Questions