将特定格式表从outlook复制到电子邮件 [英] Copy a specific format table from outlook to email
本文介绍了将特定格式表从outlook复制到电子邮件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要在工作中将许多表从outlook复制到Excel。
我知道如何使用 .getElementsByTagName（table）来做到这一点。
但是，我的公司合并并拆分了一些表格单元格。
有人可以查看吗？
I need to copy many tables from outlook to Excel at work.
I know how to use .getElementsByTagName("table") to do it.
However, my company merges and split some of the table cells.
Could someone have a look on it please?
这是我在我的展望中的表格：
< img src =https://i.stack.imgur.com/zyJhl.pngalt =在此处输入图像说明>
This is the table I have in my outlook:
这是怎么回事我想在我的Excel上复制它：
结果如下：
VBA代码：
Option Explicit
Public fso As New FileSystemObject
Public objApp As Outlook.Application
Public oItem As Outlook.MailItem
Sub importOutlookTable()
Set oItem = GetCurrentItem()
oItem.display
Dim oHTML As MSHTML.HTMLDocument: Set oHTML = New MSHTML.HTMLDocument
Dim oElColl As MSHTML.IHTMLElementCollection
With oHTML
.body.innerHTML = oItem.HTMLBody
Set oElColl = .getElementsByTagName("table")
End With
'import in Excel
Dim x As Long, y As Long
With Worksheets("sheet2")
For x = 0 To oElColl(0).Rows.Length - 1
For y = 0 To oElColl(0).Rows(x).Cells.Length - 1
.Range("a1").Offset(x, y).Value = oElColl(0).Rows(x).Cells(y).innerText
Next y
Next x
End With
Set objApp = Nothing
Set oItem = Nothing
Set oHTML = Nothing
Set oElColl = Nothing
End Sub
Function GetCurrentItem() As Object
Set objApp = CreateObject("Outlook.Application")
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
End Select
Set objApp = Nothing
End Function
我的展望中的HTML代码：
</head>
<body lang="ZH-TW" link="#0563C1" style='text-justify-trim:punctuation' vlink=
"#954F72">
<div class="WordSection1">
<table border="0" cellpadding="0" cellspacing="0" class=
"MsoNormalTable" style=
'width:203.0pt;margin-left:.1pt;border-collapse:collapse' width="271">
<tr style='height:15.75pt'>
<td nowrap rowspan="2" style=
'width:60.0pt;border:solid windowtext 1.0pt;border-bottom:solid black 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:15.75pt'
width="80">
<p class="MsoNormal" style=
'font-weight: bold; text-align: center'><span lang="EN-US"
style='font-family:"Arial",sans-serif'>header 1</span></p>
</td>
<td nowrap style=
'width:83.0pt;border-top:solid windowtext 1.0pt;border-left:none;border-bottom:none;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:15.75pt'
width="111">
<p class="MsoNormal" style=
'font-weight: bold; text-align: center'><span lang="EN-US"
style='font-family:"Arial",sans-serif'>header 2</span></p>
</td>
<td nowrap rowspan="2" style=
'width:60.0pt;border-top:solid windowtext 1.0pt;border-left:none;border-bottom:solid black 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:15.75pt'
width="80">
<p class="MsoNormal" style=
'font-weight: bold; text-align: center'><span lang="EN-US"
style='font-family:"Arial",sans-serif'>header 4</span></p>
</td>
<td height="21" style='height:15.75pt;border:none' width="0">
</td>
</tr>
<tr style='height:16.5pt'>
<td nowrap style=
'width:83.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:16.5pt'
width="111">
<p class="MsoNormal" style=
'font-weight: bold; text-align: center'><span lang="EN-US"
style='font-family:"Arial",sans-serif'>header 3</span></p>
</td>
<td height="22" style='height:16.5pt;border:none' width="0">
</td>
</tr>
<tr style='height:18.0pt'>
<td nowrap rowspan="3" style=
'width:60.0pt;border-top:none;border-left:solid windowtext 1.0pt;border-bottom:solid black 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:18.0pt'
width="80">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style=
'font-family:"Arial",sans-serif'>apple</span></p>
</td>
<td nowrap rowspan="2" style=
'width:83.0pt;border:none;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:18.0pt'
width="111">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style=
'font-family:"Arial",sans-serif'>red</span></p>
</td>
<td nowrap rowspan="3" style=
'width:60.0pt;border-top:none;border-left:none;border-bottom:solid black 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:18.0pt'
width="80">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style='font-family:"Arial",sans-serif'>4</span></p>
</td>
<td height="24" style='height:18.0pt;border:none' width="0">
</td>
</tr>
<tr style='height:18.0pt'>
<td height="24" style='height:18.0pt;border:none' width="0">
</td>
</tr>
<tr style='height:15.75pt'>
<td nowrap style=
'width:83.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:15.75pt'
width="111">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style=
'font-family:"Arial",sans-serif'>fruit</span></p>
</td>
<td height="21" style='height:15.75pt;border:none' width="0">
</td>
</tr>
<tr style='height:18.0pt'>
<td nowrap rowspan="3" style=
'width:60.0pt;border-top:none;border-left:solid windowtext 1.0pt;border-bottom:solid black 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:18.0pt'
width="80">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style=
'font-family:"Arial",sans-serif'>banana</span></p>
</td>
<td nowrap rowspan="2" style=
'width:83.0pt;border:none;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:18.0pt'
width="111">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style=
'font-family:"Arial",sans-serif'>yellow</span></p>
</td>
<td nowrap rowspan="3" style=
'width:60.0pt;border-top:none;border-left:none;border-bottom:solid black 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:18.0pt'
width="80">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style='font-family:"Arial",sans-serif'>3</span></p>
</td>
<td height="24" style='height:18.0pt;border:none' width="0">
</td>
</tr>
<tr style='height:18.0pt'>
<td height="24" style='height:18.0pt;border:none' width="0">
</td>
</tr>
<tr style='height:15.75pt'>
<td nowrap style=
'width:83.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:15.75pt'
width="111">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style=
'font-family:"Arial",sans-serif'>fruit</span></p>
</td>
<td height="21" style='height:15.75pt;border:none' width="0">
</td>
</tr>
<tr style='height:18.0pt'>
<td nowrap rowspan="3" style=
'width:60.0pt;border-top:none;border-left:solid windowtext 1.0pt;border-bottom:solid black 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:18.0pt'
width="80">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style=
'font-family:"Arial",sans-serif'>cat</span></p>
</td>
<td nowrap rowspan="2" style=
'width:83.0pt;border:none;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:18.0pt'
width="111">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style=
'font-family:"Arial",sans-serif'>cute</span></p>
</td>
<td nowrap rowspan="3" style=
'width:60.0pt;border-top:none;border-left:none;border-bottom:solid black 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:18.0pt'
width="80">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style='font-family:"Arial",sans-serif'>1</span></p>
</td>
<td height="24" style='height:18.0pt;border:none' width="0">
</td>
</tr>
<tr style='height:18.0pt'>
<td height="24" style='height:18.0pt;border:none' width="0">
</td>
</tr>
<tr style='height:15.75pt'>
<td nowrap style=
'width:83.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt;height:15.75pt'
valign="bottom" width="111">
<p class="MsoNormal" style='text-align: center'><span lang=
"EN-US" style=
'font-family:"Arial",sans-serif'>animal</span></p>
</td>
<td height="21" style='height:15.75pt;border:none' width="0">
</td>
</tr>
</table>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
</div>
</body>
</html>
推荐答案
这应该可以解决问题。它首先将元素分解为集合，因此您不必担心 Cells.Length 中的变化。在HTML表格中，一些行有4个单元格，而其他行只有一个跨越2x高的行。这就是为什么它没有正确填充你的表
This should do the trick. It breaks the elements out into a collection first, so you don't have to worry about the variation in Cells.Length. In the HTML table some rows have 4 cells, and others have only one that spans the row 2x high. Which is why it wasn't populating your table properly
你必须重新格式化目标，但它会将Header 2下的数据分开，并避免浪费行。
You'll have to reformat the target, but it will keep the data under Header 2 separate, and avoid the wasted rows.
替换此：
With Worksheets("sheet2")
For x = 0 To oElColl(0).Rows.Length - 1
For y = 0 To oElColl(0).Rows(x).Cells.Length - 1
.Range("a1").Offset(x, y).Value = oElColl(0).Rows(x).Cells(y).innerText
Next y
Next x
End With
使用此：
Dim colTabEle As New Collection
Dim i As Integer: i = 1
For x = 0 To oElColl(0).Rows.Length - 1
For y = 0 To oElColl(0).Rows(x).Cells.Length - 1
colTabEle.Add oElColl(0).Rows(x).Cells(y).innerText
Next y
Next x
With Worksheets("sheet2")
For x = 0 To (colTabEle.Count + 1) / 4
For y = 0 To 2
.Range("a1").Offset(x, y).Value = colTabEle(i)
If IsNumeric(colTabEle(i)) Then
i = i + 2
Else
i = i + 1
End If
Next y
Next x
End With
这篇关于将特定格式表从outlook复制到电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
