Excel VBA: Create an Outlook Email Draft in Windows

Yes. In Excel for Microsoft 365 on Windows, VBA can use classic Outlook’s object model to create an email, fill in its recipient, subject, and message, then save it as a draft without sending it. Put the recipient and message details in worksheet columns, run the macro, and the drafts will appear in Outlook’s Drafts folder for review.

What the macro does—and what it does not do

The example below creates one draft for each populated recipient in a worksheet. It reads the recipient from column A, the subject from column B, and the message from column C, starting at row 2. A header row can occupy row 1. The macro creates a message, fills its fields, and calls Outlook’s Save method. It does not call Send, so it does not send the messages.

This approach is useful for preparing personalized reminders, status updates, or other repetitive emails from spreadsheet data. Saving drafts gives you a chance to check addresses, wording, and attachments in Outlook before sending anything. One important consequence is that running the macro again creates another set of drafts; the sample does not check whether a matching draft already exists.

Prepare the worksheet and Outlook

Arrange the sheet so that each recipient has a separate row. For example, put email addresses in A2:A20, subjects in B2:B20, and message text in C2:C20. The first row can contain headings such as Recipient, Subject, and Message. Keep the recipient address in a format Outlook recognizes, and do not include a heading or explanatory text in the data rows.

The macro uses ThisWorkbook, which means the workbook containing the code. It expects the worksheet tab to be named Sheet1; change that name in the code if your tab has a different name. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so the VBA code is retained. Before running any macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.

This method is intended for classic Outlook for Windows, which exposes the Outlook Object Model to other Office programs. It requires Outlook to be installed and a usable Outlook profile to be configured. The new Outlook for Windows does not support this traditional COM automation method, so installing or switching to new Outlook does not make this VBA code compatible with it.

VBA code to create and save drafts

In Excel, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the code into the module, and adjust the worksheet name if needed. Return to Excel and run CreateOutlookDrafts from the Macro dialog (Alt+F8). Check that Outlook is open or can start and that your workbook data is ready before running it.

Sub CreateOutlookDrafts()
    Dim olApp As Object
    Dim mail As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim recipient As String
    Dim subjectText As String
    Dim bodyText As String

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "No recipient rows were found.", vbInformation
        Exit Sub
    End If

    ' Connect to Outlook, or start classic Outlook if needed.
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0

    If olApp Is Nothing Then
        MsgBox "Classic Outlook could not be opened.", vbExclamation
        Exit Sub
    End If

    For r = 2 To lastRow
        recipient = Trim$(CStr(ws.Cells(r, "A").Value))
        subjectText = CStr(ws.Cells(r, "B").Value)
        bodyText = CStr(ws.Cells(r, "C").Value)

        If Len(recipient) > 0 Then
            Set mail = olApp.CreateItem(0)
            With mail
                .To = recipient
                .Subject = subjectText
                .Body = bodyText
                .Save
            End With
        End If
    Next r

    Set mail = Nothing
    Set olApp = Nothing
    MsgBox "Drafts were saved in Outlook.", vbInformation
End Sub

The code uses late binding: olApp and mail are declared as Object, and Outlook’s application object is obtained at run time. This avoids setting a Microsoft Outlook Object Library reference in the VBA editor, which can make a workbook more dependent on a particular installed library version. The 0 passed to CreateItem identifies a standard mail item.

The code skips rows with a blank recipient, but it does not verify that an address is deliverable or that the subject and message are appropriate. Review the worksheet and the resulting drafts. If you want to test the process, use your own address in one row first.

Customize the message and review the drafts

Change the worksheet columns or fixed text by editing the assignments inside the loop. For example, if the subject is always the same, replace the subject assignment with a quoted string in VBA; if the message is assembled from several cells, build a string using those cell values. In VBA, joining text with & is a straightforward way to combine a greeting, a name, and a message. Be careful to keep line breaks and punctuation readable in the saved draft.

The sample assigns the message to .Body, which creates a plain-text body. If the worksheet contains HTML markup and you want formatted content, Outlook mail items also provide an .HTMLBody property, which expects an HTML string. Do not simply put unescaped spreadsheet text into HTML: characters such as angle brackets can be interpreted as markup. For a basic workflow, plain text is simpler and less error-prone.

