In Excel for Microsoft 365 on Windows, VBA can create an email through classic Outlook desktop: open the VBA editor, add a macro that creates an Outlook mail item, and use .Display to review it before sending. The example below creates a draft; it does not send anything until you click Send in Outlook.
What the macro does and what you need
Excel does not send email by itself through ordinary VBA. A common Windows approach is to automate classic Outlook using its COM interface. The macro asks Outlook to create a message, fills in the recipient, subject, and body, then displays the message window. You can inspect the details, edit the draft, and send it from Outlook.
This approach requires desktop Excel for Windows and classic Outlook for Windows to be installed and available on the computer. Outlook should have a working email profile configured. Microsoft’s newer Outlook for Windows does not provide the same COM automation interface, so a macro that calls Outlook.Application may fail if only new Outlook is installed. Excel for the web cannot run VBA macros.
The sample uses late binding: its Outlook objects are declared as Object, rather than requiring a particular Outlook library reference to be selected in the VBA editor. This makes the sample easier to set up across compatible Outlook installations, but it does not remove the need for classic Outlook.
VBA code to create an Outlook email
In Excel, press Alt+F11 on Windows to open the Visual Basic Editor. Choose Insert > Module, then paste the macro into the module. Replace the example recipient, subject, and message with your own details. The optional attachment line is commented out; remove the leading apostrophe and use a real file path if you want to attach a file.
Option Explicit
Public Sub CreateEmailDraft()
Dim outlookApp As Object
Dim outlookMail As Object
Dim emailBody As String
On Error GoTo ErrHandler
' Create an Outlook automation object.
Set outlookApp = CreateObject("Outlook.Application")
Set outlookMail = outlookApp.CreateItem(0)
' Build a plain-text message with line breaks.
emailBody = "Hello," & vbCrLf & vbCrLf & _
"Please see the attached report." & vbCrLf & vbCrLf & _
"Regards," & vbCrLf & Application.UserName
With outlookMail
.To = "recipient@example.com"
.CC = ""
.Subject = "Monthly report"
.Body = emailBody
' Optional attachment: replace this with an existing file path.
' .Attachments.Add "C:\Reports\MonthlyReport.xlsx"
.Display
End With
CleanExit:
Set outlookMail = Nothing
Set outlookApp = Nothing
Exit Sub
ErrHandler:
MsgBox "Could not create the email: " & Err.Description, vbExclamation
Resume CleanExit
End Sub
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm), not as a standard .xlsx file, if you want to keep the VBA code. Before running any macro, save a separate copy of the workbook: macro actions cannot be undone with Ctrl+Z. Run the macro with the cursor inside the procedure and press F5, or return to Excel and choose Developer > Macros, select CreateEmailDraft, and choose Run. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon on Windows.
How to customize recipients, subject, and message
The values assigned to .To, .CC, and .Subject are ordinary text. Outlook accepts multiple recipients separated by semicolons, for example person1@example.com; person2@example.com. You can leave the CC field empty, as in the sample, or place one or more addresses there. A blind-copy recipient can be set with .BCC, but use it thoughtfully: recipients should not be exposed to addresses they have no reason to see.
The .Body property creates a plain-text message. The VBA constant vbCrLf inserts a new line, and joining pieces with the ampersand lets the code build a readable message. The example uses Application.UserName as a sign-off, but that is Excel’s configured user name, not necessarily the name or identity associated with the sending email account. Set the closing text explicitly if it needs to be reliable.
If the content comes from worksheet cells, read the cell values into VBA variables and validate them before creating the message. For example, an email address in a cell can be blank, mistyped, or contain extra spaces. A macro that takes a recipient from a worksheet should stop and show a warning if the address is missing, rather than creating a message that appears ready to send to the wrong person. For messages that need bold text, links, or tables, Outlook’s .HTMLBody property can be used instead of .Body; HTML content must be built with appropriate tags and should encode any cell text that might contain special HTML characters.
Drafting versus sending automatically
The safest default is .Display. It opens the email for review and leaves the final decision to the user. To send without opening a message window, VBA can call .Send instead. That changes the behavior substantially: the macro may send a message immediately, without giving you a chance to check the recipient, content, attachment, or account first. Do not make that substitution until the process has been tested with harmless sample data and the intended sending rules are clear.
A displayed draft is also useful for checking which Outlook account is selected and whether Outlook has added a signature. The result can vary with Outlook configuration, especially if several accounts are present. A macro should not assume that the visible draft is identical on every computer or that a configured signature will be added exactly as expected.
For any automated process that sends messages to many people, include safeguards such as a preview step, a confirmation prompt, a limit on the number of messages per run, and a log of what was prepared or sent. Confirm that each recipient is authorized to receive the information. Organizational mail systems may also apply sending limits, anti-spam controls, or approval requirements; VBA does not bypass those policies.
Adding an attachment safely
The sample shows an optional .Attachments.Add line. Uncomment it only after replacing the example with a real, complete path to a file, such as a file on a local drive or an accessible network location. Outlook cannot attach a file that does not exist or that the current Windows account cannot read. For a repeatable macro, check that the file exists before adding it and show a useful message if it does not.
Think about what the attachment contains before sending it. A workbook may include hidden sheets, comments, personal information, or data outside the visible report. If the purpose is to share a finished view, consider preparing a separate export with only the intended information. Be particularly careful when a recipient address or attachment path is drawn from worksheet data: validate both, and do not assume that a matching row means the recipient is allowed to see the file.
Compatibility, macro security, and common errors
Excel for the web can open a macro-enabled workbook but does not run its VBA. On a Mac, the Windows COM call CreateObject("Outlook.Application") is not a compatible way to automate Outlook. Mac users need a different workflow, such as a supported organizational service or another automation method appropriate to their version and configuration; do not expect this Windows code to work unchanged in Excel for Mac.
If the macro reports that it cannot create the Outlook object, check that classic Outlook desktop is installed and that your organization permits its automation. If Outlook opens but cannot send, check that an account is configured, connected, and permitted to send. If Excel blocks the macro, review the file’s source and your organization’s macro policy. Do not enable all macros globally just to run an unfamiliar file; use trusted files and follow your IT administrator’s guidance.
- “User-defined type not defined”: This sample avoids that specific reference issue by declaring Outlook variables as
Object. If you replace them with Outlook-specific types, you may need to select the corresponding Outlook Object Library in Tools > References. - Outlook does not open or the call fails: Confirm that classic Outlook is installed, set up, and available. New Outlook may not support this COM-based approach.
- The message is created but not delivered: A draft is not a sent message. With
.Display, click Send yourself, then check Outlook’s Outbox and Sent Items if delivery is in question. - An attachment is missing: Check the exact path, filename, extension, and access permissions. A path copied from another computer may not exist on yours.
Alternatives when Outlook automation is not suitable
If you only need to prepare a single message, composing it directly in Outlook may be simpler and safer than maintaining a macro. For recurring business workflows, Microsoft Power Automate may be a better fit, depending on the available connectors, licenses, and organizational policies. It can work with cloud-based processes without relying on desktop COM automation, but it needs to be configured and approved for the relevant accounts and data.
Another option is to export or prepare the information in Excel and use an approved email or customer-management system to send it. This can provide better audit trails and controlled templates for workflows involving many recipients. The right choice depends on message volume, required approvals, whether the process must run unattended, and what your organization supports. VBA is convenient for a small, user-reviewed task on a compatible Windows desktop; it is not automatically the best tool for bulk or unattended mail.
Frequently Asked Questions
Can Excel VBA send email without Outlook?
Yes, other methods and services can send email, but the code in this guide uses classic Outlook for Windows. For a different method, consider approved services such as Power Automate or an organization’s mail platform, and follow its authentication and security requirements.
Will this code work with the new Outlook for Windows?
Not as written. It relies on Outlook’s COM automation interface, which is associated with classic Outlook for Windows. If only new Outlook is installed, use another supported automation option.
How do I send an email from an Excel cell?
Read the cell’s value in VBA and assign it to a property such as .To, .Subject, or .Body. Validate the value first, then display the draft to check that the cell supplied the intended text and recipient.
Can the macro attach a worksheet or workbook?
It can attach a saved file by passing its full path to .Attachments.Add. If you need to attach a particular sheet, first create or export a separate file containing the intended content, then attach that file; a worksheet itself is not a standalone file attachment.
Why does Ctrl+Z not reverse a macro?
Running VBA generally clears Excel’s normal undo history, and macro actions cannot be reversed with Ctrl+Z. Save a copy before running code, and have the macro create drafts rather than make irreversible changes wherever practical.
Can I run the macro in Excel for the web or on a Mac?
Excel for the web does not execute VBA. The Outlook COM code shown here is for Windows and is not a drop-in solution for Excel on Mac. Use a compatible, organization-approved alternative for those environments.
Related
- Print a Specific Area in Excel: Steps, Scaling, and Fixes
- Excel Print Area: Set, Change, and Clear It
- Power Query to Compare Two Excel Sheets Step by Step
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Message Box: How to Use MsgBox in MacrosIn Excel VBA, use the MsgBox function to show a message, optionally display buttons or an icon, and…
- Macro Recorder for Xbox: What Works and What Doesn’tIf you mean a device or feature that records a sequence of Xbox controller inputs and plays it…
- VBA Split: How to Turn Text into an Array in ExcelYes. In Excel VBA, use the Split function to divide a string at a delimiter and return the…
- Excel VBA: Open Multiple Files in a Folder with a LoopTo open every Excel workbook in a folder, use VBA’s Dir function to find each matching filename, then…
- Excel VBA InputBox Password: Masking and Secure OptionsExcel’s built-in VBA InputBox cannot hide a password as the user types; it displays the entered characters. To…
- Excel VBA: Count Visible Rows After AutoFilterTo count rows still visible after an AutoFilter, loop through the filter’s data rows and count those whose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.