How do I insert a linebreak when sending an email using VBA Access

Question

Using the object "Outlook.Application", I send an e-mail using VBA Access. In the body, I put a string like this:

Email = "Random things" & Chr(13) _
& "More random things" & Chr(13) _

If I show the string Email in a MsgBox it is displayed correctly, but when I send it, the linebreaks are deleted.

I've tried with:

Chr(13)
vbCrLf
vbCr

But all three have the same result:

enter image description here

If you combine `Chr(13)` and `Chr(10)` (don't remember for sure the order, but I think it is this one), that should do the trick! ;) — R3uK, Jun 11 '15 at 08:10
it doesn't work :( I tried: Chr(10) & Chr(13) and Chr(13) & Chr(10) but nothing changed — Nimrrod, Jun 11 '15 at 08:24
Ok... So there is still : `vbNewLine`, `vbVerticalTab` ,`vbLf`, `vbFormFeed` and maybe `vbBack` — R3uK, Jun 11 '15 at 08:31
can you share your wider code? chr(13) works for me when creating emails... — tea_pea, Jun 11 '15 at 08:54
`vbCrLf` has always worked for me, _however_ if you’re sending a Plain Text e-mail you cannot guarantee how the recipients e-mail client will render the message. Often they try to ‘tidy’ up messages that they think contain unnecessary line breaks. With your example, they aren’t properly formed sentences so the client may believe they are part of the same sentence hence ‘joining’ them together. If you need control over the layout you need to look at using `.HTMLBody` — andshrew, Jun 11 '15 at 09:08
What @andshrew said. I would advise you to not use plain text e-mail and use HTML. That way you'll have better control over the body formatting and you'll be able to use the `
` tag, for example. — Marek Stejskal, Jun 11 '15 at 09:21
I can confirm what andshrew said, have been using vbCrLf in my ticket system for close to a year now. I also agree with with MarekStejskal regarding using HTML, so long as the email is client facing. If it is for personal use than obviously plain text would likely be sufficient. — Newd, Jun 11 '15 at 12:16
I had exactly the same issue and tried all those options with `.htmlbody` without success. I then changed it to `.Body` and it works — Seb, Jan 30 '17 at 15:26

J.Hartslief · Answer 1 · 2015-06-11T11:34:06.463

Try This:

Sub OutlookEmail()
Dim AppOutlook As Outlook.Application
    Set AppOutlook = CreateObject("Outlook.application")

Dim Mail As MailItem
    Set Mail = AppOutlook.CreateItem(olMailItem)

Dim Email As String
    Email  = "Random things" & vbNewLine _
             & "More random things" & vbNewLine

'Generate Email

Mail.Subject = "Test Subject"
Mail.To = "Test@test.com"

Mail.Body = Email

Mail.Display

Set Mail = Nothing
Set AppOutlook = Nothing

End Sub

Tested it my self appears to work correctly on my PC.

score 1 · Answer 2 · answered Nov 07 '19 at 15:31

The code below display the email in Outlook. To send, change .Display to .Send

Sub SendDisplayEmail(strEmailFrom As String, strEmailTo As String, strEmailCC As String, strEmailBCC As String, strSubject As String)
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0) ' olMailItem

    Debug.Print ("From: " & strEmailFrom & ", To: " & strEmailTo & ", cc: " & strEmailCC & ", bcc: " & strEmailBCC & ", file: " & xFile)

    On Error Resume Next

    OutMail
    With OutMail
        .to = strEmailTo
        .CC = strEmailCC
        .BCC = strEmailBCC
        .Subject = strSubject
        '.Body = "Random things" _
        '    & vbCrLf & vbCrLf & "More random things." _
        .BodyFormat = 2 ' olFormatHTML
        .HTMLBody = "<html>Random things<br>More random things.</html>"
        '.Body = strBody
        '.Save
        .Display
        '.Send   'or use .Display
    End With

    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

You can use the HTMLBody (with .BodyFormat = 2) for a nice formated e-mail or .Body for the plain text e-mail. Note that %0D%0A and dont work in HTMLBody because Outlook parse it.

How do I insert a linebreak when sending an email using VBA Access

2 Answers2