VBA & Macro Problems

How to Fix VBA Error 5097 When Pasting Excel Data into Outlook

Algirdas Jasaitis Oct 9, 2026 870 views

Question details

The user needs to resolve run-time errors (5097 or 4198) that occur when running a VBA macro to copy an Excel range and paste it into an Outlook email.

Fixing VBA Error 5097 When Pasting Excel Data into Outlook

Product: Microsoft Excel and Outlook
Device & OS: not provided
Scenario: Automating the creation of Outlook emails with copied Excel data using the WordEditor and PasteAndFormat methods in VBA.
Observed behavior: The macro works successfully the first time it is run but throws error 5097 or 4198 on subsequent executions unless the Outlook application is restarted.

Before you start

Before modifying your script, ensure your VBA editor is closed on any actively running instances and that you have saved a backup of your current Excel macro workbook.

Solution 1Recommended

Initialize MailItem Display Before Calling WordEditor

Ensure the Outlook email message is fully displayed and initialized before using the Inspector and WordEditor objects to prevent timing and binding errors.

Calling WordEditor methods before the Outlook MailItem is fully loaded into the application's memory often causes error 5097 or 4198. Ensuring the item is displayed first allows the WordEditor to properly accept the PasteAndFormat command.

Additionally, failing to release the COM objects at the end of the script can cause the second run of the macro to crash.

Open the VBA Editor

Press Alt + F11 in Excel to open the Visual Basic for Applications (VBA) editor and locate your Outlook automation macro.

Reorder the Display Command

Find the line where you create the MailItem. Ensure you call the .Display method on this MailItem object before you set the GetInspector and WordEditor variables.

Clear Object Variables

At the very end of your macro, add cleanup code to release the objects from memory (e.g., Set objWordEditor = Nothing and Set objMail = Nothing).

Initialize MailItem Display Before Calling WordEditor

Avoid On Error Resume Next: Do not rely on 'On Error Resume Next' to bypass this issue. It will mask the failure, resulting in emails being created without the pasted Excel content.

Solution 2

Debug Object Creation with the F8 Step Tool

Use the step-through debugging tool to identify exactly which object fails to instantiate during the second execution of the macro.

Free Microsoft Office alternative

Experience Hassle-Free Spreadsheet Management with WPS Office

If you are tired of dealing with complex Microsoft Office COM automation errors and recurring VBA crashes, consider switching to WPS Office. It provides a lightweight, highly compatible, and user-friendly environment for all your spreadsheet and document needs without the heavy overhead.

1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
2. Install and Launch: Run the installer and follow the on-screen prompts. Once installed, open WPS Spreadsheet.
3. Open Your Workbooks: Open your existing Excel files directly in WPS Office to enjoy a smooth, familiar spreadsheet experience.

Completely free and lightweight Office suiteExcellent compatibility with Microsoft Excel formats (.xlsx, .xls, .csv)Familiar tabbed user interface makes migration seamless and easyStable environment for standard data processing and daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro work the first time but fail on the second run?

This typically occurs when application COM objects, such as the Outlook Inspector or WordEditor, are not properly released from memory after the first execution. The macro tries to reference a memory-locked or invalid object on the second run, leading to errors.

Can I use SendKeys as a workaround for pasting data?

While using SendKeys (like sending Ctrl+V) might temporarily bypass the WordEditor PasteAndFormat error, it is highly discouraged. SendKeys relies on window focus and can easily paste data into the wrong application if the user clicks away during the macro execution.

What does Error 4198 mean when automating Outlook from Excel?

Error 4198 'Command failed' indicates that the Word object model used inside the Outlook email body rejected your command. This usually means the email body isn't fully initialized or ready to accept the pasted Excel range, often fixed by displaying the email before assigning the WordEditor.

Excel & Data Problems