How to replace text in the body of an Outlook email message

How to Replace Text in Outlook Email Body using VBA

Learn to automate text replacement within Outlook email messages using VBA, enhancing efficiency for repetitive tasks and template customization.

Automating tasks in Microsoft Outlook can significantly boost productivity, especially when dealing with repetitive email content. One common requirement is to replace specific text strings within the body of an email message, whether it's a new message, a reply, or a forwarded email. This article will guide you through using Visual Basic for Applications (VBA) to achieve this, providing practical code examples and a clear understanding of the process.

Understanding the Outlook Object Model for Email Manipulation

Before diving into the code, it's crucial to understand how Outlook structures its objects. An email message is represented by the MailItem object. The body of the email can be accessed in two primary formats: plain text (Body) or HTML (HTMLBody). The choice between these depends on the complexity of your email content and whether you need to preserve formatting.

• MailItem.Body: This property returns or sets the plain text body of the Outlook item. It's simpler to work with if you don't need rich formatting.
• MailItem.HTMLBody: This property returns or sets the HTML body of the Outlook item. It's more powerful for maintaining formatting, but requires careful handling of HTML tags when performing replacements.

flowchart TD
    A[Start VBA Macro] --> B{Get Active Inspector/Explorer};
    B -- If Inspector --> C[Get Current MailItem];
    B -- If Explorer --> D[Get Selected MailItem];
    C --> E{Check MailItem Type};
    D --> E;
    E -- Is MailItem --> F{Determine Body Format (HTML/Plain)};
    F -- HTML Body --> G[Get HTMLBody Content];
    F -- Plain Body --> H[Get Body Content];
    G --> I[Perform HTML Text Replacement];
    H --> J[Perform Plain Text Replacement];
    I --> K[Update HTMLBody];
    J --> L[Update Body];
    K --> M[Display/Save MailItem];
    L --> M;
    M --> N[End Macro];

Flowchart illustrating the VBA process for replacing text in an Outlook email.

Implementing Text Replacement in Plain Text Emails

For emails that are primarily plain text or where formatting is not a concern, using the MailItem.Body property is the most straightforward approach. The VBA Replace function is ideal for this task. It allows you to find all occurrences of a substring within a string and replace them with another substring.

Sub ReplaceTextInPlainTextEmail()
    Dim olApp As Outlook.Application
    Dim olInsp As Outlook.Inspector
    Dim olMail As Outlook.MailItem
    Dim strFind As String
    Dim strReplace As String

    Set olApp = Outlook.Application
    Set olInsp = olApp.ActiveInspector

    If Not olInsp Is Nothing Then
        If olInsp.CurrentItem.Class = olMail Then
            Set olMail = olInsp.CurrentItem

            strFind = "[Placeholder]"
            strReplace = "Actual Value"

            ' Perform replacement in the plain text body
            olMail.Body = Replace(olMail.Body, strFind, strReplace, 1, -1, vbTextCompare)

            Set olMail = Nothing
        End If
    End If

    Set olInsp = Nothing
    Set olApp = Nothing
End Sub

VBA code to replace text in the plain text body of the currently open email.

Implementing Text Replacement in HTML Emails

When dealing with HTML-formatted emails, you need to use the MailItem.HTMLBody property. This requires a bit more caution, as you're manipulating HTML code. Directly replacing text might inadvertently break HTML tags if your strFind string happens to be part of a tag attribute or content. However, for simple text replacements that are not part of HTML structure, the Replace function still works effectively.

Sub ReplaceTextInHTMLEmail()
    Dim olApp As Outlook.Application
    Dim olInsp As Outlook.Inspector
    Dim olMail As Outlook.MailItem
    Dim strFind As String
    Dim strReplace As String

    Set olApp = Outlook.Application
    Set olInsp = olApp.ActiveInspector

    If Not olInsp Is Nothing Then
        If olInsp.CurrentItem.Class = olMail Then
            Set olMail = olInsp.CurrentItem

            strFind = "[Your Company Name]"
            strReplace = "Acme Corp."

            ' Perform replacement in the HTML body
            olMail.HTMLBody = Replace(olMail.HTMLBody, strFind, strReplace, 1, -1, vbTextCompare)

            Set olMail = Nothing
        End If
    End If

    Set olInsp = Nothing
    Set olApp = Nothing
End Sub

VBA code to replace text in the HTML body of the currently open email.

Replacing Text in Selected Emails from the Explorer Window

The previous examples focused on the currently open email in an Inspector window. You might also want to replace text in emails selected directly from the Outlook Explorer (the main Outlook window showing your inbox, etc.). This requires iterating through the Selection object.

Sub ReplaceTextInSelectedEmails()
    Dim olApp As Outlook.Application
    Dim olExp As Outlook.Explorer
    Dim olSel As Outlook.Selection
    Dim olItem As Object
    Dim olMail As Outlook.MailItem
    Dim strFind As String
    Dim strReplace As String

    Set olApp = Outlook.Application
    Set olExp = olApp.ActiveExplorer
    Set olSel = olExp.Selection

    strFind = "[Old Reference]"
    strReplace = "New Reference"

    For Each olItem In olSel
        If olItem.Class = olMail Then
            Set olMail = olItem

            ' Check if the email has an HTML body or plain text body
            If Not IsEmpty(olMail.HTMLBody) And Len(olMail.HTMLBody) > 0 Then
                olMail.HTMLBody = Replace(olMail.HTMLBody, strFind, strReplace, 1, -1, vbTextCompare)
            Else
                olMail.Body = Replace(olMail.Body, strFind, strReplace, 1, -1, vbTextCompare)
            End If

            ' Save changes if the email is not a new unsaved item
            If olMail.Saved = False Then
                olMail.Save
            End If
        End If
    Next olItem

    Set olSel = Nothing
    Set olExp = Nothing
    Set olApp = Nothing
End Sub

VBA code to replace text in selected emails from the Outlook Explorer window.

1. Open the VBA Editor

In Outlook, press Alt + F11 to open the VBA editor (Microsoft Visual Basic for Applications).

2. Insert a New Module

In the VBA editor, navigate to Project1 (or VBAProject.OTM), right-click on Microsoft Outlook Objects, then choose Insert > Module.

3. Paste the Code

Copy one of the provided VBA code examples (e.g., ReplaceTextInPlainTextEmail or ReplaceTextInHTMLEmail) and paste it into the newly created module.

4. Customize strFind and strReplace

Modify the strFind variable to the text you want to locate and strReplace to the text you want to insert. Ensure these match your specific needs.

5. Run the Macro

Place your cursor anywhere within the pasted subroutine. Press F5 to run the macro. If you are working with an open email, ensure it is the active window. If working with selected emails, ensure they are selected in the Explorer window.

6. Save the Project (Optional)

If you want to keep the macro for future use, save the VBA project by going to File > Save VBAProject.OTM. This will make the macro available across Outlook sessions.