Removing 1 line of space in VBA Email Body

Question

My code has an extra line break in the email before my signature and after strbody3. I have the code near perfect, except for deleting this extra line.
I am using Ron de Bruin's RangetoHTML function as my starting point
I have tried this fix but it's not working for me: https://forum.ozgrid.com/forum/index.php?thread/120701-remove-space-above-rangetohtml-in-email-body/

Code is returning:

What I want to return:

Sub Code

Sub Mail_Selection_Range_Outlook_Body_v2()
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim signature As String
    Dim strbody1 As String
    Dim strbody2 As String
    Dim strbody3 As String
    
    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("Calculator").Range("M7:R14").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

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

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

    With OutMail
    .Display
    End With
    
    signature = OutMail.HTMLBody
    
    strbody1 = "<p style=font-size:11pt;font-family:Calibri;mso-margin-top-alt:0.0pt;margin-bottom:0.0pt;'>Hi team,<br><br>See below for the expected moves."
    strbody2 = "<ul><li><p style=font-size:11pt;font-family:Calibri;mso-margin-top-alt:0.0pt;margin-bottom:0.0pt;'>No new items today</li></ul><br>"
    strbody3 = "<p style=font-size:11pt;font-family:Calibri;mso-margin-top-alt:0.0pt;margin-bottom:0.0pt;'><br>Thanks,<delete>"
    
    On Error Resume Next
      
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Expected Move | " & Worksheets("Calculator").Range("F2")
        .HTMLBody = strbody1 & strbody2 & RangetoHTML(rng) & strbody3 & signature
        .Display
    End With
    On Error GoTo 0

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

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Function Code

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
    RangetoHTML = Replace(RangetoHTML, "<!--[if !excel]>&nbsp;&nbsp;<![endif]-->", "")
    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

Update: I think it has something to do with this section?

I inserted this

Debug.Print strbody3 & signature

and the immediate window returned a lot but this is the section that's right before the name in my signature. I messed around with trying to replace certain strings but no luck. If anyone has suggestions would be greatly appreciated!

<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 6"/>
<w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful Accent 6"/>
<w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful Accent 6"/>
<w:LsdException Locked="false" Priority="46" Name="List Table 1 Light"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark"/>
<w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful"/>
<w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful"/>
<w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 1"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 1"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 1"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 1"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 1"/>
<w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 1"/>
<w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 1"/>
<w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 2"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 2"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 2"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 2"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 2"/>
<w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 2"/>
<w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 2"/>
<w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 3"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 3"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 3"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 3"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 3"/>
<w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 3"/>
<w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 3"/>
<w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 4"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 4"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 4"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 4"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 4"/>
<w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 4"/>
<w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 4"/>
<w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 5"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 5"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 5"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 5"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 5"/>
<w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 5"/>
<w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 5"/>
<w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 6"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 6"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 6"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 6"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 6"/>
<w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 6"/>
<w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 6"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Mention"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Smart Hyperlink"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Hashtag"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Unresolved Mention"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Smart Link"/>
</w:LatentStyles>
</xml><![endif]--><style><!--
/* Font Definitions */
@font-face
    {font-family:"Cambria Math";
    panose-1:2 4 5 3 5 4 6 3 2 4;
    mso-font-charset:0;
    mso-generic-font-family:roman;
    mso-font-pitch:variable;
    mso-font-signature:-536869121 1107305727 33554432 0 415 0;}
@font-face
    {font-family:Calibri;
    panose-1:2 15 5 2 2 2 4 3 2 4;
    mso-font-charset:0;
    mso-generic-font-family:swiss;
    mso-font-pitch:variable;
    mso-font-signature:-469750017 -1040178053 9 0 511 0;}
@font-face
    {font-family:Aptos;
    mso-font-charset:0;
    mso-generic-font-family:swiss;
    mso-font-pitch:variable;
    mso-font-signature:536871559 3 0 0 415 0;}
@font-face
    {font-family:Tahoma;
    panose-1:2 11 6 4 3 5 4 4 2 4;
    mso-font-charset:0;
    mso-generic-font-family:swiss;
    mso-font-pitch:variable;
    mso-font-signature:-520081665 -1073717157 41 0 66047 0;}
@font-face
    {font-family:"Gotham Book";
    panose-1:0 0 0 0 0 0 0 0 0 0;
    mso-font-charset:0;
    mso-generic-font-family:modern;
    mso-font-format:other;
    mso-font-pitch:variable;
    mso-font-signature:-1610611969 1073741915 0 0 159 0;}
@font-face
    {font-family:"MS PGothic";
    panose-1:2 11 6 0 7 2 5 8 2 4;
    mso-font-charset:128;
    mso-generic-font-family:swiss;
    mso-font-pitch:variable;
    mso-font-signature:-536870145 1791491579 134217746 0 131231 0;}
