Automate Outlook Email Back-Up with VBA

Generate emails backup in HTML format that can be read without Outlook Account

Need Help?

Photo by Markus Winkler / Unsplash

What happens if you want to keep records of several thousand emails and open them later without depending on Outlook?

💌 New articles straight in your inbox for free: Newsletter

Why would you need such a tool?

How are you using Outlook?

You use Outlook daily to handle a large number of emails and

You do not have time to invest in cleaning your records frequently
You want to keep records of your emails that can be read without Outlook
You believe that Outlook solutions for backup management are too time-consuming
You want to perform NLP Analysis on a large number of emails

A screenshot of the Outlook folder menu, showing the option to create a new folder. The context menu is opened under the “Inbox” folder, and the “New Folder” option is highlighted. This image — Folder Organization in Outlook — (Image by Author)

This tool will answer your needs by

Keeping records of your emails in an Excel file, including
Sender Name, Sender Address, Receiving Time, Email Type, Subject, Body (Content of your email)
Saving a copy of each email in HTML format that can be opened with your browser
Saving email attachments in a separate folder

Prerequisite

You do not need any prior knowledge of Visual Basic for Applications (VBA) to follow this tutorial.

Each step is detailed and the full code can be found in my Github repository.

How does it work?

This solution will be an Excel-VBA file with your macro that will perform the information extraction from your email files and keep records in an Excel sheet.

Step 1: Copy your emails to a folder (Folder In)

An Outlook inbox displaying a list of received emails with details such as the sender, subject, and received date. Some emails are marked with icons such as a paperclip (indicating attachment — Select your emails in Outlook GUI — (Image by Author)

Select all the emails and drag the selection to a folder (Folder In).

A folder containing several .msg files, representing individual email messages exported from Outlook. The folder displays the email file names, modified dates, types (Outlook items), and size — Drag your emails in a folder — (Image by Author)

These emails are stored in (.msg) format in this folder that will call (Folder In).

Step 2: Run the Excel VBA Macro

An Excel window with the “View” tab open, showing options for managing macros. The “View Macros” button is highlighted, indicating that the user can view and manage macros from this interface — Run Excel Macros (Menu) — (Image by Author)

Click on Macros -> View Macros -> Select SaveMSG_as_HTML

A macro window in Excel showing a macro named “SaveMSG_as_HTML” selected, with options to run, edit, or delete the macro. The window is part of the VBA interface in Excel, where users can aut — Run Button — (Image by Author)

Click on Run Button

Step 3: Results

A spreadsheet in Excel displaying a list of email records with columns for sender, subject, receiving time, and file names. The content is blurred for privacy, but the structure shows how ema — Records of email with detailed information (ID, Sender, Address, ..) [Confidential Information Blurred]— (Image by Author)

Email Records with detailed information including Sender Name and Address, Subject, Body and attachment filename.

A folder showing a list of HTML files generated from Outlook emails, with file names indicating different emails (e.g., file 1, file 2, etc.). The file sizes are listed in kilobytes. These HT — Emails copies in HTML format — (Image by Author)

Emails in HTML format: file n refers to the email n in your Excel File

A folder view showing email attachments saved from Outlook emails. The folder contains various file types such as Excel, Word, PowerPoint, and PDF documents, each named with a combination of — Attachments records with ID — (Image by Author)

Attachment records: ID{N}-AttachmentName refers to the attachment of email number N with the original filename AttachmentName

Build your tool

Step 1: Activate Macros on Excel

To be able to write and run macros you need to active VBA Macros in Excel.

A screenshot of Excel’s Quick Access Toolbar menu, showing the “More Commands” option. The background includes an open Excel sheet displaying email records, with columns for sender name, emai — More Commands menu to activate macros— (Image by Author)

Go to the Options tab and click on More Commands

A screenshot of Excel’s “Add-ins” window, showing various available add-ins, including “Analysis ToolPak” and “Analysis ToolPak — VBA,” both selected. This image highlights the process of ena — Analysis ToolPak — VBA to activate Macros — (Image by Author)

Select Add-Ins -> Click on Go -> Select Analysis ToolPak -VBA

Step 2: Create your Macro

Click on
View Tab -> Select Macros -> View Macros -> Name it: SaveMSG_as_HTML

A screenshot of the “Macro” window in Excel, where users can create or run macros. The window is blank, prompting users to input a macro name. The Excel interface in the background shows an e — Input a Macro name — (Image by Author)

Step 3: Initialize variables

strIn: copy the path where you pasted your emails (.msg)
strOut: the path where you want to save emails in HTML format and the associated attachments

Step 4: Write the loop

Step 5: Run the Macro

🔗

The full code can be found on this gist (Link).

Next Steps

💡

If you have any question, feel free to ask it here: Ask a Question

Now that you have records of all your emails in a single Excel sheet

Perform analysis: frequency of email exchanges, NLP and sentiment analysis, most frequent sender, keywords used […]
Build an automation tool to improve your productivity: Minute of Meetings Collection, Attachments Consolidation (for instance, Excel reports sent daily)

About Me

Let’s connect on LinkedIn and Twitter, I am a Supply Chain Engineer that is using data analytics to improve logistics operations and reduce costs.

If you’re looking for tailored consulting solutions to optimise your supply chain and meet sustainability goals, please contact me.