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

kitsa

Board Regular
Joined
Mar 4, 2016
Messages
66
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:

VBA 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
 
Last edited:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,246
Office Version
  1. 2013
Platform
  1. Windows
Try replacing this ...
Rich (BB code):
.HTMLBody -.HTMLBody & "<br/><br/>" & Replace(Strbody, vbCr, "<br/>")

with this ...
VBA Code:
.HTMLBody -.HTMLBody & "<br/><br/>" & Replace(Strbody, ", ", "," & vbNewLine) & "<br/>"
 

kitsa

Board Regular
Joined
Mar 4, 2016
Messages
66
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.

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

Attachments

  • Capture.JPG
    Capture.JPG
    47.7 KB · Views: 3

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,246
Office Version
  1. 2013
Platform
  1. Windows
Sorry, HTML is all about text which I overlooked :cry:
Try
VBA Code:
.HTMLBody = Replace(Strbody, ", ", ",<br/>") & "<br/>" & "" & .HTMLBody
 
Solution

kitsa

Board Regular
Joined
Mar 4, 2016
Messages
66

ADVERTISEMENT

Sorry, HTML is all about text which I overlooked :cry:
Try
VBA Code:
.HTMLBody = Replace(Strbody, ", ", ",<br/>") & "<br/>" & "" & .HTMLBody
Hi Gwteb,
Worked perfecto!! but the text is now small, how do I fix that?
 

Attachments

  • Capture.JPG
    Capture.JPG
    46.3 KB · Views: 3

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,246
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Mar 4, 2016
Messages
66
Thanks Anyway. I worked it out

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

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,246
Office Version
  1. 2013
Platform
  1. Windows
Glad it's sorted and thanks for letting me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,140
Messages
5,570,405
Members
412,322
Latest member
mohi021
Top