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 addressesB3: subject lineB4: message textB5: optional full attachment path, such asC:\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.
- In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).
- 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.
- Insert a module, paste the code, and return to Excel.
- Run the macro from Developer > Macros: select
EmailFromExceland choose Run. Alt+F8 opens the macro dialog on Windows; Option+F8 is the usual Mac equivalent, subject to keyboard settings. - 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.Sendafter 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
- QuickBooks Data Export to Excel: Steps and Options
- QuickBooks Excel and CSV Imports: What You Can Bring In
- Python Excel: Remove Duplicates with pandas or openpyxl
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to Open the Macro Editor in ExcelIn Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, where you can…
- Excel VBA Filter Function: Syntax, Examples, and LimitsIn Excel VBA, the Filter function searches a one-dimensional array of strings and returns the elements that contain…
- VBA Dictionary: How It Works, Examples, and Common UsesA VBA Dictionary is a key–value collection: you store a value under a unique key, then retrieve it…
- VBA Add Worksheet After the Last SheetTo add a worksheet after the last sheet tab in Excel, use Worksheets.Add with the last item in…
- VBA Excel Offset: How to Select a Range ReliablyIn Excel VBA, use the Offset property on a Range to refer to cells a chosen number of…
- How to Open the VBA Editor in Excel on Windows and MacIn Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic for Applications (VBA) Editor.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.