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

[Bwolosz]

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

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

 

[DanteAmor]

Try

.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.

 

[Bwolosz]

Try

.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

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Rick Rothstein]

Try

.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)

 

[DanteAmor]

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.

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

00000hhhhh!!!


Once again, thanks Rick.