vba 将表格从黑莓友好的 Excel 导出到 Outlook

export table to outlook from excel that is blackberry friendly

提问by macutan

So I gathered from different places on the internet some code to export a table into outlook and send it out automatically and have just noticed that the email is not really blackberry friendly (mainly due to the fact that the email body is sent as HTML and not as "Rich Text", REMEMBER the table needs to also be in rich format). I am missing a line of code that will tell outlook to send email as "Rich Text" but I do not know what it is. Can anyone help me? Please see full code attached below (apologies for the lenght of the code in advance).

所以我从互联网上的不同地方收集了一些代码，将表格导出到 Outlook 中并自动发送出去，刚刚注意到该电子邮件并不是真正的黑莓友好型（主要是因为电子邮件正文以 HTML 格式发送而不是作为“富文本”，请记住表格也需要采用富格式）。我缺少一行代码，它会告诉 Outlook 将电子邮件作为“富文本”发送，但我不知道它是什么。谁能帮我？请参阅下面附上的完整代码（提前为代码的长度道歉）。

Thanks P

谢谢P

Sub Mail_Sheet_Outlook_Body()
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set rng = Nothing
    Set rng = Selection.SpecialCells(xlCellTypeVisible)

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

    On Error Resume Next
    With OutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "Enter Subject text here"
        .HTMLBody = RangetoHTML(rng)
       '.Attachments.Add ("c:\temp\" & ActiveSheet.Range("DateSerial").Value & ".pdf")
        .Display
    End With
    On Error GoTo 0

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

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Function RangetoHTML(rng As Range)
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
    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 workbook to receive the data.
    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 an .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 the RangetoHTML subroutine.
    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.
    Kill TempFile

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

采纳答案by Tony Dallimore

Background to answer

回答背景

The objective of the question was to be able to get an Excel table sent in an email that was readable on a Blackberry. There were a number of red herrings before the cause of the problem was identified as Excel's PublishObjects. The issue might have been the large number of unnecessary CSS formats or the sizing of cells and fonts in points but, whatever the issue, the HTML could not be rendered correctly by the Blackberry's display engine.

问题的目的是能够在电子邮件中发送一个 Excel 表格，该表格在黑莓上可读。在问题的原因被确定为 Excel 的 PublishObjects 之前，有一些不合理的地方。问题可能是大量不必要的 CSS 格式或单元格和字体的大小（以磅为单位），但无论出现什么问题，Blackberry 的显示引擎都无法正确呈现 HTML。

I created a fairly simple piece of VBA code to create an HTML table from a range. It copied values, bold, italic, font colour, background colour and column widths from Excel to the HTML table. Although the first version did not meet macutan's full requirements, it demonstrated that the approach was viable: an HTML/CSS table with a minimum of formatting was displayed correctly and attractively on a Blackberry.

我创建了一段相当简单的 VBA 代码来从一个范围创建一个 HTML 表。它将值、粗体、斜体、字体颜色、背景颜色和列宽从 Excel 复制到 HTML 表格。尽管第一个版本没有满足 macutan 的全部要求，但它证明了该方法是可行的：在 Blackberry 上正确且有吸引力地显示具有最少格式的 HTML/CSS 表格。

As I made further enhancements to meet the macutan's requirements, I discovered the same problem existed with other smart phones and there was a general need for such a routine. I continue to develop the routine with the intention that the final version copies all the Excel formatting to the HTML table.

当我进一步增强以满足 macutan 的要求时，我发现其他智能手机也存在同样的问题，并且普遍需要这样的例程。我继续开发该例程，目的是最终版本将所有 Excel 格式复制到 HTML 表中。

The code plus the instructions soon exceeded Stack Overflow's limit of 30,000 characters. I passed new versions to macutan via email. The documentation from the code module is below. If you look at my profile there is an email address. I will forward copies of my code on request.

