Excel Tips and Tricks: How to Extract Emails and Email Addresses from Outlook Email Body

I fell into a situation where I wanted to extract email addresses from my outlook. The challenge was that the emails were embedded within the body of the Emails. 

I searched through many sites with titles like

"Can you export a list of emails from Outlook?"

"Can Excel pull data from emails?"

"How do I extract all emails from Outlook?"

"How to extract emails from Outlook to Excel?"

This blog post will guide you through a powerful VBA script that allows you to extract emails and email addresses from your Outlook inbox directly into an Excel spreadsheet. Whether you're a marketer building a contact list, a researcher collecting data, or simply trying to organize your communications, this tool will save you hours of manual work.

The Problem

Manually copying and pasting email details and searching for email addresses within email bodies is time-consuming and error-prone. What if you could automate this process with just a few clicks?

The Solution

We've developed a VBA script that does the following:

1. Connects to your Outlook account
2. Allows you to select a specific email folder
3. Extracts key information from emails (received time, sender, subject, body)
4. Identifies and extracts up to three email addresses from the body of each email
5. Organizes all this information neatly into an Excel spreadsheet

The Code

Let's break down the script and explain each part:

vbscript
Sub ExtractMailsFromFolder()
    Dim OutApp As New Outlook.Application
    Dim OutNamespace As Namespace, OutFolder As MAPIFolder, OutMail As Object
    Dim r As Integer, cutDate As Date
    Dim regEx As Object, matches As Object, match As Object
    Dim emailAddresses() As String, emailCount As Integer
    Dim accountIndex As Integer, i As Integer
    Dim accounts() As String
    Dim userInput As String

This section declares the variables we'll use throughout the script. We're setting up connections to Outlook, creating objects to handle regular expressions for email extraction, and preparing variables to store our data.

vbscript
    Set OutNamespace = OutApp.GetNamespace("MAPI")
    
    ' List all accounts
    ReDim accounts(OutNamespace.Folders.Count - 1)
    For i = 1 To OutNamespace.Folders.Count
        accounts(i - 1) = OutNamespace.Folders(i).Name
        Debug.Print i & ". " & accounts(i - 1)
    Next i

Here, we're connecting to Outlook and listing all the email accounts you have set up. This allows you to choose which account you want to extract emails from.

vbscript
    ' Ask user to select an account
    userInput = InputBox("Enter the number of the account you want to use:" & vbNewLine & _
                            Join(accounts, vbNewLine), "Select Account")
    
    ' Convert user input to a number and validate
    If userInput = "" Then
        MsgBox "No input provided. Exiting script."
        Exit Sub
    End If
    
    accountIndex = CInt(userInput)
    
    If accountIndex < 1 Or accountIndex > UBound(accounts) + 1 Then
        MsgBox "Invalid selection. Exiting script."
        Exit Sub
    End If

This section creates a pop-up box asking you to select which account you want to use. It then validates your input to make sure it's a valid selection.

vbscript
    ' Set the selected account and folder
    Set OutFolder = OutNamespace.Folders(accounts(accountIndex - 1)).Folders("Inbox")
    
    ' Check if the folder exists
    If OutFolder Is Nothing Then
        MsgBox "The folder 'Inbox' was not found in the selected account. Please check the folder name and try again."
        Exit Sub
    End If

Here, we're selecting the specific folder you want to extract emails from. In this case, it's looking for a folder named "Inbox". If the folder doesn't exist, the script will show an error message and stop.

vbscript
    cutDate = DateSerial(Year(Date) - 1, Month(Date), Day(Date))  ' Set to one year ago
    
    ' Create a regular expression object for email matching
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Pattern = "[\w-\.]+@([\w-]+\.)+[\w-]{2,4}"
    regEx.Global = True

This part sets up the date range for emails (in this case, it's set to extract emails from the last year) and creates a regular expression object to identify email addresses in the body of the emails.

vbscript
    r = 1 ' Start from the first row
    
    ' Add headers
    Range("A" & r).Value = "Received Time"
    Range("B" & r).Value = "Sender Name"
    Range("C" & r).Value = "Subject"
    Range("D" & r).Value = "Body"
    Range("E" & r).Value = "Email 1"
    Range("F" & r).Value = "Email 2"
    Range("G" & r).Value = "Email 3"

This section sets up the headers in your Excel spreadsheet, preparing it for the data we're about to extract.

vbscript
    On Error Resume Next
    For Each OutMail In OutFolder.Items
        If OutMail.ReceivedTime >= cutDate Then
            r = r + 1
            Range("A" & r).Value = OutMail.ReceivedTime
            Range("B" & r).Value = OutMail.SenderName
            Range("C" & r).Value = OutMail.Subject
            Range("D" & r).Value = OutMail.Body
            
            ' Extract email addresses from the body
            Set matches = regEx.Execute(OutMail.Body)
            emailCount = 0
            ReDim emailAddresses(matches.Count - 1)
            
            For Each match In matches
                emailAddresses(emailCount) = match.Value
                emailCount = emailCount + 1
            Next match
            
            ' Write extracted email addresses to separate columns
            For i = 0 To UBound(emailAddresses)
                If i <= 2 Then ' Limit to 3 email addresses, adjust if needed
                    Cells(r, 5 + i).Value = emailAddresses(i)
                Else
                    Exit For
                End If
            Next i
        End If
    Next OutMail
    On Error GoTo 0

This is the heart of the script. It loops through each email in the selected folder, extracts the relevant information, searches for email addresses in the body, and writes all this data to the Excel spreadsheet.

vbscript
    If r = 1 Then
        MsgBox "No emails were found in the specified date range. Please check the cutoff date and try again."
    Else
        MsgBox "Extraction complete. " & (r - 1) & " emails were processed."
    End If
    
    Set OutFolder = Nothing
    Set OutNamespace = Nothing
    Set OutApp = Nothing
    Set regEx = Nothing
End Sub

Finally, this section provides feedback on how many emails were processed and cleans up the objects we created to free up memory.

How to Use the Script

1. Open Microsoft Excel and press Alt + F11 to open the Visual Basic Editor.
2. Insert a new module and paste the entire script into it.
3. Make sure you have the Microsoft Outlook object library referenced in your VBA project.
4. Adjust the folder name "Inbox" in the script if your target folder has a different name.
5. Run the script. You'll be prompted to select an email account.
6. The script will process the emails and populate your Excel sheet with the extracted data.

Conclusion

This VBA script offers a streamlined solution to extract email data and addresses from Outlook into Excel, saving you countless hours of manual work. By automating the extraction of key details like received time, sender information, and embedded email addresses, you can focus on higher-value tasks like building contact lists, analyzing communication patterns, or managing your business operations more efficiently. With just a few clicks, this powerful tool helps organize your inbox and unlock insights buried in your emails.

If you’re looking to optimize your business operations, marketing strategies, or digital presence, consider reaching out to Shoeb Lodhi, an expert consultant with over 17 years of experience in IT, operations, digital marketing, and business development. Shoeb has helped businesses of all sizes enhance their processes and drive growth. You can learn more about his services at shoeblodhi.com.

Additionally, Vantage Market & Digital Solutions LLC (VMarket Digital) provides comprehensive digital solutions, from website and mobile app development to CRM systems and AI-driven automation. Whether you’re aiming to scale your business or streamline operations, VMarket Digital has the expertise to support your goals. Visit https://vmarket.digital to explore how their services can help elevate your business.

Happy extracting!