Excel VBA Chr(13), vbNewLine, & vbCrLf Not Working

Bwolosz

New Member
Joined
Jul 10, 2019
Messages
6
Hi all,

I am writing a script that will run once a day through windows task manager, then send out an automatic e-mail alert with the returned data.
I have set variables with the message body, but the vbNewLine, vbCrLf is not working. The message is received with all the text on a single line.

Any ideas on how to over come this issue?
Below is a copy of the code.

Any help is greatly appreciated

Code:
Sub Mail_Follow_Up_Lines_Outlook()
    Dim rng As Range
    Dim rng2 As Range
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBodyLn1 = "Hi there"
    XMailBodyLn2 = "Below is a list of Consignment Inventory Orders Requiring Review"
    XMailBodyLn3 = "Please Respond On Past Due Items ASAP."
    On Error Resume Next
    
        Set rng = Nothing
    On Error Resume Next
    'Only the visible cells in the selection
    'Set rng = Selection.SpecialCells(xlCellTypeVisible)
    'You can also use a fixed range if you want
    Set rng = Sheets("Follow Up Consignment Lines").Range("A1:K99").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
            Set rng2 = Nothing
    On Error Resume Next
    'Only the visible cells in the selection
    'Set rng = Selection.SpecialCells(xlCellTypeVisible)
    'You can also use a fixed range if you want
    Set rng2 = Sheets("Past Due Consignment Lines").Range("A1:K99").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    With xOutMail
        .To = "E-Mail Address Here"
        .CC = ""
        .BCC = ""
        .Subject = "E_MAIL TEST"
        .HTMLBody = xMailBodyLn1 & vbCrLf & vbCrLf & XMailBodyLn2 & vbCrLf & XMailBodyLn3 & vbCrLf & RangetoHTML(rng) & vbCrLf & RangetoHTML2(rng2)
        .Send   'or use .Send .Display
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Thank You,

Bradley
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try

Code:
.HTMLBody = xMailBodyLn1 & " < br > < br > " & xMailBodyLn2 & " < br > " & xMailBodyLn3 & " < br > " & RangetoHTML(Rng) & " < br > " & RangetoHTML2(rng2)


Remove the blanks of:


" < br > "

The forum editor does not allow me to put it together, so you must remove the spaces.
 
Last edited:
Upvote 0
Try

Code:
.HTMLBody = xMailBodyLn1 & " < br > < br > " & xMailBodyLn2 & " < br > " & xMailBodyLn3 & " < br > " & RangetoHTML(Rng) & " < br > " & RangetoHTML2(rng2)


Remove the blanks of:


" < br > "

The forum editor does not allow me to put it together, so you must remove the spaces.


Dante,

Your solution worked great!!!
Thanks for the quick reply
 
Upvote 0
Try

Code:
.HTMLBody = xMailBodyLn1 & " < br > < br > " & xMailBodyLn2 & " < br > " & xMailBodyLn3 & " < br > " & RangetoHTML(Rng) & " < br > " & RangetoHTML2(rng2)

Remove the blanks of:

" < br > "

The forum editor does not allow me to put it together, so you must remove the spaces.

You can make the forum editor ignore the HTML tags by clicking the "Go Advanced" button, scroll down to the panel titled "Additional Options" and change the HTML dropdown to "HTML Off", then submit the message. Here is your code line as you intended to be displayed...

.HTMLBody = xMailBodyLn1 & "<br><br>" & xMailBodyLn2 & "<br>" & xMailBodyLn3 & "<br>" & RangetoHTML(Rng) & "<br>" & RangetoHTML2(rng2)
 
Last edited:
Upvote 0
You can make the forum editor ignore the HTML tags by clicking the "Go Advanced" button, scroll down to the panel titled "Additional Options" and change the HTML dropdown to "HTML Off", then submit the message.


Code:
.HTMLBody = xMailBodyLn1 & "<br><br>" & xMailBodyLn2 & "<br>" & xMailBodyLn3 & "<br>" & RangetoHTML(Rng) & "<br>" & RangetoHTML2(rng2)


00000hhhhh!!!:biggrin:


Once again, thanks Rick.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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
Back
Top