How to export Outlook email body text to an Excel spreadsheet?
Exporting email body text from Outlook into an Excel spreadsheet is a practical solution for users who need to further process, analyze, or archive the content of their emails. Common scenarios for this need include monitoring communication logs, analyzing sales leads, organizing customer inquiries, or compiling data-centric feedback from clients. This workflow transforms unstructured email content into structured Excel data, making it easier to filter, search, and analyze key information over time.
This tutorial explores several methods tailored to different levels of technical skill and batch size requirements. You will find both code-based and user-friendly tools for manual and automated extraction. The options include a manual VBA macro for tailored, single-message exports, the Save Messages as Other Files feature in Kutools for Outlook for high-volume and flexible saving, strategies for parsing and analyzing exported data directly within Excel using formulas, and automated data import via Excel’s Get & Transform (Power Query) tool.
Table of Contents:
- Exporting Email Body Text to Excel with VBA
- Exporting Email Body Text to Excel in Bulk with Kutools for Outlook
Supports exporting multiple messages and saving them as PDF, Excel, HTML, or other file formats. - Import Outlook Data into Excel with Get & Transform (Power Query)
Exporting Email Body Text to Excel with VBA
Using VBA provides a manual and direct way to export the content of an Outlook email body to an Excel spreadsheet. This approach is recommended for users who need customized exporting or do not have third-party add-ins installed. The VBA method is ideal for one-time or occasional exports and allows for further tweaking of what data and formatting are included.
Typical application scenarios: This method is best suited when you want to extract and save the body text of a single message or a select set of emails. It can be especially useful if you only occasionally need to process such exports or want more granular control over the exact body content that gets transferred.
Advantages: Complete control and customization, does not require any add-ins.
Disadvantages: Manual operation for each email, requires basic VBA knowledge, and is less suitable for bulk exports.
To use this method, follow these steps carefully:
1. Open the target email and manually select (highlight) the email body content you wish to export to Excel. After selection, press the Alt + F11 keys at the same time—this opens the Microsoft Visual Basic for Applications (VBA) editor window.
2. Within the VBA editor, navigate to the top menu bar, click Insert, then select Module from the dropdown menu. In the Module window that appears, paste the following VBA code.
VBA code: export Outlook email body text to Excel spreadsheet
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 SubNote: In the code, the value “Email body.xlsx” is the default name for the saved workbook. You can rename this as needed to better fit the context or date—for example, “Leads_June2024.xlsx”. Make sure the filename does not conflict with existing files in your chosen save directory.
3. Before running the code, click Tools > References in the VBA editor’s menu to bring up the References – Project dialog. Make sure to check the boxes for both Microsoft Excel Object Library and Microsoft Word Object Library. This ensures your macro can interface with Excel and Word objects as required by the script.
4. Run the code by pressing the F5 key while the cursor is inside your code. A Browse For Folder dialog will appear; this lets you select a specific directory or folder on your computer to save the generated Excel file. Once selected, click OK to continue.
When the macro finishes, you will find a new Excel file named as you specified (e.g., “Email body.xlsx”) in the chosen folder. Open this workbook, and you’ll see the selected email body text exported in Sheet1.
Tips and precautions:
- Always ensure only the relevant body content is selected before running the macro to avoid unwanted data export.
- If you receive a security warning about macros in Outlook or Excel, make sure to enable macros temporarily; remember to disable them afterward for safety.
- If the VBA code fails to execute, double-check that you have referenced the required Excel and Word object libraries.
- If the output Excel file does not appear, verify that you have sufficient permissions to write in the selected folder.
Troubleshooting:
- Error messages related to object libraries often mean a reference has not been enabled.
- If you get a “subscript out of range” error, ensure you have selected an email and highlighted the correct text.
- Incompatible file names (with forbidden characters like slashes) can prevent file creation.
To avoid these issues, follow each step carefully and always start with a test export.
AI Mail Assistant in Outlook: Smarter Replies, Clearer Communication (one-click magic!)
Streamline your daily Outlook tasks with the AI Mail Assistant from Kutools for Outlook. This powerful tool learns from your past emails to offer intelligent and accurate responses, optimize your email content, and help you draft and refine messages effortlessly.
This feature supports:
- Smart Replies: Get responses crafted from your past conversations—tailored, precise, and ready to go.
- Enhanced Content: Automatically refine your email text for clarity and impact.
- Effortless Composition: Just provide keywords, and let AI handle the rest, with multiple writing styles.
- Intelligent Extensions: Expand your thoughts with context-aware suggestions.
- Summarization: Get concise overviews of long emails instantly.
- Global Reach: Translate your emails into any language with ease.
This feature supports:
- Smart email replies
- Optimized content
- Keyword-based drafts
- Intelligent content extension
- Email summarization
- Multi-language translation
Don’t wait—download AI Mail Assistant now and enjoy!
Exporting Email Body Text to Excel in Bulk with Kutools for Outlook
If you need to export the body text of multiple Outlook emails at once, Kutools for Outlook provides a streamlined and efficient batch export feature called Save Messages as Other Files. This approach is ideal for those managing high daily email traffic or needing to compile communications from various team members, departments, or clients into a single, searchable format.
After installing Kutools for Outlook, please follow the steps below for efficient mass export:
Step 1. Select all messages you want to export by holding Ctrl or Shift while clicking on individual or continuous emails within your Outlook list view. For large selections, you may use the folder pane or advanced search to select filtered results efficiently.
Step 2. From the main Outlook interface, go to the Kutools tab, then choose Bulk Processing > Save Selected Emails as Files in Various Formats like PDF.
Step 3. In the Save Messages as Other Files dialog, configure your export:
Select the target folder where all exported Excel files will be saved.
Tick the Excel format to export the files as .xlsx. This is suitable for later consolidation, bulk data processing, or direct import into databases.
Choose Body only, so that supplementary details such as subject, sender, or attachments are excluded—ideal for focused content analysis.
Click OK to proceed and initiate export.
The tool will quickly create separate Excel files for each selected email, saving the body content as needed. For high-volume scenarios or regular batch exports, this method reduces manual effort and potential errors compared to copying and pasting messages one by one.
Tips and notes:
- You can select multiple formats and fields in addition to the body, such as subject or attachment details.
- Make sure not to select too many emails if your computer’s memory is limited, as the export process could temporarily increase usage.
- If you have naming conventions or need to merge multiple Excel files into a master workbook, consider using Excel’s “Consolidate” feature or scripts.
Troubleshooting:
- If the export dialog does not appear, verify the Kutools add-in is enabled and properly licensed.
- For files not appearing in the chosen directory, check for pop-up permissions or antivirus settings that may block write access.
- For unexpected formatting, use Excel’s Text-to-Columns feature to reformat data, or unmerge cells if present.
Import Outlook Data into Excel with Get & Transform (Power Query)
Excel’s Get & Transform (formerly known as Power Query) allows direct importation of Outlook emails, making it a powerful solution for advanced users who routinely analyze or archive large sets of messages. Using this native integration, you can automate the extraction and transformation of email body text and other fields, thus eliminating repetitive manual steps.
When to use: Ideal for recurring sync between Outlook folders and Excel, complex filtering, or large mailboxes where simple copy-paste and manual extraction would be inefficient.
Advantages: Automated workflows, repeatable queries, support for filtering and shaping large volumes of data.
Disadvantages: Initial setup may be complex for novice users, limited to desktop (not available online), and relies on Outlook being installed locally.
How to use Get & Transform to extract email body text from Outlook:
1. In Excel, go to the Data tab. Click Get Data (or Get External Data in some older versions), then choose From Other Sources > From Microsoft Exchange or From Outlook (the exact option may vary by Excel version).
2. Connect to your Outlook profile by following the on-screen prompts and allowing the necessary permissions.
3. In the Navigator pane, expand Mailbox and select the Inbox or other relevant folder.
4. Preview your messages and select only columns you need—especially Body, Subject, Sender, and Received Date.
5. Click Load to import these fields directly into a new Excel worksheet. The email body text will be available and can be further processed using Excel features or Power Query’s transformation tools.
6. Save the query for easy refresh in future sessions—Excel will update imported data with new emails on demand.
Practical tips and caveats:
- You may need to filter out meeting requests or calendar items that appear along with emails.
- If you don’t see the “From Outlook” option, ensure your Outlook desktop client is installed and active on the same machine.
- Power Query lets you filter, pivot, and split data during import—ideal for bulk formatting or advanced reporting.
Troubleshooting:
- If Power Query fails to connect, close and restart both Outlook and Excel before retrying.
- Ensure that the mailbox/folder is not restricted or overly large to avoid timeouts.
- If the email body appears as HTML code, use Power Query’s transformation tools to parse or clean markup as needed.
This approach is well-suited for teams that need regular synchronization or complex workflow automation beyond basic exports.
Best Office Productivity Tools
Experience the all-new Kutools for Outlook with 100+ incredible features! Click to download now!
📧 Email Automation: Auto Reply (Available for POP and IMAP) / Schedule Send Emails / Auto CC/BCC by Rules When Sending Email / Auto Forward (Advanced Rules) / Auto Add Greeting / Automatically Split Multi-Recipient Emails into Individual Messages ...
📨 Email Management: Recall Emails / Block Scam Emails by Subjects and Others / Delete Duplicate Emails / Advanced Search / Consolidate Folders ...
📁 Attachments Pro: Batch Save / Batch Detach / Batch Compress / Auto Save / Auto Detach / Auto Compress ...
🌟 Interface Magic: 😊More Pretty and Cool Emojis / Remind you when important emails come / Minimize Outlook Instead of Closing ...
👍 One-click Wonders: Reply All with Attachments / Anti-Phishing Emails / 🕘Show Sender's Time Zone ...
👩🏼🤝👩🏻 Contacts & Calendar: Batch Add Contacts From Selected Emails / Split a Contact Group to Individual Groups / Remove Birthday Reminders ...
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!
🚀 One-Click Download — Get All Office Add-ins
Strongly Recommended: Kutools for Office (5-in-1)
One click to download five installers at once — Kutools for Excel, Outlook, Word, PowerPoint and Office Tab Pro. Click to download now!
- ✅ One-click convenience: Download all five setup packages in a single action.
- 🚀 Ready for any Office task: Install the add-ins you need, when you need them.
- 🧰 Included: Kutools for Excel / Kutools for Outlook / Kutools for Word / Office Tab Pro / Kutools for PowerPoint