How to Quickly Export All Tables from an Outlook Email to an Excel Workbook via VBA

If you want to quickly export the tables from an Outlook email to an Excel file, you can utilize the method introduced in this article. It will use VBA to help you achieve this just via one click.

As we all know, Excel is designed for tables and data statistics. Thus, when you receive an email that contains several tables in the body, you may wish to export all these tables to an Excel file so as to better analyze the data in the tables. Hence, here we will teach you how to quickly export all tables from an email to a new Excel workbook. Now please read on to get the VBA code and detailed steps.

Export All Tables from an Outlook Email to an Excel Workbook

1. To begin with, start your Outlook application as usual.
2. Then in the main Outlook window, press “Alt + F11” key buttons.
3. Next in the following “Microsoft Visual Basic for Applications” window, you should open an empty module.
4. Subsequently, you need to copy the VBA code below into this module.

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

5. After that, you need to add the new macro to Quick Access Toolbar.
6. Later you should alter your Outlook macro security level to low.
7. Ultimately, you could have a try.

• Firstly, select a source email in the email list.
• Then click the macro button in the Quick Access Toolbar.
• At once, a new Excel file will display.
• If there is only one table in the email, this table will be exported into the first worksheet of the Excel file, like the following screenshot:
• But if there is more than one table in the email, every table will be copied into individual worksheet.

Get Rid of Miserable PST Data Loss

Outlook is prone to errors and corruption. Therefore, it is a tedious and arduous task to safeguard your PST data and avert painful PST data loss. Firstly, you need to back up your PST files regularly. Moreover, you have to prepare a potent PST fix utility, like DataNumen Outlook Repair. It can rescue you from all kinds of PST troubles with effortless ease.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupted sql and outlook repair software products. For more information visit www.datanumen.com