VBA Macro to Send Email From Excel: Setup and Code

VBA & macros · Updated November 21, 2026

To send email from Excel with VBA, automate classic Outlook for Windows: place the recipient, subject, message, and optional attachment path in worksheet cells, then run a macro that creates an Outlook message. The example below opens a draft for review; after testing, you can change one line to send it automatically.

What the macro needs

This method uses Excel VBA to control the Outlook desktop application through Outlook’s object model. It is intended for Microsoft Excel for Microsoft 365 on Windows and requires classic Outlook for Windows to be installed and configured with an account. The macro does not send email through Excel itself: Outlook creates and sends the message using an available Outlook account.

The distinction between classic Outlook and new Outlook matters. New Outlook for Windows does not support the same COM automation interface used by this example, so a macro that calls CreateObject("Outlook.Application") may fail if only new Outlook is available. Excel for the web cannot create or run VBA macros. On a Mac, Outlook automation differs and this Windows COM example is not a reliable solution; consider Power Automate or another supported mail service instead.

  • Use a workbook saved as an Excel Macro-Enabled Workbook (.xlsm).
  • Have a working Outlook desktop profile and confirm Outlook can send a normal message.
  • Use an email address or semicolon-separated addresses in the recipient cell.
  • For attachments, provide the complete path to a file that exists on the computer or an accessible network location.

Set up the worksheet

Create a worksheet named Email and arrange the input values as follows. The example reads these exact cells, so either use this layout or edit the cell references in the code.

  • B2: recipient address or addresses
  • B3: subject line
  • B4: message text
  • B5: optional full attachment path, such as C:\Reports\summary.pdf

For example, B2 could contain alex@example.com; pat@example.com, B3 could contain Monthly summary, and B4 could contain Hello, please see the attached report.. Outlook accepts multiple recipients separated by semicolons. Keep the attachment path blank when no file is needed. A cell-based setup makes the macro easy to reuse without changing its VBA, but it also means the macro will send whatever values are currently in those cells.

VBA macro to create the email

In Excel, open the Visual Basic Editor, choose Insert > Module, and paste this code into the module. The code uses late binding, so you do not need to add an Outlook reference under Tools > References. It checks the required values and verifies that a requested attachment exists before opening the draft.

Option Explicit

Sub EmailFromExcel()
    Dim ws As Worksheet
    Dim olApp As Object
    Dim mail As Object
    Dim recipient As String
    Dim subjectText As String
    Dim bodyText As String
    Dim attachmentPath As String

    On Error GoTo ErrHandler

    Set ws = ThisWorkbook.Worksheets("Email")
    recipient = Trim$(CStr(ws.Range("B2").Value))
    subjectText = CStr(ws.Range("B3").Value)
    bodyText = CStr(ws.Range("B4").Value)
    attachmentPath = Trim$(CStr(ws.Range("B5").Value))

    If Len(recipient) = 0 Then
        MsgBox "Enter at least one recipient in Email!B2.", vbExclamation
        Exit Sub
    End If

    If Len(attachmentPath) > 0 Then
        If Len(Dir$(attachmentPath)) = 0 Then
            MsgBox "The attachment file was not found: " & attachmentPath, vbExclamation
            Exit Sub
        End If
    End If

    Set olApp = CreateObject("Outlook.Application")
    Set mail = olApp.CreateItem(0)

    With mail
        .To = recipient
        .Subject = subjectText
        .Body = bodyText
        If Len(attachmentPath) > 0 Then .Attachments.Add attachmentPath
        .Display  ' Review the message before sending.
        ' After testing, replace .Display with .Send to send directly.
    End With

    Set mail = Nothing
    Set olApp = Nothing
    Exit Sub

ErrHandler:
    MsgBox "The email could not be created: " & Err.Description, vbCritical
    Set mail = Nothing
    Set olApp = Nothing
End Sub

The code fills the To, Subject, and plain-text Body fields, adds an attachment only when B5 is not blank, and displays the finished message. To send without opening a draft, replace .Display with .Send and remove or comment out the following explanatory comment. Keep the preview behavior until you have verified the workbook’s inputs and recipient handling. The macro uses Outlook’s default sending account; it does not select a particular account or set CC and BCC.

Run and test it safely

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, and a send action cannot be recalled reliably once Outlook has transmitted the message. Save the working file as .xlsm, enter test values, and use your own address as the recipient before trying real contacts.

  1. In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).
  2. Open the VBA editor with Alt+F11 on Windows. On Mac, Option+F11 may require the Fn key depending on keyboard settings; Excel for Mac also has a Visual Basic command on the Developer tab.
  3. Insert a module, paste the code, and return to Excel.
  4. Run the macro from Developer > Macros: select EmailFromExcel and choose Run. Alt+F8 opens the macro dialog on Windows; Option+F8 is the usual Mac equivalent, subject to keyboard settings.
  5. Review the Outlook draft, confirm the recipients, subject, body, and attachment, then send it manually.

