将表格从 Outlook 电子邮件复制到 Excel 文件 - VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17678249/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copying Tables from Outlook Email to Excel File - VBA
提问by Vinay Dandekar
I am currently working on a Outlook 2010 VBA macro to pull information from a email messages and place it into an Excel file. The idea is that each email has the same fields in tables embedded in the email message every time (name, order number, date, etc.) and that data is put into a spreadsheet. To do this, I have currently used the following code to get the table and move it into Excel:
我目前正在使用 Outlook 2010 VBA 宏从电子邮件中提取信息并将其放入 Excel 文件中。这个想法是，每封电子邮件在每次嵌入电子邮件的表格中都有相同的字段（姓名、订单号、日期等），并将数据放入电子表格中。为此，我目前使用以下代码来获取表格并将其移动到 Excel 中：
'This code is inside a for each loop for each message
Set excelWorksheet2 = excelWorkbook.Worksheets.item(2)
Set excelWorksheet3 = excelWorkbook.Worksheets.item(3)
Set excelWorksheet4 = excelWorkbook.Worksheets.Add(After:=excelWorkbook.Sheets(excelWorkbook.Sheets.count))
Dim mailObj As Outlook.MailItem
Dim doc As Word.Document
Set doc = mailObj.GetInspector.WordEditor
Dim table1, table2, table3 As Object
Set table3 = doc.Tables(4).Range
Set table2 = doc.Tables(3).Range
Set table1 = doc.Tables(2).Range
table1.Copy
excelWorksheet2.Paste
table2.Copy
excelWorksheet4.Paste
table3.Copy
excelWorksheet3.Paste
Set table1 = Nothing
Set table2 = Nothing
Set table3 = Nothing
'I do much more of this to get the data from each table and put it into a master worksheet...
excelWorksheet.Cells(rows, cols + 1).Value = excelWorksheet2.Cells(4, 2).Value 'Contract Number
excelWorksheet.Cells(rows, cols + 2).Value = excelWorksheet2.Cells(4, 4).Value 'Contractor Name
Set doc = Nothing
Set excelWorksheet2 = Nothing
Set excelWorksheet3 = Nothing
Set excelWorksheet4 = Nothing
I get the following errors every so often, but it doesn't occur on the same data, it is sort of random and seems to occur on the Outlook/email side only:
我经常收到以下错误，但它不会发生在相同的数据上，它有点随机，似乎只发生在 Outlook/电子邮件端：
- "The requested member of the collection does not exist." (Error code
5941) at the
.Rangeline - "Method 'Copy' of object 'Range' failed" at the
.Copyline
- “请求的集合成员不存在。” （错误代码 5941）在该
.Range行 - “对象'Range'的方法'Copy'失败”在该
.Copy行
Sometimes both of these errors occur if I step through, if the copy fails, the macro will crash.
有时，如果我单步执行，这两种错误都会发生，如果复制失败，宏将崩溃。
I have tried:
我试过了：
- Putting in 2 second delays
- Go through fewer emails (this code usually fails when I select > 10 emails to process)
- Clearing the clipboard after every email
- Close/deallocate objects through
Nothing(not sure if this is the best practice as I'm more of a C/C++/C#/Java guy)
- 延迟 2 秒
- 通过更少的电子邮件（当我选择 > 10 封电子邮件进行处理时，此代码通常会失败）
- 每封电子邮件后清除剪贴板
- 通过关闭/释放对象
Nothing（不确定这是否是最佳实践，因为我更像是一个 C/C++/C#/Java 人）
None of these seemed to remotely fix this issue as both errors pop up frequently, but intermittently. I'm truly at a loss as to what the next step would be in debugging this issue, any help would be much appreciated!
这些似乎都没有远程解决这个问题，因为这两个错误经常弹出，但间歇性弹出。对于调试这个问题的下一步是什么，我真的很茫然，任何帮助将不胜感激！
回答by Vinay Dandekar
Based on research I have been doing on the issue of the WordEditor tables, it seems as the amount of copy/paste operations and the searching HTML tables just simply do not allow for reliable behavior. One possible solution to this could be to copy the entire email into Excel and parse the tables that way (this still requires copy/paste).
根据我对 WordEditor 表格问题所做的研究，似乎复制/粘贴操作和搜索 HTML 表格的数量根本不允许可靠的行为。一种可能的解决方案是将整个电子邮件复制到 Excel 中并以这种方式解析表格（这仍然需要复制/粘贴）。
What I did to solve this problem (indirectly) is to save all the emails as HTML files (through Outlook.Application running in Excel: email.SaveAs folderPath + fileName + ".html", olHTML) in a temporary directory and have Excel open the HTML files in a workbook and work with the data that way. This has been much more reliable (added overhead though) and allows for large volumes of emails to be exported to Excel properly.
我为解决这个问题所做的（间接）是将所有电子邮件保存为 HTML 文件（通过 Outlook.Application 在 Excel 中运行：）email.SaveAs folderPath + fileName + ".html", olHTML在一个临时目录中，并让 Excel 打开工作簿中的 HTML 文件并以这种方式处理数据. 这更加可靠（虽然增加了开销），并且允许将大量电子邮件正确导出到 Excel。
If anyone wants the exact code to my problem, message me (vindansam at hotmail.com, it's a tad long and has some proprietary information in it).
如果有人想要我的问题的确切代码，请给我发消息（hotmail.com 上的 vindansam，它有点长，其中包含一些专有信息）。
It's too bad that the WordEditor seems to not handle the information well, maybe Microsoft will beef things up in the next version of Office.
WordEditor 似乎不能很好地处理信息，这太糟糕了，也许微软会在下一个版本的 Office 中加强这一点。