The macro saves each item to the active Outlook profile’s Drafts folder by calling .Save. It does not display each draft on screen. To open a message while the macro runs, use .Display; for a reliable saved draft, keep .Save as well. If you use Outlook’s default email signature, note that assigning a new value to .Body replaces the body text rather than automatically combining the worksheet message with a signature. Add the signature deliberately or review and edit the saved draft in Outlook.

Outlook automation, permissions, and safety

Creating drafts is safer than sending automatically, but a macro can still create many items or put private data into the wrong account. Test with a small number of rows, confirm the active Outlook profile, and inspect the Drafts folder before sharing or sending messages. Treat the workbook as sensitive if it contains names, addresses, account details, or other personal information.

Excel’s macro security settings may prevent code from running. Only enable macros in a workbook you trust, and avoid lowering global security settings just to run one file. In managed workplaces, an administrator may control macro execution or Outlook automation. Outlook security prompts or organizational policies can also affect automation, depending on the environment; do not assume that changing VBA code is an appropriate way around a company’s security controls.

Because the code saves one item for each row, a large worksheet can create a large batch of drafts. A useful safeguard is to add a limit during initial testing, or to work from a small test copy. You can also add an explicit confirmation prompt before the loop. If you later add attachments or change the code to send messages, the impact is greater: verify the file paths and recipients carefully, and retain a review step rather than treating a successful macro run as proof that the content is correct.

Common problems and practical fixes

“Subscript out of range” at the worksheet line: The sheet name in the code does not match the tab name exactly. Update Worksheets("Sheet1") to the actual tab name.
Classic Outlook could not be opened: Confirm classic Outlook is installed and configured with a profile. This COM-based macro is not supported by new Outlook for Windows.
No drafts appear: Check that column A contains recipient values from row 2 onward, that the macro completed, and that you are looking in the Drafts folder for the Outlook profile used by the automation.
Only some rows create messages: Blank recipient cells are intentionally skipped. Check for formulas returning empty text, misplaced data, or addresses in a different column.
The macro stops on a cell or data type: Inspect the affected row for unexpected errors or values. The example converts cell values to strings, but it is not a complete data-validation system.
Repeated runs produce duplicates: That is expected; the sample creates a new draft each time. Delete test drafts before rerunning, or add carefully designed duplicate checks if the workflow requires them.

If a message is saved but does not show the intended signature or formatting, that is usually a body-format issue rather than a failure to create the draft. The sample deliberately uses plain text. If a draft is saved to an unexpected account, check the Outlook profile and default data store; the code does not select a specific mailbox or account.

Alternatives and when to choose them

For a small number of messages, writing directly in Outlook may be faster and easier to review. For a repeatable Excel-driven workflow on a Windows computer with classic Outlook, VBA is convenient because it can read the workbook and create Outlook mail items locally. It is less suitable for unattended cloud workflows or environments standardized on new Outlook.

For automation that must run in Microsoft 365 without a desktop Outlook session, consider a supported cloud workflow such as Power Automate, subject to your organization’s licensing, connectors, and policies. That is a different setup from Excel VBA and may require additional configuration. If the goal is a bulk campaign, a dedicated email platform may offer consent management, unsubscribe handling, and delivery reporting that a simple spreadsheet macro does not provide.

In short, use the VBA method when you want Excel to prepare messages for human review in classic Outlook. Keep the first run small, save rather than send, and treat the Drafts folder as a review queue—not as evidence that every address or message is correct.

Frequently Asked Questions

Can Excel VBA create an Outlook draft without sending it?
Yes. Create a mail item, fill in its fields, and call .Save. Do not call .Send if you want it to remain a draft.

Does this work with the new Outlook for Windows?
No. This example relies on COM automation and the Outlook Object Model available in classic Outlook for Windows. New Outlook does not support this VBA automation method.

Where does Outlook save the draft?
Calling .Save saves the item in the Drafts folder associated with the Outlook profile and store used by the automation. The code does not choose a particular mailbox or shared mailbox.

Can the macro create a separate draft for every spreadsheet row?
Yes. The sample loops from row 2 through the last used row in column A and creates a draft when that row has a nonblank recipient. It reads the subject and body from columns B and C.

Can I create HTML-formatted email drafts?
Yes. Outlook mail items have an .HTMLBody property for HTML content. The sample uses .Body for plain text; HTML requires valid markup and careful handling of text taken from worksheet cells.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.