Extract Email Addresses from Text with Excel VBA

This solution will use Regular Expressions (RegEx) to identify and extract email addresses from a given string.

Explanation:

In VBA, you can use Regular Expressions to search for patterns in a string. An email address has a standard format, such as username@domain.com, and Regular Expressions are perfect for matching this pattern.

In this example, we will create a VBA function that:

1. Accepts a block of text as input.
2. Searches the text for email addresses using a Regular Expression.
3. Extracts all valid email addresses found in the text.
4. Returns a list of these email addresses.

Prerequisites:

• You need to enable the Microsoft VBScript Regular Expressions 5.5 reference in Excel VBA. To do this:
  1. In the VBA editor, go to Tools → References.
  2. Scroll down and check Microsoft VBScript Regular Expressions 5.5.
  3. Click OK.

Now, let’s break down the code.

VBA Code: Extract Email Addresses from Text

Option Explicit
' This function will extract all email addresses from the provided text.
Function ExtractEmails(inputText As String) As String
    Dim regEx As Object
    Dim matches As Object
    Dim match As Variant
    Dim emailList As String
    Dim emailPattern As String
    ' Define the pattern for a basic email address
    emailPattern = "([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4})"
    ' Create a RegExp object
    Set regEx = CreateObject("VBScript.RegExp")
    ' Set RegExp properties
    regEx.IgnoreCase = True       ' Case-insensitive matching
    regEx.Global = True           ' Find all matches in the input text
    regEx.Pattern = emailPattern  ' Set the regular expression pattern   
    ' Execute the regular expression on the input text
    Set matches = regEx.Execute(inputText)   
    ' Initialize an empty string to store the results
    emailList = ""   
    ' Loop through all matches and append them to the result string
    For Each match In matches
        emailList = emailList & match.Value & vbCrLf
    Next match   
    ' Return the email addresses as a string
    If Len(emailList) > 0 Then
        ' Remove the last line break for neatness
        emailList = Left(emailList, Len(emailList) - 2)
    End If  
    ExtractEmails = emailList ' Return the list of emails
      ' Clean up
    Set regEx = Nothing
    Set matches = Nothing
End Function

' Test the function
Sub TestExtractEmails()
    Dim textToSearch As String
    Dim extractedEmails As String   
    ' Sample input text containing emails
    textToSearch = "Here are some emails: john.doe@example.com, jane_doe@domain.co.uk, and test123@xyz.org."   
    ' Call the function to extract emails
    extractedEmails = ExtractEmails(textToSearch)   
    ' Display the extracted emails in the Immediate window (Ctrl + G)
    Debug.Print extractedEmails
End Sub

Code Explanation:

1. Regular Expression Pattern:
   The regular expression used here is designed to match email addresses:
2. emailPattern = « ([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}) »
   • [a-zA-Z0-9._%+-]: Matches any alphanumeric character and some special characters (period, underscore, percentage, plus, and hyphen).
   • +: Ensures that the previous set of characters appears at least once.
   • @: Matches the literal @ symbol.
   • [a-zA-Z0-9.-]: Matches the domain name part, which can include letters, numbers, periods, and hyphens.
   • \.: Matches the literal period . (dot).
   • [a-zA-Z]{2,4}: Matches the top-level domain (TLD), such as .com, .org, etc. This ensures the TLD is at least two characters long and no more than four characters.
3. Creating the RegExp Object:
   We create a RegExp object and configure it:

Set regEx = CreateObject(« VBScript.RegExp »)

regEx.IgnoreCase = True    ‘ Ignore case when matching (e.g., ‘example.com’ and ‘Example.com’ are treated the same)

regEx.Global = True        ‘ This allows finding all matches in the text, not just the first one

regEx.Pattern = emailPattern ‘ Set the regular expression pattern

4. Executing the Regular Expression:
The Execute method runs the regular expression on the provided inputText. It returns all the matches found in the text:

Set matches = regEx.Execute(inputText)

5. Building the Result:
We loop through the matches collection, which contains all the found email addresses, and append them to the emailList string:

For Each match In matches

    emailList = emailList & match.Value & vbCrLf

Next match

Each email address is separated by a new line (vbCrLf).

6. Returning the Extracted Emails:

After looping through all matches, the list of email addresses is returned as a string. We also remove the trailing newline at the end of the list for neatness:

If Len(emailList) > 0 Then

    emailList = Left(emailList, Len(emailList) – 2)

End If

Testing the Function:

In the TestExtractEmails subroutine, we provide a sample text string containing email addresses. The ExtractEmails function is called, and the result is printed in the Immediate window:

Debug.Print extractedEmails

Example Output:

For the sample text:

Here are some emails: john.doe@example.com, jane_doe@domain.co.uk, and test123@xyz.org.

The output in the Immediate window would be:

john.doe@example.com

jane_doe@domain.co.uk

test123@xyz.org

Notes:

• Customizing the Email Pattern: You can modify the regular expression to match more complex email patterns, if necessary. For example, you might want to allow email addresses with longer top-level domains, such as .photography or .technology.
• Performance Considerations: This approach should work efficiently for typical text inputs, but for very large text blocks or very frequent use, performance might degrade. Consider optimizing further if needed.