出口表从excel的展望是黑莓友好 [英] export table to outlook from excel that is blackberry friendly
本文介绍了出口表从excel的展望是黑莓友好的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
感谢
P
Sub Mail_Sheet_Outlook_Body（）
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
应用程序
.EnableEvents = False
.ScreenUpdating = False
结束
设置rng =没有
设置rng = Selection.SpecialCells（xlCellTypeVisible）
设置OutApp = CreateObject（Outlook.Application）
设置OutMail = OutApp.CreateItem（0）
开错误简历Next
带OutMail
.To =email@email.com
.CC =
.BCC =
.Subject =输入主题文本
.HTMLBody = RangetoHTML（rng）
'.Attachments.Add（c：\temp\& ActiveSheet.Range（DateSerial）。Value&。 pdf）
。显示
结束
在Erro上r GoTo 0
应用程序
.EnableEvents = True
.ScreenUpdating = True
结束
设置OutMail = Nothing
Set OutApp = Nothing
End Sub
功能范围toHTML（rng As Range）
'适用于Excel 2000，Excel 2002，Excel 2003，Excel 2007，Excel 2010，Outlook 2000 ，Outlook 2002，Outlook 2003，Outlook 2007和Outlook 2010.
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
TempFile = Environ $（temp）& /&格式（现在，dd-mm-yy h-mm-ss）& .htm
'复制范围并创建工作簿以接收数据。
rng.Copy
设置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
在错误恢复下一个
.DrawingObjects.Visible = True
.DrawingObjects.Delete
错误GoTo 0
结束
'发布工作表到.htm文件。
With TempWB.PublishObjects.Add（_
SourceType：= xlSourceRange，_
文件名：= TempFile，_
工作表：= TempWB.Sheets（1）.Name，_
来源：= TempWB.Sheets（1）.UsedRange.Address，_
HtmlType：= xlHtmlStatic）
.Publish（True）
结束
'将.htm文件中的所有数据读入RangetoHTML子例程。
设置fso = CreateObject（Scripting.FileSystemObject）
设置ts = fso.GetFile（TempFile）.OpenAsTextStream（1，-2）
RangetoHTML = ts.ReadAll
ts .Close
RangetoHTML =替换（RangetoHTML，align = center x：publishsource =，align = left x：publishsource =）
'关闭TempWB。
TempWB.Close savechanges：= False
'删除htm文件。
Kill TempFile
设置ts =没有
设置fso =没有
设置TempWB =没有
结束函数
解决方案
要回答的背景
问题的目的是能够获得在BlackBerry中可读的电子邮件中发送的Excel表。之前有一些红色的鲱鱼，问题的原因被确定为Excel的PublishObjects。问题可能是大量不必要的CSS格式或单元格和字体的大小，但无论什么问题，黑莓的显示引擎都无法正确呈现HTML。
我创建了一个相当简单的VBA代码来创建一个范围内的HTML表。它将值，粗体，斜体，字体颜色，背景颜色和列宽从Excel复制到HTML表。虽然第一个版本没有满足macutan的完整要求，但它表明该方法是可行的：具有最小格式化的HTML / CSS表格在Blackberry中正确显示和引人入胜。
当我进一步改进以满足macutan的要求时，我发现与其他智能手机存在同样的问题，而且普遍需要这样一个例程。我继续开发例程，目的是最终版本将所有的Excel格式复制到HTML表格。
代码加上指令很快超过了Stack Overflow的限制为30,000字符。我通过电子邮件将新版本通过了macutan。代码模块的文档如下。如果您看我的个人资料，那里有一个电子邮件地址。我将根据要求转发我的代码副本。
'将Excel工作表中的矩形范围转换为Html文档。
'RangeToHtml是这个模块中唯一的例子，设计为从模块外的
'调用。典型的通话可能是：
'Call RangeToHtml（Worksheets（Data），1，RowBottom，ColRight，_
'BorderStyle = Separate，CellPadding = .25）
'RangeToHtml的参数是：
'* Wsht打开的工作簿中的工作表
'* RowTop \
'* ColumnLeft |这些在
'* RowBottom |中指定一个矩形范围WSHT。这是要转换为Html的范围。
'* ColumnRight /
'*选项零个或多个形式的字符串OptionName = OptionValue
'指定可用的高级，
'格式化选项（如果有）是必要的。
'检查各个选项。 OptionName必须是已记录的
'选项的名称，OptionValue必须是该选项的允许值。等号之前或之后不允许空格为
'。 OptionNames和OptionValues是
'不区分大小写;即BorderStyle = Collapse和borderstyle = collapse是
'都可以接受，含义相同。但是，对于
'重复的选项，并没有测试选项。例如，您可以指定：
'BorderStyle = Separate，BorderStyle = Collapse。
'在本示例中，BorderStyle = Separate将覆盖默认的折叠样式
'，然后BorderStyle = Collapse将覆盖BorderStyle = Separate
'可用选项是：
'* BorderStyle =折叠
'* BorderStyle =单独
'使用Html / Css，表格的单元格可以触摸，因此似乎有一个
'在相邻单元格之间的边界，或者它们之间可以分开一个小的间隙，所以它是
'明显的每个单元格都有自己的边框。如果没有指定BorderStyle，则
'BorderStyle = Collapse是默认值，意味着单元格边框的触摸。
'* CellPad = .nn
'如果省略此选项，或指定了CellPad = 0，则单元格边框与其内容之间存在最小间隙
'。如果该选项是例如
'CellPad = .25，那么将围绕单元格内容的间隙为.25 ems。 em是
'长度等于单元格字体高度的度量。
'* TableWidth = nnn
'如果省略此选项，或者指定了TableWidth = 100，则该表占用整个可用显示宽度的
'。如果该选项是例如TableWidth = 50，
'该表将占用可用显示宽度的50％。如果您希望用户滚动查看整个表格，nnn可以大于
'100。
'下表列出了在单元格和单元格级别处理的格式。下面解释默认值
'列。
'细胞水平细胞缺失值
'bold bold false
'italic italic false
'删除线删除线false
'下划线单下划线单个无下划线
'下划线双下划线双无下划线
'下划线会计无下划线
'字体颜色字体颜色黑色
'背景颜色白色
'水平对齐字符串左边;正确的数字和日期
'字体大小字体大小11或设置为Excel应用程序级
'字体名称字体名称Calibri或设置为Excel应用程序级
'垂直对齐底部
'没有Html / Css相当于double或accouting下划线，所以都是
'转换为单下划线。
'检查范围内的每个单元格的每个格式。格式化信息只有
'输出到Html / Css，如果格式具有非默认值，所以输出是干净的，
'尽可能清晰。您需要注意字体名称和大小。许多更改名称
'和工作表的大小，但该例程无法访问工作表的标准名称
'和大小;它是确定默认值的Excel标准。
'Html / Css不处理单元格级别的包装文本。如果没有Excel单元格包含文本集
'，则例程不会输出任何列大小信息，列宽度由
完全由接收者的浏览器确定。如果任何单元格具有换行文本集，则列
'width按百分比设置，因此它们的关系取决于Excel列宽。
'不检查Excel边框，因此工作表中的任何边框都不会被转换。
'相反，每个单元格都有一个薄而灰色的边框，所以Html看起来像一个默认的
'Excel工作表。
'如果单元格值是数字，则会检查颜色名称的数字格式。如果
'合适，数字格式中指定的颜色将覆盖单元格的字体
'颜色。有关详细信息，请参阅UpdateHtmlFontColourForNumericFormat。
'Excel的默认垂直对齐方式为bottom，但Html / Css为center。如果一个单元格
'是单行，则差异不会很明显，但是由于每个单元格的vertical-align：bottom，Html / Css将会比必需更大
'。将每个单元格的垂直对齐设置为中心可能值
'。
'合并的单元格转换为Html等价物。
'大部分工作是由宏HtmlStyleTable执行的。此例程返回两个
'字符串Style和Table，RangeToHtml在Html信封中打包，以创建作为字符串返回给调用者的
'文档。样式和表格将如下所示：
'样式：
'table {border-collapse：collapse;}
'td {border-style：solid;边框宽度：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;}
'Style中的前两个条目是固定的。所有其他条目取决于
'工作表中显示的格式。 td.xxx条目用于
'单元格级格式。 span.xxx条目用于单元格格式。
'单元格格式是指单元格值的一部分，例如粗体
'，而部分为非粗体。
'表：
'< 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我对Stack Overflow问题进行了类似的编码。主要的
'区别在于，在早期版本中，格式化样式（例如：
'background-color：＃D7EAF4;）都在单个TD元素STYLE
属性。我相信在STYLE元素中拥有所有样式，并通过TD元素中的名称引用
'来提供一个更干净的文档。
'已经回答了Stack Overflow问题以满足OP，I
'继续开发例程。很快，很明显，一个完整的
'转换在VBA中是不实际的，所以我将代码转换为VB，然后
'继续。我有一个非常有吸引力的VB版本，但它比我更愿意
'喜欢。互联网上有很多关于从VB访问
'Excel的速度和一些奇怪的解决方案的投诉。我发现了一种不同的方法
'，这意味着一个程序可以在
'单一调用InterOp中获取大量关于工作表的信息。该信息是作为一个字符串提供的，其中
'的文档很差，往往是错误的。但是，我确实设法解码
'字符串。有时候，我必须将所有单独的例程都放在一个单独的
'程序中，以进行快速，完整的转换。
'Jun16要求我的原始代码的副本意味着我搜索我的档案
'代码。我没有找到代码，但我意识到我可以写一个更好的例程。
'该模块中的所有例程都分阶段编码和调试。第一个
'版本处理了三个单元格级格式。最终版本在单元格和单元格级别处理更多的
'格式。
'Aug16添加了HtmlStyleTable选项。
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).
Thanks 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@email.com"
.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
解决方案
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.
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.
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.
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.
' 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.
这篇关于出口表从excel的展望是黑莓友好的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
