CODEWITHSUNDEEP
vbaemailoutlookspacing
Richard H
Richard H

Reputation: 39135

VBA: How to set single line spacing in Outlook email?

I am creating an email from within Excel. Once the email's been created I need to add a comment or two at the top. I've figured out how to set the font style but Outlook is adding a double line space on Carriage Return which I really don't want. How can I change this?

Code below:

Sub CreateDailyEmail()

    Dim oApp As Object
    Dim oMail As Object

    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)

    With oMail
        .To = Range("EMAIL_TO")
        .Cc = Range("EMAIL_CC")
        .Subject = Range("EMAIL_SUBJECT")
        .Attachments.Add (Range("PATH"))
        .HTMLBody = "<p style=""font-family: Calibri; font-size: 14px; color: #00f; line-height: 1;""><br /></p>" & RangetoHTML(ActiveWorkbook.Worksheets("Daily").Range("B6:H65"))
        .Display
    End With

    Set oMail = Nothing
    Set oApp = Nothing

End Sub

Upvotes: 1

Views: 22271

Answers (2)

Steve Irwin
Steve Irwin

Reputation: 251

It looks like you are using the excellent Ron De Bruin's code for sending and email from Excel (hence the RangetoHTML() formula).

I have been using this same piece of code which can be found at http://www.rondebruin.nl/win/s1/outlook/mail.htm

Instead of using paragraph HTML tags in the .HTMLBody use body tags and set the line height to 1. Then when the RangetoHTML returns the range you want it will only be seperated from any text you have by a single space! This makes the code....

Sub CreateDailyEmail()

Dim oApp As Object
Dim oMail As Object

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)

With oMail
    .To = Range("EMAIL_TO")
    .Cc = Range("EMAIL_CC")
    .Subject = Range("EMAIL_SUBJECT")
    .Attachments.Add (Range("PATH"))
    .HTMLBody = "<body style=""font-family: Calibri; font-size: 14px; color: #00f; line-height: 1;""><br />" & RangetoHTML(ActiveWorkbook.Worksheets("Daily").Range("B6:H65")) & "</body" & .HTMLBody
    .Display
End With

Set oMail = Nothing
Set oApp = Nothing

End Sub

Upvotes: 2

Scott Conover
Scott Conover

Reputation: 1421

I am not entirely sure I understand what you are asking, as I am unsure if the carriage returns are occurring in the cells from the excel sheet or only in the html in outlook, but there are two approaches in my mind:

You can replace carriage returns and line breaks in a cell(http://stackoverflow.com/questions/2321078/how-can-i-remove-blank-line-breaks-from-an-excel-cell-with-vb-or-a-formula ) to deal with spacing issues, e.g. 

Substitute(Substitute(A1, CHAR(10), ""), CHAR(13), "")

If it is occuring in the html portion of your document, then it is an outlook-specific issue, as an html example using your settings works just fine:

<!DOCTYPE html>

<html>

<head>

<meta name="description" content="A Jack Orange 

Lantern Example" />

<style type="text/css">

p
{
font-family: "Calibri"; 
font-size: 20px;
color: #00f;
line-height: 1;
} 

</style>


</head>

<title> What's Up? </title>

<body>



<p> This is totally a paragraph </p>

<p> this is totally a paragraph <br /> + a line 

break </p>

<p> this is totally a paragraph with a line break 

afterwards <br /> </p>

<p> Totally... yeah. <br /> </p>

</body>

</html>

For outlook, you should be able to replace carriage returns using the Replace function:

stringNewText = Replace(stringOldText, vbCr, "")

Or, possibly: 

stringNewText = Replace(stringOldText, vbCr, <br>)

Or, alternatively: 

stringNewText = Replace(stringOldText, vbCr, vbCrLf)

The MSDN documentation directly addresses the replacement function in this Microsoft tutorial on working with item bodies: http://msdn.microsoft.com/en-us/library/office/dd492012(v=office.12).aspx

A tutorial at vbaexpress appears to at least peripherally address this issue, and may provide further clarification: http://www.vbaexpress.com/forum/showthread.php?t=39348

Upvotes: 0

Related Questions