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 fact I have absolutely no clue how to implement my font formatting into Outlook's email body. I cannot set font formatting to constant value as each row's formatting varies and is depended on excel formulas and conditional formattings in my worksheet.

The function which creates the HTML body looks as below:

            Private Function BodyBuilder(wsA As Worksheet) As String
            Dim html As String
            Dim sTTo As String
            Dim rngSN As Range
            Dim cl As Range
            Dim sName As String


            Set OutSh = ThisWorkbook.Sheets("Output")

               sTTo = Comm.Cells(5, 4)

               sName = OutSh.Cells(3, 2)

               html = "Dear " & sTTo & ","

            Set rngSN = OutSh.Range("a4:a11")

            html = html & "<br><br><table><tr><td><strong>" & "Detailed risk report for selected company - " & sName & "</strong></td></tr>"

            **For Each cl In rngSN
               html = html & "<tr><td>" & Format(cl.Value, cl.NumberFormat) & "</td><td>" & cl.Offset(, 1).Value & "</td></tr>"
            Next cl**


            html = html & "</table>" & "<br>" & "Kind regards"

            BodyBuilder = html
            End Function

Basically, the email looks perfectly fine besides that I would like to see my values with it's font formats (bolded if needed and colors).

Can someone help with this?

Thank you

标签： html excel vba excel-vba outlook-vba

2条回答

The star\"

2楼-- · 2019-08-10 10:42

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>
    <meta http-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>
    <td class="td.backclr-0000FF ">Column1</td>
    <td class="hAlign-right td.fontclr-0000FF ">100,000</td>
    <td class="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.

0人赞添加讨论(0) 举报

仙女界的扛把子

3楼-- · 2019-08-10 10:52

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-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=")

    '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

0人赞添加讨论(0) 举报

VBA Excel to Outlook font formatting

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间