CODEWITHSUNDEEP
vbams-accessoutlook
Tim.McNamara
Tim.McNamara

Reputation: 35

.Body text when creating Outlook email is double-spacing no matter the new line command used

I'm creating an Outlook e-mail through VBA that runs off an Access form button click. For the .Body, I'm setting my 'strBody' string object using the following concatenated string: 

strBody = "First Line of text here." & vbNewLine & _
    vbNewLine & _
    "Second Line of text here." & vbNewLine & _
    Chr(187) & "Third Line of text here." & vbNewLine & vbTab & _
    Chr(187) & "Fourth Line of text here." & vbNewLine & vbTab & _
    Chr(187) & "Fifth Line of text here." & vbNewLine & vbTab & vbTab & _
    Chr(187) & "Sixth Line of text here." & vbNewLine & vbTab & _
    Chr(187) & "Seventh Line of text here." & vbNewLine & _
    "Eighth Line of text here"

The resulting text in the e-mail body should be:

First Line of text here

Second Line of text here.
Third Line of text here.
Fourth Line of text here.
Fifth Line of text here
Sixth Line of text here.
Seventh Line of text here.
Eighth Line of text here.

And when debug.print(ing), it looks perfect in the immediate window. However, when it is created in the Outlook window, it double-spaces every line so you get this:

First Line of text here

Second Line of text here.

Third Line of text here.

Fourth Line of text here.

Fifth Line of text here

Sixth Line of text here.

Seventh Line of text here.

Eighth Line of text here.

I've tried every combination of new line commands (vbcrlf, vbcr, vblf, vbnewline, etc) and every one results in the same outcome of double-spacing once I get how I need it in the vba string. I've also tried doing this concatenation right in the .Body and as a string object shown here...same result for both approaches.

Any idea(s) on how to prevent this double-spacing from occurring? Or, if not, is there way to manipulate the line spacing to 0pt in the e-mail body when creating?

Thanks in advance for any assistance.

Upvotes: 2

Views: 10944

Answers (1)

Erikas
Erikas

Reputation: 1086

I've recently sorted it out (I had different scenario, but the solution would be the same. I will try to explain why it happens:

You are creating HTML body of you mail. Each line is presented with a following format (or at least - similar):

<p><span style="color: #1f497d;">THISISSOMETEXT</span></p>

But you know what? 'p' stands for Paragraph, and by default, MS Office splits paragraphs with a large break (new line). To avoid this, use following format when creating HTML body for your email:

<span style="color: #1f497d;">THISISSOMETEXT</span>

And use <BR> for new line. Example HTML body:

<span style="color: #1f497d;">Hello,</span>
<BR><BR>
<span style="color: #1f497d;">REPLACEMEWITHGENERATEDSENTENCE</span>
<BR><BR>
<span style="color: #1f497d;">PLEASELETMEKNOWIFYOUHAVEANYISSUES</span>
<BR><BR>
<span style="color: #1f497d;">Regards,</span><BR><BR>

But wait, there is more! You won't have signature added while using BodyHTML property instead of Body. Create new function:

Function getSignature() As String
    signature = Environ("appdata") & "\Microsoft\Signatures\"
    If Dir(signature, vbDirectory) <> vbNullString Then
        signature = signature & Dir$(signature & "*.htm")
    Else:
        signature = ""
    End If
    getSignature = CreateObject("Scripting.FileSystemObject").GetFile(signature).OpenAsTextStream(1, -2).ReadAll
End Function

And at the end, use something like:

htmlBodyCode = "<span>This is HTML body</span>"
outMail.HTMLBody = "<p>" & htmlBodyCode & "</p>" & getSignature()

Hope this helps! I am currently using this set up to generate massive amount of emails from Excel in the company I am currently working.

Upvotes: 4

Related Questions