Automating Outlook Emails with VBA

Welcome back! In our last lesson, you mastered a crucial skill for any data professional: exporting data from Excel into a clean, universally readable CSV file. That skill allows your tools to "talk" to other systems. Now, we'll learn how to complete that communication loop by automatically sending those files to clients, colleagues, or stakeholders directly from Excel.

This lesson focuses on one of the most frequently requested automation tasks for freelance Excel developers: writing a VBA procedure to generate and send emails via Microsoft Outlook. You will learn how to create personalized emails, attach files dynamically, and build a robust tool that can handle different versions of Outlook—a key consideration for creating professional, shareable solutions.

By the end of this lesson, you will be able to write a VBA macro that can loop through a list of recipients, compose a unique email for each, attach a specific file, and either display it for review or send it automatically.

Automating Emails: The Core Concept

At its heart, automating Outlook from Excel involves using VBA to control the Outlook application. Your VBA code will act like a remote control, telling Outlook to open, create a new email, fill in the details, and send it. This is possible because Outlook, like other Microsoft Office applications, exposes an "Object Model" that VBA can interact with.

As this graphic suggests, we will be using Excel as the command center to orchestrate sending emails through Outlook.

We will explore two primary methods for this:

The Object Model Method: The standard, most reliable technique that directly manipulates Outlook's objects (like mail items and attachments).
The SendKeys Method: A clever workaround for the newer, web-based version of Outlook that doesn't fully support the traditional object model.

Let's start with the standard method, which is the foundation of email automation in VBA.

The Standard Method: Using the Outlook Object Model

The process for sending a basic email involves a few clear steps:

Create an instance of the Outlook Application: Tell VBA to "connect" to Outlook.
Create a new Mail Item: Tell Outlook to open a new, blank email.
Populate the Mail Item's properties: Fill in the .To, .Subject, and .Body fields.
Add Attachments: If needed, specify the path to any files you want to attach.
Display or Send: Show the email to the user for review (.Display) or send it immediately (.Send).
Clean Up: Release the objects from memory.

The following article from LaunchExcel provides excellent, well-structured code that demonstrates this progression perfectly.

