Vba Excel To Outlook Font Formatting

I am currently working on a process automation for my work. Basically, the macro analyses the data and creates an email with results. Everything works perfectly fine, besides the

Solution 1:

If your goal is to functionally be able to "copy-paste" some data from Excel into an Outlook e-mail while retaining all the formatting, it would probably be easiest to just use Ron de Bruin's function that converts an Excel range into HTML. I checked and this seems like it even retains formatting from conditional formatting.

Source website.

Then you can just get your data converted to HTML to put in the body tag like

html = html & RangeToHTML(OutSh.Range("a4:a11"))

(Repost of the function in question)

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006' Working in Office 2000-2016Dim fso AsObjectDim ts AsObjectDim TempFile AsStringDim 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 = FalseOnErrorResumeNext
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        OnErrorGoTo0EndWith'Publish the sheet to a htm fileWith TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    EndWith'Read all data from the htm file into RangetoHTMLSet 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=")

    'Close TempWB
    TempWB.Close savechanges:=False'Delete the htm file we used in this function
    Kill TempFile

    Set ts = NothingSet fso = NothingSet TempWB = NothingEndFunction

Solution 2:

Wedge is correct that Ron de Bruin’s RangeToHtml is your first choice. However, this routine uses Excel Publish Objects which produce poor quality CCS with column widths set in pixels. At least some smartphones cannot process this CSS at all and others produce displays that are difficult to read. If none of your recipients will open your emails with a smartphone then Wedge’s solution will be adequate for your needs.

A second solution is a RangeToHtml routine that I wrote in VBA. This following extract from its introduction lists its handling of formatting:

' The table below lists the formats handled at cell and or in-cell level.'   CELL-LEVEL            IN-CELL           DEFAULT VALUE'   bold                  bold              false'   italic                italic            false'   strikethrough         strikethrough     false'   underline single      underline single  no underline'   underline double      underline double  no underline'   underline accounting                    no underline'   font colour           font colour       black'   background colour                       white'   horizontal alignment                    left for string; right for numbers and dates'   font size             font size         11 or as set at the Excel application level'   font name             font name         Calibri or as set at the Excel application level'   vertical alignment                      bottom

The trouble with my routine is (1) it is not particularly fast and (2) its total size is way over Stack overflow’s limit of 30,000 characters per answer. If you need anything like this level of functionality, email me and I will send you my development workbook.

If all you need is bold and a few colours, you can code your own routine using my approach.

My html head element is something like this:

<head><metahttp-equiv="Content-Type"content="text/html; charset=utf-8"/><style>table {border-collapse:collapse;}
      td {border-style:solid; border-width:1px; border-color:#BFBFBF;}
      td.backclr-0000FF {background-color:#0000FF;}
      td.backclr-D7EAF4 {background-color:#D7EAF4;}
      td.backclr-F3F5DB {background-color:#F3F5DB;}
      td.bold {font-weight:bold;}
      td.fontclr-0000FF {color:#0000FF;}
      td.fontclr-0070C0 {color:#0070C0;}
      td.fontclr-00FF00 {color:#00FF00;}
      td.hAlign-right {text-align:right;}
        </style><head>

My routine generates just the styles it needs to handle the formats it finds in the Excel range. If you only need bold and a few colours, you could hardcode your own style element with shorter class names than I need.

The output for a row is then something like:

<tr><tdclass="td.backclr-0000FF ">Column1</td><tdclass="hAlign-right td.fontclr-0000FF ">100,000</td><tdclass="hAlign-right td.bold ">200,000</td></tr>

If either of the second and third approaches looks interesting but is unclear, post a comment and I will expand my explanations.