本文介绍了将特定格式表从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? 这是我在我的展望中的表格:This is the table I have in my outlook: 这是怎么回事我想在我的Excel上复制它: 结果如下: VBA代码:Option ExplicitPublic fso As New FileSystemObjectPublic objApp As Outlook.ApplicationPublic oItem As Outlook.MailItemSub importOutlookTable() Set oItem = GetCurrentItem() oItem.displayDim oHTML As MSHTML.HTMLDocument: Set oHTML = New MSHTML.HTMLDocumentDim oElColl As MSHTML.IHTMLElementCollectionWith oHTML .body.innerHTML = oItem.HTMLBody Set oElColl = .getElementsByTagName("table")End With'import in ExcelDim x As Long, y As LongWith 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 yNext xEnd WithSet objApp = NothingSet oItem = NothingSet oHTML = NothingSet oElColl = NothingEnd SubFunction 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 = NothingEnd 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 yNext xEnd With使用此:Dim colTabEle As New CollectionDim i As Integer: i = 1For 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 yNext xWith 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 xEnd With 这篇关于将特定格式表从outlook复制到电子邮件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 09-12 19:56