使用 VBA 将电子邮件中的表格复制到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19436212/
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
Copy table in email to Excel using VBA
提问by user2892053
I am trying to create some code that will copy the body of an email into a new Excel spreadsheet. I have this code:
我正在尝试创建一些代码，将电子邮件正文复制到新的 Excel 电子表格中。我有这个代码：
Public Sub ExportToExcel1()
Dim ns As NameSpace
Dim Inbox As MAPIFolder
Dim myitem As Outlook.MailItem
Dim FileName As String
Dim i As Integer
Dim objSearchFolder As Outlook.MAPIFolder
Dim item As Object
Dim mai As MailItem
Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox).Folders("Hold Info")
Set objSearchFolder = Inbox
i = 0
For Each item In Inbox.Items
item.Display
item.Body.Select
Selection.Copy
Dim xlApp As Object ' Excel.Application
Dim xlWkb As Object ' Excel.Workbook
Set xlApp = CreateObject("Excel.Application") ' New Excel.Application
Set xlWkb = xlApp.Workbooks.Add
xlApp.Visible = True
xlApp.Workbooks.Add
xlApp.Selection.Paste False, False, False
Next
End Sub
It keeps giving me an error at item.Body.Selectand I have no clue why. It may have something to do with the fact that the email I am trying to copy is nothing but tables that were generated in Oracle, but I have no clue.
它一直给我一个错误，item.Body.Select我不知道为什么。这可能与我试图复制的电子邮件只不过是在 Oracle 中生成的表的事实有关，但我不知道。
回答by Dick Kusleika
You could use the Clipboard directly rather than trying to select and copy. If you have a userform in your project, you already have this reference set. If not, set a reference to Microsoft Forms 2.0 Object Library. Then use a DataObject to put some text into the Clipboard.
您可以直接使用剪贴板而不是尝试选择和复制。如果您的项目中有用户表单，则您已经拥有此参考集。如果没有，请设置对 Microsoft Forms 2.0 对象库的引用。然后使用 DataObject 将一些文本放入剪贴板。
Public Sub ExportToExcel1()
Dim ns As NameSpace
Dim Inbox As MAPIFolder
Dim item As Object
Dim doClip As MSForms.DataObject
Dim xlApp As Object ' Excel.Application
Dim xlWkb As Object
Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox).Folders("Hold Info")
Set doClip = New MSForms.DataObject
For Each item In Inbox.Items
If TypeName(item) = "MailItem" Then
doClip.SetText item.Body
doClip.PutInClipboard
Set xlApp = CreateObject("Excel.Application") ' New Excel.Application
xlApp.Visible = True
Set xlWkb = xlApp.Workbooks.Add
xlWkb.Sheets(1).Range("A1").PasteSpecial "Text"
End If
Next
End Sub
A couple of points to consider. When you use the Clipboard in this fashion, you're using the Windows Clipboard, not the Office Clipboard. The Windows Clipboard doesn't recognize Office specific Clipboard formats, so you lose a little in translation.
需要考虑的几点。当您以这种方式使用剪贴板时，您使用的是 Windows 剪贴板，而不是 Office 剪贴板。Windows 剪贴板无法识别 Office 特定的剪贴板格式，因此您在翻译时会损失一些。
Pasting from the Clipboard has some advantages. But if you want complete control of how your data shows up in Excel, then read the Body into a string, parse the string for the data you want, and write the specific data to the cells you want.
从剪贴板粘贴有一些优点。但是，如果您想完全控制数据在 Excel 中的显示方式，则将 Body 读入字符串，解析字符串以获取所需数据，然后将特定数据写入所需单元格。
