Productivity

Automate Outlook Email Back-Up with VBA

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

Article originally published on Medium.

What happens if you want to keep records of several thousands of 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

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 Application (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)

Select your emails in Outlook GUI — (Image by Author)

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

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

Run Excel Macros (Menu) — (Image by Author)

Click on Macros -> View Macros -> Select SaveMSG_as_HTML

Click on Run Button

Step 3: Results

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.

Emails copies in HTML format — (Image by Author)

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

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.

More Commands menu to activate macros— (Image by Author)

Go to the Options tab and click on More Commands

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

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

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

Perform analysis: frequency of emails exchange, NLP and sentiment analysis, most frequent sender, keywords used […]
Build 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 optimize your supply chain and meet sustainability goals, please contact me.