How to Send Outlook Email with VBA from Microsoft Excel

Automating communication directly from your spreadsheet saves hours of manual data entry and attachment handling. If you need to distribute reports, notify team members of status changes, or send personalized messages based on spreadsheet data, learning exactly sending Outlook Email with VBA from Microsoft Excel provides a seamless, automated workflow. By leveraging Visual Basic for Applications, you can trigger the email client to generate, populate, and dispatch messages without ever leaving your active workbook.

Accessing the VBA Editor and Setting Up the Environment

Before writing any code, you must enable the necessary developer tools within your spreadsheet interface.

1. Open your workbook and look at the top ribbon menu. If you do not see the Developer tab, right-click anywhere on the empty ribbon space and select Customize the Ribbon.

2. In the right-hand pane of the Options window, locate the Main Tabs list, check the box next to Developer, and click OK.

3. Navigate to the newly visible Developer tab and click the Visual Basic button on the far left side (or press Alt + F11 on your keyboard).

4. In the Visual Basic for Applications editor window, click Insert from the top menu bar, then choose Module. This action creates a blank text window in the center pane where your automation script will reside.

Writing the Macro to Generate an Email

To communicate with the mail client, your macro will use a technique called Late Binding. This method creates an instance of the external application during runtime, preventing object library reference errors if you share the workbook with colleagues running different versions of the software.

Type or paste the following code block into your new module window:

Sub SendEmail()
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
.To = "recipient@example.com"
.CC = ""
.BCC = ""
.Subject = "Monthly Report Update"
.Body = "Hello, please find the latest data updates applied to the system."
.Display
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

To test this script, place your text cursor anywhere inside the code block and press F5, or click the green Run Sub/UserForm arrow in the top toolbar. A new message window will pop up on your screen with the specified address, subject line, and body text fully populated. Changing .Display to .Send in the code will bypass the popup and dispatch the message immediately in the background.

Referencing Cell Data and Attaching Files

Static text is rarely sufficient for recurring administrative tasks. You can make the script fully dynamic by pulling addresses, subject lines, or body copy directly from specific cells in your worksheet.

Update the .To line in your code to reference a target cell. For example, typing .To = Sheets("Sheet1").Range("A2").Value tells the script to look at Sheet1, find cell A2, and use its contents as the recipient address.

To attach a file to the message, insert the .Attachments.Add property directly below the body definition inside the With statement. For example, typing .Attachments.Add "C:\Users\Username\Documents\Report.xlsx" will automatically upload the specified file. If you want to attach the current active workbook, use .Attachments.Add ActiveWorkbook.FullName. You must ensure the spreadsheet is saved first so that a valid, readable file path exists on your hard drive.

Streamlining Document Sharing with WPS Office

Because VBA macros that rely on Microsoft's COM objects strictly require both Excel and Outlook to be locally installed and authenticated with active Microsoft accounts, WPS Office cannot change those Microsoft-side licensing or administrative settings. However, if your underlying document goal is simply to distribute a spreadsheet efficiently without writing scripts, WPS Office provides excellent native tools for this workflow.

Open your document in WPS Spreadsheets. Navigate to the Menu button in the top left corner or the Share button in the top right. Select Email from the drop-down list. This command will instantly attach your current, saved document to a blank message in your computer's default system mail client, entirely bypassing the need for programmatic triggers.

Furthermore, if you need to lock the data formatting before sending, WPS Office includes a built-in PDF conversion engine. Go to the Tools tab and click Export to PDF. Configure your page ranges, click Export, and then use the integrated sharing options on the resulting file to send the secured PDF. This helps ensure your tables and charts remain correctly intact for the recipient, eliminating the need to maintain complex macro security settings.

Frequently Asked Questions

Why do I get a "User-defined type not defined" error when running the script?

This error occurs if you attempt to use Early Binding (where you declare variables specifically as Outlook.Application) without enabling the correct object library reference. To fix this, open the Visual Basic editor, go to Tools > References in the top menu, scroll down, and check the box next to Microsoft Outlook XX.X Object Library. Alternatively, rewrite your variables as generic Object types (Late Binding) as demonstrated in the primary workflow above, which completely bypasses the need for manual library activation.

How can I send emails to multiple recipients at once?

You can include multiple email addresses in the .To or .CC fields by separating them with a semicolon. For example, configure the line as .To = "user1@example.com; user2@example.com". If you are pulling these addresses dynamically from spreadsheet cells, you can concatenate multiple cell values using the ampersand operator, such as .To = Range("A2").Value & ";" & Range("A3").Value, which ensures the necessary semicolon properly divides the fetched addresses.

Can I format the email body text with bolding or hyperlinks?

Yes, but you must switch the property in your code from .Body to .HTMLBody. When you use the HTML body property, you can wrap your text strings in standard HTML tags for rich formatting. For instance, typing .HTMLBody = "Hello,

Please review the attached report." will insert a double line break and bold the phrase "attached report" in the final generated message.

Why does my macro prompt a security warning before sending?

Modern security protocols frequently block external applications from dispatching messages automatically to prevent malicious scripts from distributing spam invisibly. This specific warning triggers if your system's antivirus software is inactive, outdated, or if programmatic access security is set to "Warn me" in the Trust Center. To verify the outcome without triggering the security block and failing the script, leave your code set to .Display rather than .Send, which allows you to manually review and click the send button on the generated window.