Line break on "Comma" in email body from a single cell

[kitsa]

Hi Guys,
I have a cell [K14] which has "Hi Peter Homes, As attached is the quote you have requested". When I VBA to email body, it doesn't line break, want it to look like below:

Hi Peter Homes,
As attached is the quote you have requested

I have code:

Private Sub CommandButton1_Click()
'Sub SendExcelFilesAsPDFtoEmail()
 
   Dim OutApp As Object
   Dim OutMail As Object
   Dim Strbody As String, Fname As String
   Dim x As Date
       x = Format(Now() + 1, "MMMM dd, yyyy")
 
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    Fname = "EQ" & Range("F19") & ".pdf"

Strbody = Range("K14")

On Error Resume Next

' Create the PDF attachment
  ActiveSheet.ExportAsFixedFormat Type:=x1TypePDF, Filename:=Fname

With OutMail
        .Display
        .To = Range("K11").Value
        .Subject = Range("K13") & " " & x
        .HTMLBody =.HTMLBody & "<br/><br/>" & Replace(Strbody, vbCr, "<br/>")
        .Attachments.Add Fname
        .Importance = 2 'Or olImprotanceHigh Or olImprotanceLow
        .Display
    End With
   
  On Error GoTo 0
   Set OutMail = Nothing
   Set OutApp = Nothing
   Set OutLookMess = Nothing
   Set OutLookNSpace = Nothing
 
End Sub

 

[GWteB]

Try replacing this ...

.HTMLBody -.HTMLBody & "<br/><br/>" & Replace(Strbody, vbCr, "<br/>")

with this ...

.HTMLBody -.HTMLBody & "<br/><br/>" & Replace(Strbody, ", ", "," & vbNewLine) & "<br/>"

 

[eduzs]

sorry

 

[kitsa]

Hi Rich,
Some how this didn't work, but I noticed my "Strbody" showed up after signature. I have now change code as below, but still no line break.

.HTMLBody = Replace(Strbody, ", ", "," & vbNewLine) & "<br/>" & "" & .HTMLBody

 

[kitsa]

sorry

Hi Eduz,
Sorry but your code didn't work either. I'm not sure how to fix this.

 

[GWteB]

Sorry, HTML is all about text which I overlooked
Try

.HTMLBody = Replace(Strbody, ", ", ",<br/>") & "<br/>" & "" & .HTMLBody

 

[kitsa]

Sorry, HTML is all about text which I overlooked
Try

.HTMLBody = Replace(Strbody, ", ", ",<br/>") & "<br/>" & "" & .HTMLBody

Hi Gwteb,
Worked perfecto!! but the text is now small, how do I fix that?

 

[GWteB]

To be honest I have no idea. I should Google, but you can do that yourself.
In cases where I need it, I use Word as an editor, so I'm able to manipulate everything much easier, insert Excel tables and images, and so on.

 

[kitsa]

Thanks Anyway. I worked it out

.HTMLBody = "<p style='font-family:calibri;font-size:14.5'>" & Replace(Strbody, ", ", ",<br/>") & "<br/>" & "" & "</p>" & .HTMLBody

 

[GWteB]

Glad it's sorted and thanks for letting me know.