If the Developer tab is hidden on Windows, enable it through File > Options > Customize Ribbon and check Developer. Do not lower macro security globally just to run this workbook. If Excel blocks the macro, follow your organization’s approved process or ask its administrator; policies can prevent macros or automated email even when the code is correct.

Adapt the message and recipient fields

To add CC or BCC, put those values in additional worksheet cells and assign them to the corresponding Outlook properties, such as .CC or .BCC, inside the With mail block. To change the worksheet, update Worksheets("Email"); to change a field’s location, edit its Range reference. Keep the worksheet name and cell addresses consistent with the workbook, because a misspelled sheet name triggers the error handler.

The example uses .Body, which creates a plain-text message. For a formatted message, Outlook provides .HTMLBody, but the value must be valid HTML, and line breaks need to be represented with HTML such as paragraph or break tags. Do not simply paste plain text into .HTMLBody and expect Excel line breaks or formatting to transfer correctly. If you want to preserve an Outlook signature, constructing the HTML body can require additional care because assigning the body may replace or alter signature content.

For a row-by-row mailing, a macro can loop through a table and create one message per row, using each row’s address and values. Treat that as a separate design step: add checks for blank or duplicate addresses, test with a small sample, and decide whether each recipient should receive a separate message. Putting many people in one To field exposes their addresses to one another. A personalized one-message-per-row workflow avoids that exposure, but it can still send incorrect or sensitive information if the data mapping is wrong.

Common errors and practical fixes

  • “ActiveX component can’t create object” or a similar automation error: Excel may not be able to instantiate classic Outlook. Check that classic Outlook is installed and configured, rather than assuming new Outlook supports this COM interface.
  • “Subscript out of range”: The workbook does not contain a sheet named exactly Email. Rename the tab or change the worksheet name in the macro.
  • The attachment is missing: Check the full path, spelling, file extension, and access permissions. A mapped network drive may not be available under a different sign-in context; test the path in File Explorer.
  • The macro does not appear in the macro list: Confirm the file is saved as .xlsm, the code is in a standard module, and macros are permitted by your organization’s settings.
  • The draft opens but does not send: That is expected with .Display. Review it and click Send, or deliberately change the method to .Send after testing.
  • Outlook security or policy blocks automation: Managed devices may apply programmatic-access controls. Do not attempt to bypass them; ask IT which approved automation method is available.

Also consider whether the message is in Outlook’s Outbox or whether Outlook is offline before diagnosing a macro failure. A successful call to the send method does not prove that the recipient received the message; delivery can be delayed or rejected later by mail systems.

Outlook automation and other options

For a small, attended task on a Windows computer, Outlook VBA automation is convenient because the message is visible and can use the configured Outlook profile. It is not the same as a mail merge, a bulk-mail platform, or a background service. It depends on a compatible Outlook installation and is usually a poor fit for a server or unattended process.

If the workflow must run on a schedule or without a desktop Outlook session, consider Microsoft Power Automate with an approved Outlook connector, or an organization-approved service using Microsoft Graph. Those options have their own account permissions, licensing, administration, and setup requirements. Word’s mail merge can also create individualized Outlook messages for a conventional mailing, though it is not a replacement for every spreadsheet-driven workflow. A mailto: hyperlink can open a new message in a mail app, but it is limited and does not provide a dependable way to attach files or silently send mail.

Security and responsible use

Email macros can expose personal or confidential information, attach the wrong file, or send a message to a stale address. Restrict access to the workbook, avoid storing passwords in VBA, and do not enable macros in files from sources you do not trust. Use the preview-first version for routine work, and place a deliberate confirmation step before any high-volume or sensitive send process.

Check your organization’s policies on automated email, bulk messaging, and data handling before deployment. The macro sends through the signed-in Outlook environment, so organizational retention, mail-flow rules, and access controls may apply. For recurring business processes, a centrally managed and auditable workflow is usually easier to govern than a copy of a macro passed among users.

Frequently Asked Questions

Can Excel send an email without Outlook?
Not with this Outlook object-model macro. It requires classic Outlook for Windows. Other approaches, such as Power Automate or a properly configured mail service, may send without desktop Outlook, but require their own supported setup and permissions.

How do I make the macro send automatically?
After thoroughly testing the preview, replace .Display with .Send. Confirm that the recipient and attachment values are correct before each run. Sending is not undoable, and organizational security settings may still block automated sending.

Can I use this macro in Excel for the web?
No. Excel for the web can open an .xlsm workbook but cannot run its VBA macros. Use desktop Excel on Windows with compatible classic Outlook, or choose a web-based automation option.

Can the macro attach a file from a worksheet cell?
Yes. This example reads a full file path from B5 and adds the file if the path exists. Leave B5 blank for no attachment. Check the path and access permissions before running the macro.

Will it send from a particular Outlook account?
The sample uses Outlook’s default sending account. If several accounts are configured and the sender must be controlled, the VBA needs additional account-selection logic; verify that requirement with your Outlook configuration and test before sending.

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.