Fix Excel VBA Error 424 in Outlook Email Automation
Question details
The user needs to fix Excel VBA errors when a macro creates an Outlook email and reads worksheet ranges.

- 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.
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.
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.
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.
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.
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.
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.
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.

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.

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.