Send Emails 10 TIMES FASTER with This Excel Hack! (7 ...

This article breaks down email automation into several "levels." We will focus on the foundational code for creating and attaching files.

Start by reading the section on basic automation, which introduces the core logic. Then, study the VBA code in the "VBA Code Box" for Level_2_Emails. Next, read the introduction for Level 3 and examine the Level_3_Emails_Single_Attachment code. Notice how it adds the .Attachments.Add method to include a file.

The code in the article shows you the fundamental structure. Now, let's watch a video that puts these concepts into action in a dynamic, real-world scenario.

Building a Dynamic Email Macro

This video from Chester Tugwell provides a masterclass in building a practical email automation tool. It loops through a table of data, generating a personalized email with a unique attachment for each row.

VBA to Automate Sending Email From Excel Table with Attachment from Folder

This video demonstrates how to build a complete email-sending macro from scratch, covering several key professional practices.

Please watch the following segments: Declaring Outlook Objects (watch this segment): The video explains two ways to create the Outlook object: Late Binding (CreateObject) and Early Binding (using the Tools -> References library). For freelance work, Late Binding is strongly preferred because it doesn't require the user to have a specific library reference enabled, making your tool more portable and reliable across different computers. Populating the Email (watch this segment): Pay close attention to how the code uses a For Each loop and the .Offset property to pull data (email address, subject details, attachment name) from the worksheet. The use of a With...End With block is a best practice for writing cleaner, more efficient code. Composing the Body (watch this segment): This section shows how to construct a multi-line email body, incorporating data from the spreadsheet and using the vbNewLine constant to add line breaks. Sending and Cleanup (watch this segment): Here, you'll see the difference between .Display (for testing) and .Send (for production). The video also covers the important "housekeeping" step of setting your object variables to Nothing to free up memory, and how to assign your macro to a worksheet button for a user-friendly interface.

By combining these resources, you get a complete picture of the standard, robust method for email automation.

The Challenge of the "New Outlook"

The Object Model method we just learned works perfectly with the traditional, desktop versions of Outlook. However, Microsoft is transitioning to a "New Outlook" that is built on web technologies. This new version does not have the same VBA backend, meaning our CreateObject("Outlook.Application") code will fail.

For a freelancer, building a tool that might break when a client updates their software is a significant risk. We need a fallback strategy. The solution is to use a method called SendKeys, which simulates keyboard strokes to control the application's user interface.

This next video, from the "Excel For Freelancers" channel, is specifically designed to solve this exact problem.

How To Send Emails From Excel Using VBA For The New Outlook In 2025

This video directly addresses the compatibility issue with the New Outlook and provides a brilliant solution.

Please watch the following segments carefully: The SendKeys Concept (watch this segment): The instructor explains why the old method fails and introduces the idea of using SendKeys to send keyboard shortcuts to Outlook. Notice the shortcuts for creating a new email (Ctrl+N) and, crucially, for navigating the menus to attach a file (Alt, then N, then AF, then B). Basic SendKeys in Action (watch this segment): This part shows the initial VBA code for automating the New Outlook. You'll see how to activate the Outlook window and send keystrokes for the recipient, subject, and body, using Tab to move between fields. The Complete Solution (watch this segment): This is the most important part. The code here is more advanced. It first determines which version of Outlook is running (new or old). Then, it uses a sophisticated method to activate the Outlook window (even if minimized) and executes the full SendKeys sequence, including the complex steps for adding an attachment.

The SendKeys method is more fragile than the Object Model method—if the user clicks on another window while the macro is running, the keystrokes will be sent to the wrong place! However, it's an invaluable technique to have in your toolkit for automating applications that don't offer a proper object model.

Putting It All Together: A Robust, Professional Macro

A true professional solution anticipates problems. The best approach is to combine both methods into a single, intelligent macro. The code will first check which version of Outlook is running and then execute the appropriate subroutine.

Here is a conceptual structure for what this professional-grade macro would look like. You don't need to build this from scratch, as the final code in the "Excel for Freelancers" video provides a complete working example.

' Main procedure to be assigned to a button
Sub SendEmail_Master()

    ' --- 1. Gather all data from the Excel sheet ---
    ' (e.g., recipient, subject, body text, attachment path)
    
    ' --- 2. Determine Outlook Version ---
    Dim outlookVersion As String
    outlookVersion = GetOutlookVersion() ' A custom function to check running processes
    
    ' --- 3. Call the appropriate sending sub ---
    Select Case outlookVersion
        Case "Legacy"
            Call SendEmail_ObjectModel(recipient, subject, body, attachmentPath)
        Case "New"
            Call SendEmail_SendKeys(recipient, subject, body, attachmentPath)
        Case Else
            MsgBox "Outlook is not running.", vbExclamation
    End Select

End Sub


' --- Helper function to check Outlook version ---
Function GetOutlookVersion() As String
    ' ... Code to check for "OUTLOOK.EXE" or "Olk.exe" processes ...
    ' (As demonstrated in the second video)
End Function


' --- Subroutine for the traditional method ---
Sub SendEmail_ObjectModel(...)
    ' ... Use CreateObject("Outlook.Application") ...
End Sub


' --- Subroutine for the new method ---
Sub SendEmail_SendKeys(...)
    ' ... Use AppActivate and SendKeys ...
End Sub

This modular design is clean, easy to debug, and highly robust, ensuring your tool will work for the widest possible range of clients.

Conclusion

You have now added a powerful and highly marketable skill to your VBA arsenal. Automating emails is a common client request that bridges the gap between data analysis in Excel and communication with the outside world.

Key Takeaways:

The standard method for email automation uses CreateObject("Outlook.Application") to manipulate Outlook's Object Model. This is the most stable and preferred technique.
The key properties to set are .To, .CC, .BCC, .Subject, .Body (for plain text), and .HTMLBody (for formatted text). Use .Attachments.Add to include files.
Use .Display to review an email before sending and .Send to dispatch it automatically.
The "New Outlook" is web-based and requires a different approach using SendKeys to simulate user keyboard input. This method is less robust but essential for modern compatibility.
A professional-quality macro should check the user's Outlook version and automatically choose the correct sending method.

In our next module, we will begin to synthesize all the skills you've learned—from formulas and Power Query to advanced VBA—to build complete, polished, client-ready solutions from the ground up.