如何将Outlook电子邮件中的多个表导出到Excel（同一张表） [英] How do I export multiple tables from outlook email to excel (same sheet)

问题描述

朋友们好，



我很难理解如何将多个表从Outlook电子邮件导出到Excel。无论我到哪里，我都会找到如何将多张桌子放到不同的床单中，但我正在寻找一种方法在同一张纸上做这件事。



电子邮件我希望将其自动化为格式化为两个（或更多）单独的表。我希望能够在具有相同主题的电子邮件上使用此脚本，但我对.msg文件中的不同主题有不同的行数。有些电子邮件有1个表，10行x 2个colomns，有些可能有3个表，2行x 2个colomns。 Colomn数量永远不会超过2.



我希望脚本能够让我选择要从中提取数据的电子邮件并创建新的Excel工作簿。示例：我选择了15封电子邮件并按下宏按钮，Excel工作表将获得一个标题行，其中的属性名称为A1，B1 .....，然后使用属性值填充下方的行。



我会在收到这封电子邮件时尝试向您展示它的样子：





_____________________________________________

信息

______________________________________________

Phonenr：9434343



反馈：tutu



主题VITO



mailto [e-mail]

______________________________________________

用户信息

______________________________________________

浏览器FIREFOX



ClientIP 00.00 .0.0



网站CRM









好​​的，所以我在Excel中的理想表现在是这样的：

Phonenr |反馈|主题| mailto |浏览器| ClientIP |网站|

______________________________________________________________________________

9434343 tutu VITO [e-mail] FIREFOX 00.00.0.0 CRM



价值观将在下面添加下一封电子邮件（本例中为第3行）。当电子邮件中出现表名时，请注意我不关心它们。在本例信息和用户信息中，删除，删除，删除！ :)



我尝试了什么：



太多了!! ！

但这是谷歌在经过两周的挖掘后带领我的地方。此代码段分隔为不同的工作表，并且缺少格式化为标题和行的功能。 （由于工作策略，我无法为此下载完成的加载项（并且我知道它存在:(），因为所有代码必须是原始的，并且可以由我自己处理）

 Sub ExportTablesinEmailtoExcel（）
 Dim objMail As Outlook.MailItem 
 Dim objWordDocument As Word.document 
 Dim objTable As Word.Table 
 Dim lTableCount As Long 
 Dim objExcelApp As Excel.Application 
 Dim objExcelWorkbook As Excel.Workbook 
 Dim objExcelWorksheet As Excel.Worksheet 
 Dim i As Long 
 
'创建一个新的excel工作簿
设置objExcelApp = CreateObject（Excel.Application）
设置objExcelWorkbook = objExcelApp.Workbooks.Add 
 objExcelApp.Visible = True 
 
'获取所选电子邮件中的表
设置objMail = Outlook.Application.ActiveExplorer.Selection.Item（1）
设置objWordDocument = objMail.GetInspector.WordEditor 
 lTableCount = objWordDocument .Tables.Cou nt 
 
 
如果lTableCount> 1然后
'如果有多个表
'将每个表复制到单独的工作表中
对于i = 1到lTableCount 
设置objTable = objWordDocument.Tables（i）
 objTable.Range.Copy 
 
设置objExcelWorksheet = objExcelWorkbook.Sheets（i）
 objExcelWorksheet.Paste 
 objExcelWorksheet.Columns.AutoFit 
 Next 
 Else 
'如果只有一个表
'只需将其复制到第一个工作表中
设置objTable = objWordDocument.Tables（1）
 objTable.Range.Copy 
 
设置objExcelWorksheet = objExcelWorkbook.Sheets（1）
 objExcelWorksheet.Paste 
 objExcelWorksheet.Columns.AutoFit 
 End if 
 End Sub 

解决方案

读取表格后，您需要提取并将内容重新格式化为输出行。



（即您当前的表格）不符合计划输出的布局。



c＃ - 使用Microsoft.Office.Interop.Word解析表，只从第一列获取文本？ - 堆栈溢出 [ ^ ]



（当然，您可以为多个列完成）。

Hello friends,

I am having difficulties understanding how I can export multiple tables from Outlook emails to Excel. Everywhere I look I find how to do it for multiple tables into different sheets, but I am looking for a way to do this in the same sheet.

The emails that I want to automate this for are formatted as two(or more) separate tables. I want to be able to use this script on emails with the same subject, but I have a different amount of rows to the different subjects in the .msg files. Some emails have 1 table with 10 rows x 2 colomns, and some may have 3 tables with 2 rows x 2 colomns. Colomn number never exceeds 2.

I would like the script to allow me to select what emails I would like to extract data from and create a new Excel workbook. Example: I select 15 emails and press the macro-button and the Excel-sheet gets a header row with the attribute names on A1, B1....., and then fills the rows beneath with the attribute values.

I will try showing you how it looks like when I receive the email here:


_____________________________________________
Info
______________________________________________
Phonenr: 9434343

Feedback: tutu

topic VITO

mailto [e-mail]
______________________________________________
User Information
______________________________________________
Browser FIREFOX

ClientIP 00.00.0.0

Site CRM




OK, so my ideal sheet in Excel would now be like this:
Phonenr | Feedback | topic | mailto | Browser | ClientIP | Site |
______________________________________________________________________________
9434343 tutu VITO [e-mail] FIREFOX 00.00.0.0 CRM

The values of the next email is to be added below (row 3 in this example). Please mark that I do NOT care about the table names when they appear in the email. In this example "Info" and "User Information", delete, delete, delete! :)

