VBA Code for Sending Email From Excel

VBA & macros · Updated November 21, 2026

To create an email from Excel VBA, automate classic Outlook for Windows: use CreateObject("Outlook.Application"), create a mail item, fill in its fields, and call .Display to review it or .Send to send it. The example below opens a draft for review first; it requires desktop Outlook configured with an account and does not work in Excel for the web.

What Excel VBA does when it sends email

VBA is Excel’s built-in programming language. A macro can read worksheet values, prepare an email, and hand it to another application. The common Windows approach uses Outlook’s object model: Excel asks Outlook to create a message, sets properties such as the recipient and subject, and then displays or sends the message.

This is not the same as Excel sending email directly through an email server. Outlook typically supplies the configured account, connection, and sending process. That distinction matters: installing Excel alone is not enough, and an Outlook profile may need to be set up before the macro can create a working message.

The example uses late binding, which means it does not require you to add an Outlook reference in the VBA editor. It can be easier to share between workbooks with different reference settings. The value 0 passed to CreateItem represents an Outlook mail item.

Requirements and setup

This example targets Excel for Microsoft 365 on Windows and classic Outlook for Windows. Microsoft’s newer Outlook for Windows does not provide the same COM automation support, so VBA code that automates the Outlook desktop object model may not work with it. If your organization has moved to new Outlook, ask which supported email workflow it provides.

  • Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA code.
  • Open the workbook in the Excel desktop app. Excel for the web can store a macro-enabled workbook, but it cannot run VBA macros.
  • Use a configured classic Outlook profile, and confirm that your organization permits desktop automation and macro execution.
  • Keep a separate copy of the workbook before running a macro. Macro actions cannot be undone with Ctrl+Z.

To add a macro, open the Visual Basic Editor with Alt+F11 on Windows, choose Insert > Module, and paste the code into the module. On a Mac, the Visual Basic Editor shortcut is usually Option+F11 (some keyboards require Fn as well); Excel for Mac supports VBA, but the Windows Outlook automation code below relies on COM and is not a direct Mac equivalent.

Basic VBA code to create an email

This macro creates a message with a recipient, subject, and plain-text body. It displays the draft instead of sending it, so you can confirm the details in Outlook before clicking Send.

Sub CreateEmailFromExcel()
    Dim outlookApp As Object
    Dim outlookMail As Object

    On Error GoTo HandleError

    ' Start or connect to classic Outlook.
    Set outlookApp = CreateObject("Outlook.Application")
    Set outlookMail = outlookApp.CreateItem(0)

    ' Fill in a draft for review.
    With outlookMail
        .To = "recipient@example.com"
        .CC = ""
        .Subject = "Monthly report"
        .Body = "Hello," & vbCrLf & vbCrLf & _
                "Please review the monthly report." & vbCrLf & _
                "Regards," & vbCrLf & "Alex"
        .Display
    End With

    Set outlookMail = Nothing
    Set outlookApp = Nothing
    Exit Sub

HandleError:
    MsgBox "The email could not be prepared: " & Err.Description, vbExclamation
    Set outlookMail = Nothing
    Set outlookApp = Nothing
End Sub

Change the sample address and message before using it. The .Body property is plain text; vbCrLf inserts a new line. The error handler displays a message if Outlook cannot be opened or the email cannot be prepared. It does not guarantee that a message has been delivered.

How to run and review the macro

  1. In Excel, open the workbook and press Alt+F8 on Windows. On Mac, use Tools > Macro > Macros if the shortcut differs on your keyboard.
  2. Select CreateEmailFromExcel and choose Run.
  3. Check the draft in Outlook: verify the recipient, subject, body, account, and any other details before sending it.
  4. Click Send in Outlook only after checking that the message is appropriate and addressed correctly.

Displaying a draft is a useful first test because it reveals formatting and recipient mistakes before a message leaves your mailbox. Once the macro behaves as intended, you can replace .Display with .Send. That change sends without opening the draft for manual review, so test it with a safe address and non-sensitive content first.

Customize recipients, message text, and formatting

Outlook mail items accept common fields such as .To, .CC, .BCC, .Subject, and .Body. For multiple recipients, separate email addresses with semicolons. A macro can also read those values from cells, which helps avoid editing VBA every time the recipient or message changes.

For example, if the recipient is in cell B2 and the subject is in B3 on the active worksheet, the corresponding assignments are .To = ActiveSheet.Range("B2").Value and .Subject = ActiveSheet.Range("B3").Value. Validate that the cells are not blank and that the recipient is correct before sending. A spreadsheet value can be outdated, mistyped, or contain an unintended address.

To format a message with HTML, assign HTML markup to .HTMLBody instead of using .Body. For example, HTML tags can create paragraphs, bold text, or links. HTML email needs valid markup, and the recipient’s email application may display it differently; keep important information readable as ordinary text too. Avoid putting sensitive data in a message simply because the macro can format it.

Add a workbook or other attachment

Outlook’s .Attachments.Add method attaches a file using its full path. The path must point to a file that exists and that the user is permitted to share. This example shows the basic check before adding an attachment to a draft:

