跳到主要内容

如何将Outlook电子邮件正文文本导出到Excel电子表格?

添加一名作者 最后修改时间:2020-10-30

如果要将选定的电子邮件正文文本从Outlook导出到Excel电子表格,则本文中的方法可以为您提供帮助。

使用VBA代码将Outlook电子邮件正文文本导出到Excel电子表格


使用VBA代码将Outlook电子邮件正文文本导出到Excel电子表格<

请运行下面的VBA代码,将Outlook电子邮件的选定正文文本导出到Excel。

1.打开电子邮件,选择要导出到Excel电子表格的电子邮件正文,然后按 其他 + F11 键打开 Microsoft Visual Basic应用程序 窗口。

2.在 Microsoft Visual Basic应用程序 窗口中,单击 插页 > 模块。 然后将下面的VBA代码复制到“代码”窗口中。

VBA代码:将Outlook电子邮件正文文本导出到Excel电子表格

Sub ExportToExcel()
Dim xExcel As Excel.Application
Dim xWb As Workbook
Dim xWs As Worksheet
Dim xInspector As Inspector
Dim xItem As Object
Dim xMailItem As MailItem
Dim xDoc As Document
Dim xShell As Object
Dim xFilePath As String
On Error Resume Next
    Set xShell = CreateObject("Shell.Application")
    Set xFolder = xShell.BrowseForFolder(0, "Select a Folder:", 0, 0)
    If TypeName(xFolder) = "Nothing" Then Exit Sub
    Set xFolderItem = xFolder.Self
    xFilePath = xFolderItem.Path & "\"
    Set xItem = Outlook.Application.ActiveExplorer.Selection.item(1)
    If xItem.Class <> olMail Then Exit Sub
    Set xMailItem = xItem
    Set xInspector = xMailItem.GetInspector
    Set xDoc = xInspector.WordEditor
    xDoc.Application.Selection.Range.Copy
    xInspector.Close olDiscard
    Set xExcel = New Excel.Application
    Set xWb = xExcel.Workbooks.Add
    Set xWs = xWb.Sheets.item(1)
    xExcel.Visible = False
    xWs.Activate
    xWs.Paste
    xWs.SaveAs xFilePath & "Email body.xlsx"
    xWb.Close True
    xExcel.Quit
    Set xWs = Nothing
    Set xWb = Nothing
    Set xExcel = Nothing
End Sub

备注:在代码中,“电子邮件body.xlsx”是您将使用所选电子邮件正文文本创建的工作簿名称。 您可以根据需要进行更改。

3。 点击 工具 > 参考资料。 然后检查 Microsoft Excel对象库Microsoft Word对象库 中的框 参考–项目 对话框。 看截图:

4.然后 浏览文件夹 弹出对话框,请选择一个文件夹来保存工作簿,然后单击 OK 按钮。

现在,一个名为“电子邮件正文”将创建并保存到指定的文件夹中。 打开工作簿,您可以看到所选的电子邮件正文文本已导出到工作簿的Sheet1。


最佳办公生产力工具

最新消息:Kutools for Outlook 发布 免费版本!

体验全新的 Kutools for Outlook 免费版本拥有 70 多项令人难以置信的功能,您可以永久使用! 点击立即下载!

🤖 Kutools人工智能 : 具有人工智能魔力的即时专业电子邮件——一键天才回复、完美语气、多语言掌握。轻松改变电子邮件! ...

📧 电子邮件自动化: 自动回复(适用于 POP 和 IMAP)  /  安排发送电子邮件  /  发送电子邮件时按规则自动抄送/密件抄送  /  自动转发(高级规则)   /  自动添加问候语   /  自动将多收件人电子邮件拆分为单独的消息 ...

📨 电子邮件管理: 撤回电子邮件  /  按主题和其他人阻止诈骗电子邮件  /  删除重复的电子邮件  /  高级搜索  /  合并文件夹 ...

📁 附件专业版批量保存  /  批量分离  /  批量压缩  /  自动保存   /  自动分离  /  自动压缩 ...

🌟 界面魔法: 😊更多又漂亮又酷的表情符号   /  收到重要邮件时提醒您  /  最小化 Outlook 而不是关闭 ...

👍 一键奇迹: 使用传入附件回复全部  /   反网络钓鱼电子邮件  /  🕘显示发件人的时区 ...

👩🏼‍🤝‍👩🏻 通讯录和日历: 从选定的电子邮件中批量添加联系人  /  将联系人组拆分为各个组  /  删除生日提醒 ...

只需单击一下即可立即解锁 Kutools for Outlook -永久免费. 别等了, 立即下载并提高您的效率!

kutools for outlook 功能1 kutools for outlook 功能2
 

 

 

Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
this work but in one email only what if in folder theres a multiple email thats need to be extracted in excel?
This comment was minimized by the moderator on the site
Você vai precisar implementar o código fazendo um Looping, com um FOR por exemplo:

Sub lerEmails()

' Criando a aplicação do Outlook
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")

' Criando um Namespace, que seria uma sessão no Outlook
Dim objNSpace As Object
Set objNSpace = objOutlook.GetNamespace("MAPI")

' Cria um objeto com a pasta Inbox do Outlook
Dim minhaPasta As Object
Set minhaPasta = objNSpace.GetDefaultFolder(olFolderInbox)

Dim i As Long
Dim itemPasta As Object

i = 2 'Linha que vai começar preenchendo na planilha

' Percorrer todos os itens dentro da pasta
For Each itemPasta In minhaPasta.Items

If itemPasta.Class = olMail Then
Dim objEmail As Outlook.MailItem
Set objEmail = itemPasta

Cells(i, 1).Value = objEmail.SenderEmailAddress
Cells(i, 2).Value = objEmail.To
Cells(i, 3).Value = objEmail.Subject
Cells(i, 4).Value = objEmail.ReceivedTime
Cells(i, 5).Value = objEmail.Body
Cells(i, 5).WrapText = False

End If
i = i + 1

Next

Set objEmail = Nothing
Set objOutlook = Nothing
Set objNSpace = Nothing
Set minhaPasta = Nothing

End Sub
This comment was minimized by the moderator on the site
Hi the code only returned directly, into the excel and not the body of the email, may I know why was that??
This comment was minimized by the moderator on the site
same issue for me as well
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations