VBA Script to Send Email From Excel With Outlook

VBA & macros · Updated November 22, 2026

To send an email from Excel on Windows, use a VBA macro to create an Outlook message, fill in its recipient, subject, and body, then display it for review or send it. The example below uses classic Outlook for Microsoft 365; Excel’s VBA does not send the message by itself, and the new Outlook for Windows does not support this COM automation method.

What the macro does and what it requires

The macro passes text from worksheet cells to Outlook through Outlook’s object model. Outlook then uses an account configured in its desktop application to create the message. That means you need desktop Excel for Windows, classic Outlook installed and set up with an account, and permission to run macros. Excel for the web cannot run VBA macros.

The example expects a worksheet named Mail with these values:

  • B2: the recipient’s email address
  • B3: the subject line
  • B4: the message text

Change the worksheet name or cell references in the code if your workbook uses a different layout. The example opens a draft rather than sending automatically. This is a sensible default: you can check the address and message before clicking Send in Outlook.

VBA script to create an Outlook email

In Excel, press Alt+F11 on Windows to open the Visual Basic Editor, choose Insert > Module, and paste this macro into the module. On a Mac, Option+F11 (sometimes Fn+Option+F11) opens the editor on many keyboards, but this Outlook automation code is Windows-specific and will not work with Outlook for Mac.

Sub DraftEmailFromExcel()
    Dim outlookApp As Object
    Dim mailItem As Object
    Dim ws As Worksheet
    Dim recipient As String
    Dim emailSubject As String
    Dim emailBody As String

    On Error GoTo ErrHandler

    ' Read the message details from the Mail worksheet.
    Set ws = ThisWorkbook.Worksheets("Mail")
    recipient = Trim$(CStr(ws.Range("B2").Value))
    emailSubject = CStr(ws.Range("B3").Value)
    emailBody = CStr(ws.Range("B4").Value)

    If Len(recipient) = 0 Then
        MsgBox "Enter a recipient in cell B2 first.", vbExclamation
        Exit Sub
    End If

    ' Create a message in classic Outlook.
    Set outlookApp = CreateObject("Outlook.Application")
    Set mailItem = outlookApp.CreateItem(0)

    With mailItem
        .To = recipient
        .Subject = emailSubject
        .Body = emailBody
        .Display
    End With

    Set mailItem = Nothing
    Set outlookApp = Nothing
    Exit Sub

ErrHandler:
    MsgBox "The email draft could not be created: " & Err.Description, vbExclamation
    Set mailItem = Nothing
    Set outlookApp = Nothing
End Sub

The variables declared as Object use late binding, so you do not have to select an Outlook library under Tools > References in the VBA editor. The number 0 passed to CreateItem means a standard mail item. The error handler displays an explanation if, for example, the worksheet name is wrong or Outlook cannot be opened.

Install and run the macro safely

  1. Put the recipient, subject, and message in the cells described above. Check that B2 contains the intended address.
  2. Save a copy of the workbook before running a macro. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the VBA code.
  3. In the Visual Basic Editor, click inside the macro and press F5, or return to Excel and run it from Developer > Macros. If the Developer tab is hidden, enable it in File > Options > Customize Ribbon.
  4. Review the draft Outlook opens. Verify the To field, subject, and entire message, then send it from Outlook if it is correct.

Macros cannot be undone with Ctrl+Z, and a sent email cannot be recalled reliably. On a Mac, the usual undo shortcut is Command+Z, but that does not reverse a macro’s actions either. Do not lower Excel’s macro security settings broadly just to run a file; only enable code you trust, and follow your organization’s policy.

Drafting versus sending automatically

The sample uses .Display, which opens the message for review. To ask Outlook to submit it without opening a draft, change that line to .Send. The rest of the code can stay the same. Use automatic sending only after you have tested the recipient and content with drafts, because a typo in a cell can otherwise send the wrong message with no final review in Excel.

.Send submits the message through the Outlook account; it does not guarantee that the recipient receives it immediately. Outlook may place it in the Outbox, and delivery can depend on connectivity, account restrictions, or mail-system checks. A macro also does not bypass organization rules about who may send messages or what content may be shared.

Sending different messages from worksheet rows