代码加上指令很快就超过了 Stack Overflow 的 30,000 个字符的限制。我通过电子邮件将新版本传递给了 macutan。代码模块的文档如下。如果您查看我的个人资料，就会有一个电子邮件地址。我将应要求转发我的代码的副本。

  ' Converts a rectangular range within an Excel worksheet to an Html document.

  ' RangeToHtml is the only routine in this module designed to be called from
  ' outside the module.  A typical call might be:
  '     Call RangeToHtml(Worksheets("Data"), 1, 1, RowBottom, ColRight, _
  '                                     BorderStyle=Separate, CellPadding=.25)

  ' The parameters of RangeToHtml are:
  '  * Wsht         A worksheet within an open workbook
  '  * RowTop       \
  '  * ColumnLeft   | Together these specify a rectangular range within
  '  * RowBottom    | Wsht.  This is the range to be converted to Html.
  '  * ColumnRight  /
  '  * Options      Zero or more strings of the form OptionName=OptionValue
  '                 specifying which, if any, of the available high-level,
  '                 formatting options are required.

  ' Individual options are checked.  "OptionName" must be the name of a documented
  ' option and "OptionValue" must be a permitted value for that option. No space is
  ' permitted before or after the equals sign. OptionNames and OptionValues are
  ' case insensitive; that is "BorderStyle=Collapse" and "borderstyle=collapse" are
  ' both acceptable and have the same meaning. However, options are not tested for
  ' duplicates. You can, for example, specify:
  '     "BorderStyle=Separate", "BorderStyle=Collapse".
  ' In this example, "BorderStyle=Separate" will overwrite the default collapse style
  ' and then "BorderStyle=Collapse" will overwrite "BorderStyle=Separate"

  ' The available options are:
  '   * BorderStyle=Collapse
  '   * BorderStyle=Separate
  '       With Html/Css, the cells of a table can touch so there appears to be a single
  '       border between adjacent cells or they can be separated by a small gap so it is
  '       obvious that each cell has its own border. If no BorderStyle is specified,
  '       "BorderStyle=Collapse" is the default which means cell borders touch.
  '   * CellPad=.nn
  '       If this option is omitted or if "CellPad=0" is specified, there is a minimal gap
  '       between the cell border and its contents. If the option is, for example,
  '       "CellPad=.25" then will be a gap of .25 ems around the cell contents. An "em" is
  '       a measure of length equal to the height of the cell's font.
  '   * TableWidth=nnn
  '       If this option is omitted or if "TableWidth=100" is specified, the table occupies
  '       the entire available display width.  If the option is, for example, "TableWidth=50",
  '       the table will occupy 50% of the available display width. "nnn" can be greater than
  '       100 if you wish the user to scroll to see the entire table.

  ' The table below lists the formats handled at cell and or in-cell level. The default value
  ' column is explained below.
  '   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 as the Excel application level
  '   font name             font name         Calibri or as set as the Excel application level
  '   vertical alignment                      bottom

  ' There is no Html/Css equivalent to double or accouting underlining so both are
  ' converted to single underline.

  ' Each format is checked for every cell within the range. Formatting information is only
  ' output to the Html/Css if a format has a non-default value so the output is as clean and
  ' crisp as possible. You need to be careful about font name and size. Many change the name
  ' and size for a worksheet but the routine has no access to the worksheet's standard name
  ' and size; it is the Excel standard that determines the default.

  ' Html/Css does not handle wrap text at the cell level.  If no Excel cell has wrap text set
  ' then the routine does not output any column size information and columns widths are
  ' entirely determined by the receiver's browser.  If any cell has wrap text set then column
  ' widths are set by percentage so their relationship depends on the Excel column widths.

  ' Excel borders are not checked so any borders within the worksheet will not be converted.
  ' Instead every cell is given a thin, grey border so ther Html looks like a default
  ' Excel worksheet.

  ' If the cell value is numeric, the number format is checked for colour names. If
  ' appropriate, the colour specified in the number format will overwrite the cell's font
  ' colour.  See UpdateHtmlFontColourForNumericFormat for more information.

  ' Excel's default vertical alignment is "bottom" but Html/Css's is "center". If a cell
  ' is a single line, the difference will not be apparent but the Html/Css will be bigger
  ' than necessary because of "vertical-align:bottom" for every cell. It may be worth
  ' setting every cell's vertical alignment to "center".

  ' Merged cells are converted to the Html equivalent.

  ' Most of the work is performed by the macro HtmlStyleTable. This routine returns two
  ' strings, Style and Table, which RangeToHtml wraps in an Html envelope to create the
  ' document returned as a string to the caller.  Style and Table will look like:

  ' 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;}
  '         :   :   :
  '      span.bold {font-weight:bold;}
  '      span.fontclr-0070C0 {color:#0070C0;}
  '    The first two entries in Style are fixed . All other entries depend on
  '    the formats that appear in the worksheets. The td.xxx entries are used for
  '    cell-level formats. The span.xxx entries are used for in-cell formats.
  '    "In-cell formats" refers to part of a cell value being, for example, bold
  '    and part being non-bold.
  ' Table:
  '     <table class="bord-collapse">
  '       <tr>
  '         <td rowspan=2 style="width:29.41%" class="back-D7F4EA bold hAlign-center fontsize-095 fontname-Arial">Product</td>
  '         <td rowspan=2 style="width:11.76%" class="back-D7F4EA bold hAlign-center fontsize-095 fontname-Arial">Position</td>

  '   Mar12  I coded something similar in response to a Stack Overflow question. The main
  '          difference is that with the earlier version the formatting styles (for example:
  '          "background-color:#D7EAF4;") were all within the individual TD elements' STYLE
  '          attribute. I believe having all the styles in a STYLE element and references
  '          them by name in the TD elements gives a cleaner document.
  '   ?????  Having answered the Stack Overflow question to the satisfaction of the OP, I
  '          continued developing the routine. It quickly became clear that a full
  '          conversion was not practical in VBA so I converted my code to VB before
  '          continuing. I have quite an attractive VB version but it is slower than I would
  '          like. There are many complaints on the internet about the speed of access to
  '          Excel from VB and some bizarre solutions. I discovered a different approach
  '          which meant a program could obtain a lot of information about a worksheet in a
  '          single call to the InterOp. That information was provided as a string for which
  '          the documentation was poor and often wrong. However, I did manage to decode the
  '          string. Sometime, I must bring all my separate routines together in a single
  '          program to give a fast, complete conversion.
  '   Jun16  A request for a copy of my original code meant I searched my archives for that
  '          code. I did not find that code but I realised I could write a better routine.
  '          All routines in this module were coded and debugged in stages. The first
  '          version handled three cell-level formats. The final version handled more
  '          formats at both cell and in-cell level.
  '   Aug16  Added HtmlStyleTable options.

回答by JimmyPena

Inside your With block, set the BodyFormatProperty to olFormatRichText.

在 With 块中，将BodyFormatProperty设置为olFormatRichText。

With OutMail
.BodyFormat = olFormatRichText
' rest of your code

Let us know if that works.

让我们知道这是否有效。

Edit:

编辑：

Setting the HTMLBodyProperty will convert the email to HTML. If you set the BodyFormatProperty to olFormatRichTextthen you would need to use the BodyProperty, which would eliminate the possibility of using HTML.

设置HTMLBody属性会将电子邮件转换为 HTML。如果您将BodyFormat属性设置为olFormatRichText那么您将需要使用该Body属性，这将消除使用 HTML 的可能性。

So I don't see any way you could both set up the email with a HTML table andsend it as Rich Text format.

所以，我看不出有什么办法可以同时设置了一个HTML表中的电子邮件，并把它作为RTF格式。