What I have tried:

SO MUCH!!!
But this is where Google has lead me after two weeks of digging. This codesnippet is separating into different sheets, and is lacking the feature for formatting into header and rows. (Due to work policy I cannot download a finished add-in for this(AND YES I KNOW IT EXISTS :( ), as all code must be raw and possible to process by others than myself)

Sub ExportTablesinEmailtoExcel()
    Dim objMail As Outlook.MailItem
    Dim objWordDocument As Word.document
    Dim objTable As Word.Table
    Dim lTableCount As Long
    Dim objExcelApp As Excel.Application
    Dim objExcelWorkbook As Excel.Workbook
    Dim objExcelWorksheet As Excel.Worksheet
    Dim i As Long
 
    'Create a new excel workbook
    Set objExcelApp = CreateObject("Excel.Application")
    Set objExcelWorkbook = objExcelApp.Workbooks.Add
    objExcelApp.Visible = True
 
    'Get the table(s) in the selected email
    Set objMail = Outlook.Application.ActiveExplorer.Selection.Item(1)
    Set objWordDocument = objMail.GetInspector.WordEditor
    lTableCount = objWordDocument.Tables.Count
 
 
    If lTableCount > 1 Then
       'If there is more than one table
       'Copy each table into separate worksheet
       For i = 1 To lTableCount
           Set objTable = objWordDocument.Tables(i)
           objTable.Range.Copy
 
           Set objExcelWorksheet = objExcelWorkbook.Sheets(i)
           objExcelWorksheet.Paste
           objExcelWorksheet.Columns.AutoFit
       Next
    Else
      'If there is only one table
      'Just copy it into the first worksheet
      Set objTable = objWordDocument.Tables(1)
      objTable.Range.Copy
 
      Set objExcelWorksheet = objExcelWorkbook.Sheets(1)
      objExcelWorksheet.Paste
      objExcelWorksheet.Columns.AutoFit
    End If
End Sub

解决方案

Once you read a "table", you will need to "extract" and reformat the contents as a row for your output.

(i.e. Your current "tables" are not in the same "layout" as your planned output).

c# - Parse table using Microsoft.Office.Interop.Word, get only text from first column? - Stack Overflow[^]

(Of course you can done for "multiple" columns).

这篇关于如何将Outlook电子邮件中的多个表导出到Excel（同一张表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！