VBA & Macro Problems

Fix Excel VBA Error 424 in Outlook Email Automation

Steven ParkerSteven Parker Nov 18, 2026 868 views

Question details

The user needs to fix Excel VBA errors when a macro creates an Outlook email and reads worksheet ranges.

Fix Excel VBA Error 424 in Outlook Email Automation
Product
Microsoft Excel VBA and Microsoft Outlook
Device & OS
not provided
Scenario
An Excel macro uses a worksheet range and an Outlook MailItem to prepare an email.
Observed behavior
The macro reports run-time error 424, Object required. After a range assignment is corrected, it reports error 1004, Application-defined or object-defined error.
Before you start

Open the workbook and identify the worksheet and cell or named range that the macro should use for rngSupplierMPID. For Outlook automation, check that desktop Outlook is installed and configured on the Windows computer running the macro.

Solution 1Recommended

Qualify the Excel range and initialize the Outlook MailItem

Use this approach to correct invalid worksheet references and ensure an Outlook email object exists before setting its body.

1
Find the failing statement

In Excel on Windows, press Alt+F11 to open the VBA editor. Run the macro and select Debug when the error appears; inspect the highlighted line and the objects it uses.

2
Qualify the worksheet and range

In the VBA editor, assign the worksheet and range explicitly, for example: Set ws = ThisWorkbook.Worksheets("Sheet1") followed by Set rngSupplierMPID = ws.Range("B2"). Replace Sheet1 and B2 with the actual worksheet and cell or range. If the data is in another workbook, reference that workbook instead of ThisWorkbook.

3
Create the Outlook objects

Before using objMail.Body, initialize both objects with Set: Set objOutlook = CreateObject("Outlook.Application") and Set objMail = objOutlook.CreateItem(0). Declare both variables As Object for late binding.

4
Set the body and test the draft

After the assignments, use objMail.Body = CStr(rngSupplierMPID.Value) for a single-cell example, then use objMail.Display to inspect the draft without sending it. Adapt the body to the actual range and message content.

5
Compile and check remaining references

In the VBA editor, choose Debug > Compile VBAProject, then run the macro again. If error 1004 remains, use Debug to identify the failing line and check that its workbook, worksheet, cell address or named range exists and is correctly qualified.

Qualify the Excel range and initialize the Outlook MailItem
Outlook references: The CreateObject example uses late binding and does not require an Outlook library reference. If the code declares Outlook-specific types instead, check Tools > References in the VBA editor for the appropriate Microsoft Outlook Object Library and any missing references.
Free Microsoft Office alternative

Work with Excel Files in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets and other Office files. Its format compatibility helps you move existing documents into WPS, though it does not fix Microsoft Excel or Outlook automation errors.

Open and edit spreadsheet data in a familiar interfaceCompatible with Microsoft Excel XLS and XLSX filesWork with worksheets and ranges without relying on an email automation macroFree and lightweight for everyday Office tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel VBA report Object required when creating an Outlook email?

A statement may be using something that is not a valid object reference, such as an incorrectly assigned range or mail object. Select Debug in the error dialog and inspect the highlighted line, including the Set statements that assign its objects.

How do I create an Outlook MailItem before setting its body?

In the VBA editor, declare objOutlook and objMail As Object, then use Set objOutlook = CreateObject("Outlook.Application") and Set objMail = objOutlook.CreateItem(0) before assigning objMail.Body.

Why did error 1004 appear after I changed rngSupplierMPID?

Error 1004 can occur when a worksheet or range reference is invalid. Select Debug, then check that the workbook, worksheet, cell address and any named range on the highlighted line are correct and fully qualified.

Do I need to enable the Outlook Object Library?

Not when using late-bound variables declared As Object with CreateObject. If your code uses Outlook-specific types, open Tools > References in the VBA editor and check the Microsoft Outlook Object Library and any missing references.