Sub CreateEmailWithAttachment()
    Dim outlookApp As Object
    Dim outlookMail As Object
    Dim attachmentPath As String

    On Error GoTo HandleError
    attachmentPath = "C:\Reports\MonthlyReport.xlsx"

    ' Check that the file is present before attaching it.
    If Len(Dir$(attachmentPath)) = 0 Then
        MsgBox "Attachment not found: " & attachmentPath, vbExclamation
        Exit Sub
    End If

    Set outlookApp = CreateObject("Outlook.Application")
    Set outlookMail = outlookApp.CreateItem(0)

    With outlookMail
        .To = "recipient@example.com"
        .Subject = "Monthly report"
        .Body = "Hello, the report is attached."
        .Attachments.Add attachmentPath
        .Display
    End With

    Set outlookMail = Nothing
    Set outlookApp = Nothing
    Exit Sub

HandleError:
    MsgBox "The email could not be prepared: " & Err.Description, vbExclamation
    Set outlookMail = Nothing
    Set outlookApp = Nothing
End Sub

Replace the example path with the real file location. If you intend to attach the workbook containing the macro, save it first; an unsaved workbook may not have a usable file path. Before sending, check that the attachment is the right version and does not include hidden sheets or information that should not be shared.

Sending messages from worksheet rows

A common next step is to create one email per row—for example, when each row contains an individual recipient and a personalized note. A typical layout might put email addresses in column A, subjects in column B, and message text in column C, with headers in row 1. The macro can loop from row 2 to the last populated address and use each row’s values to fill a new mail item.

For an initial version, display each generated message rather than sending it automatically. Review the output for blank addresses, duplicate rows, incorrect personalization, and unexpected line breaks. If many rows are selected, displaying every draft can open a large number of windows, so start with a small test range. A production workflow should include checks for empty or malformed data and a clear way to limit which rows are processed.

Do not assume that a worksheet’s last row is the same as the number of valid recipients. Blank rows, formulas returning empty strings, and notes below the data can all affect how a macro determines where to stop. Also consider marking processed rows or logging outcomes if the workflow is repeated; otherwise, rerunning it may create duplicate messages.

Common problems and safe troubleshooting

  • “ActiveX component can’t create object” or a similar automation error: Confirm that classic Outlook for Windows is installed and available, rather than relying on new Outlook or Outlook on the web. Your organization may also restrict COM automation.
  • The macro does not run: Confirm that the file is saved as .xlsm, that macros are allowed by your organization’s policy, and that you opened the file in desktop Excel. Do not enable macros in an untrusted workbook just to make it run.
  • A draft opens but cannot be sent: Check that Outlook has a configured account and can send messages normally. The macro prepares a message; it does not correct account, connection, or permission problems.
  • The email is missing information: Check the worksheet cells and the code’s field assignments. A cell containing a formula that returns an empty string may look populated in the formula bar but provide no visible message text.
  • The message goes out before it is checked: Search the macro for .Send. Replace it with .Display while testing, and confirm that no other procedure sends the message automatically.

Outlook or an organization’s security controls may show a warning, block automation, or apply other restrictions. Do not try to bypass those controls; ask your Microsoft 365 administrator about an approved method. Sending behavior also depends on the account and policies in use, so a successful macro run is not proof that the recipient received or read the email.

Alternatives and platform differences

Excel for the web cannot run VBA, and Outlook automation using CreateObject is a Windows COM technique. Excel for Mac can run VBA, but it cannot use this same Windows COM connection to automate Outlook for Mac. If the workflow must run across platforms, consider a supported Microsoft 365 process instead of assuming that a Windows macro will transfer unchanged.

For a cloud-based workflow, Power Automate can use a flow to send email in response to an approved trigger, such as a form submission or a workbook stored in a supported location. Office Scripts can automate workbook tasks in Excel for the web, but they do not by themselves provide the same Outlook VBA mail-sending object model; a flow is commonly used for the email step. Choose based on where the workbook is stored, who needs to run the process, and your organization’s permissions.

Frequently Asked Questions

Can Excel VBA send an email without opening Outlook?
Not with the Outlook object-model example here: it uses classic Outlook for Windows. Other services and automation methods exist, but they require their own setup and permissions. Avoid replacing Outlook with an unverified mail-server workaround.

Should I use .Display or .Send?
Use .Display while building and testing because it opens a draft for review. Use .Send only when automatic sending is intentional, the inputs have been checked, and the workflow is authorized.

Can I send an email from Excel for the web?
No, Excel for the web does not run VBA. A cloud flow such as Power Automate may be suitable if it is configured to access the workbook and email service under your organization’s rules.

Will this macro work with new Outlook for Windows?
Do not assume it will. The code uses the classic Outlook COM object model, which is not supported in the same way by new Outlook. Check with your administrator for the currently supported option.

Can a VBA email include a file attachment?
Yes. Add the file’s full path with .Attachments.Add, after confirming the file exists and is appropriate to share. Review the attachment in the draft 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.