@font-face
    {font-family:"\@MS PGothic";
    mso-font-charset:128;
    mso-generic-font-family:swiss;
    mso-font-pitch:variable;
    mso-font-signature:-536870145 1791491579 134217746 0 131231 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
    {mso-style-unhide:no;
    mso-style-qformat:yes;
    mso-style-parent:"";
    margin:0in;
    mso-pagination:widow-orphan;
    font-size:12.0pt;
    font-family:"Aptos",sans-serif;
    mso-ascii-font-family:Aptos;
    mso-ascii-theme-font:minor-latin;
    mso-fareast-font-family:Aptos;
    mso-fareast-theme-font:minor-latin;
    mso-hansi-font-family:Aptos;
    mso-hansi-theme-font:minor-latin;
    mso-bidi-font-family:"Times New Roman";
    mso-bidi-theme-font:minor-bidi;
    mso-font-kerning:1.0pt;
    mso-ligatures:standardcontextual;}
a:link, span.MsoHyperlink
    {mso-style-noshow:yes;
    mso-style-priority:99;
    color:#467886;
    mso-themecolor:hyperlink;
    text-decoration:underline;
    text-underline:single;}
a:visited, span.MsoHyperlinkFollowed
    {mso-style-noshow:yes;
    mso-style-priority:99;
    color:#96607D;
    mso-themecolor:followedhyperlink;
    text-decoration:underline;
    text-underline:single;}
span.EmailStyle17
    {mso-style-type:personal-compose;
    mso-style-noshow:yes;
    mso-style-unhide:no;
    mso-ansi-font-size:11.0pt;
    mso-bidi-font-size:12.0pt;
    font-family:"Calibri",sans-serif;
    mso-ascii-font-family:Calibri;
    mso-hansi-font-family:Calibri;
    mso-bidi-font-family:"Times New Roman";
    mso-bidi-theme-font:minor-bidi;
    color:windowtext;}
.MsoChpDefault
    {mso-style-type:export-only;
    mso-default-props:yes;
    mso-ascii-font-family:Aptos;
    mso-ascii-theme-font:minor-latin;
    mso-fareast-font-family:Aptos;
    mso-fareast-theme-font:minor-latin;
    mso-hansi-font-family:Aptos;
    mso-hansi-theme-font:minor-latin;
    mso-bidi-font-family:"Times New Roman";
    mso-bidi-theme-font:minor-bidi;}
@page WordSection1
    {size:8.5in 11.0in;
    margin:1.0in 1.0in 1.0in 1.0in;
    mso-header-margin:.5in;
    mso-footer-margin:.5in;
    mso-paper-source:0;}
div.WordSection1
    {page:WordSection1;}
--></style><!--[if gte mso 10]><style>/* Style Definitions */
table.MsoNormalTable
    {mso-style-name:"Table Normal";
    mso-tstyle-rowband-size:0;
    mso-tstyle-colband-size:0;
    mso-style-noshow:yes;
    mso-style-priority:99;
    mso-style-parent:"";
    mso-padding-alt:0in 5.4pt 0in 5.4pt;
    mso-para-margin:0in;
    mso-pagination:widow-orphan;
    font-size:12.0pt;
    font-family:"Aptos",sans-serif;
    mso-ascii-font-family:Aptos;
    mso-ascii-theme-font:minor-latin;
    mso-hansi-font-family:Aptos;
    mso-hansi-theme-font:minor-latin;
    mso-font-kerning:1.0pt;
    mso-ligatures:standardcontextual;}
</style><![endif]--></head><body lang=EN-US link="#467886" vlink="#96607D" style='tab-interval:.5in;word-wrap:break-word'><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:"Calibri",sans-serif;mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi'><o:p>&nbsp;</o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:"Calibri",sans-serif;mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi'><o:p>&nbsp;</o:p></span></p><p class=MsoNormal><a name="_MailAutoSig"><b><span style='font-size:10.5pt;font-family:"Gotham Book";mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:minor-fareast;color:#203755;mso-fareast-language:JA;mso-no-proof:yes'>```

If you Debug.Print strbody3 & signature what does that look like? That would be a good place to start... — Tim Williams
– Tim Williams, Commented May 5 at 21:41
Thanks @Tim I tried that, updated my question above. I'm not an expert in HTML, messed around replacing some of the code but nothing got rid of that double space. If you have any suggestions, would greatly appreciate it. Thank you! — mr_nane
– mr_nane, Commented May 6 at 16:03
There's so much junk there - you'd likely be better off creating your signature in regular HTML and using that instead of relying on copying the HTML from a new message. — Tim Williams
– Tim Williams, Commented May 6 at 17:04

Avitzavi · Accepted Answer · 2025-06-04 20:13:04Z

0

This might be from the HTML interpreting a Line Feed and Carriage Return as 2 separate line breaks.

In the past I have solved this by doing a replace(your signature,char(10),"") char(10) is the line feed, I think char(13) is carriage return, you can try experimenting with different ones.

Another solution is to do left(your signature,len(your signature)-1) to literally strip out the final newline

answered Jun 4 at 20:13

Avitzavi

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Removing 1 line of space in VBA Email Body

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related