For a mailing list, arrange one recipient per row—for example, email address in column A, name in column B, and a message-specific value in column C. A loop can read each row, create a separate Outlook item, and personalize the subject or body with the row’s values. Before processing a full list, test with your own address and a small number of rows.

Keep the workflow controlled rather than treating every populated cell as permission to send. Useful safeguards include a clearly labeled test mode, a status column such as Drafted or Sent, and a check that the address cell is not blank. Avoid adding every recipient to one message’s To field: separate messages help prevent exposing the list of addresses to other recipients. If you add a status update, understand that a failure after sending but before recording the status could lead to a duplicate on a later run.

Adding attachments or formatted content

Outlook mail items can include attachments and HTML-formatted bodies, but they need extra checks. For an attachment, the macro must provide a valid file path; check that the file exists before asking Outlook to attach it, and confirm that the file is appropriate to share. A path on a local drive may not exist on another computer, and a network drive may be unavailable when the macro runs.

The example uses .Body for plain text. HTML email uses the mail item’s .HTMLBody property instead, with valid HTML markup in the string. Be careful when inserting cell values into HTML: characters such as ampersands and angle brackets can be interpreted as markup unless they are encoded. Plain text is usually simpler and safer for a short notification.

Common problems and their causes

  • ActiveX component cannot create object: Excel cannot create the Outlook application object. Confirm that classic Outlook desktop is installed and configured, and check whether your organization blocks automation. The new Outlook app is not a drop-in replacement for this COM-based code.
  • Subscript out of range: The workbook does not contain a worksheet with the exact name Mail. Change the code to match the actual sheet tab name.
  • The draft has empty fields: Check the cell locations, ensure the macro is reading the intended workbook, and confirm the values are not formulas returning empty text.
  • Excel blocks the macro: The file may be untrusted, macros may be disabled by policy, or the file may have been downloaded from the internet. Use only trusted files and ask your administrator about managed settings rather than changing security controls indiscriminately.
  • Outlook displays a warning or sending is blocked: Security prompts and restrictions vary with Outlook configuration and organization policy. Do not try to bypass them; ask your administrator which approved sending method to use.

Security and privacy considerations

A macro that sends email can expose customer data, internal information, or attachments if a workbook is shared or edited carelessly. Limit access to the workbook, keep recipient lists and message content out of untrusted files, and review any formulas that generate addresses. Do not store passwords in VBA or worksheet cells: this approach uses Outlook’s configured account rather than asking the macro to handle a password.

For recurring or high-volume messages, check your organization’s rules and the mail provider’s sending limits before automating. Outlook automation is best suited to a controlled desktop task, not an unattended service. If an email is legally or operationally important, use a process with appropriate approval, logging, and error handling rather than relying on an unreviewed macro.

When to use another method

This VBA approach is a practical fit when someone is working in Excel on Windows with classic Outlook and wants to prepare or send a small number of messages from workbook data. It is not suitable for Excel for the web, Outlook for Mac, or the new Outlook for Windows through the COM interface used here.

For a cloud-based workflow, Microsoft Power Automate may be a better option, especially when a flow should run without a user opening Excel on a desktop. A simple mailto: link can open a message in a default mail app, but it is limited for automation and is not a substitute for a controlled bulk-email process. Choose based on whether you need a reviewable desktop draft, a repeatable managed workflow, or merely a link that starts a message.

Frequently Asked Questions

Can Excel send an email without Outlook?
This sample relies on classic Outlook for Windows, so it cannot send without Outlook. Other approaches, such as an approved cloud flow or a mail service API, require their own setup, permissions, and security review.

Does this macro work with the new Outlook for Windows?
No. The code uses Outlook COM automation, which the new Outlook for Windows does not support. Use classic Outlook for this method, or choose another approved integration.

Can I send an email from Excel for Mac?
VBA is available in Excel for Mac, but this code’s CreateObject("Outlook.Application") call depends on Windows COM automation and does not work with Outlook for Mac. Use a Mac-compatible workflow instead.

Can I send to several people at once?
Yes. You can put multiple addresses in Outlook’s To field, or create one message per worksheet row. Separate messages are generally preferable when recipients should not see one another’s addresses.

Why does the macro open a draft instead of sending?
.Display is deliberately used so you can inspect the address and content first. Replacing it with .Send requests automatic submission, but it removes that review step and remains subject to Outlook and organization controls.

Related

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.