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
- To begin with, start your Outlook application as usual.
- Then in the main Outlook window, press “Alt + F11” key buttons.
- Next in the following “Microsoft Visual Basic for Applications” window, you should open an empty module.
- 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
- After that, you need to add the new macro to Quick Access Toolbar.
- Later you should alter your Outlook macro security level to low.
- 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.
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