Click here to Skip to main content
13,701,931 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
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 crm@crm.com
______________________________________________
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 crm@crm.com 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
Posted 13-Jun-18 4:26am
Updated 13-Jun-18 8:29am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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).
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04-2016 | 2.8.180906.1 | Last Updated 13 Jun 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100