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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
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: 7

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
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: 7

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
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,295
Office Version
  1. 2013
Platform
  1. Windows
Glad it's sorted and thanks for letting me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,352
Messages
5,595,655
Members
414,006
Latest member
Davefromlondon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top