How to Send Email from Excel VBA Without Outlook in 3 Minutes

Written by Kasper Langmann

Excel VBA, or Visual Basic for Applications, is a powerful tool that allows you to automate tasks in Excel. One such task is sending emails directly from Excel, without the need for Outlook. This can save you time and streamline your workflow, especially if you regularly send emails containing data from Excel. In this guide, we will walk you through the process of sending an email from Excel VBA without Outlook in just three minutes.

Understanding Excel VBA

Before we dive into the process of sending emails from Excel VBA, it’s important to understand what VBA is and how it works. VBA stands for Visual Basic for Applications, which is a programming language developed by Microsoft. It’s used to automate tasks in Microsoft Office applications, including Excel.

With VBA, you can create macros, or sets of instructions, that Excel can execute automatically. These macros can perform a wide range of tasks, from simple actions like copying and pasting data, to more complex operations like sending emails.

Why Use Excel VBA?

Excel VBA can be a powerful tool for automating tasks and improving your productivity. By automating repetitive tasks, you can save time and reduce the risk of errors. Additionally, VBA allows you to customize Excel to better suit your needs, making it a more effective tool for data analysis and management.

One of the most useful features of Excel VBA is its ability to interact with other applications. This means you can use VBA to send emails directly from Excel, without the need for Outlook or another email client. This can be particularly useful if you regularly send emails containing data from Excel, as it allows you to automate the process and ensure accuracy.

Setting Up Excel VBA for Email

Before you can send emails from Excel VBA, you’ll need to set up your VBA environment. This involves enabling the Developer tab in Excel, which gives you access to the VBA editor. You’ll also need to add a reference to the Microsoft CDO for Windows 2000 Library, which provides the functionality needed to send emails.

To enable the Developer tab, click on the File tab in Excel, then select Options. In the Excel Options window, click on Customize Ribbon, then check the box for Developer under the Main Tabs section. Click OK to close the window.

Adding a Reference to the Microsoft CDO Library

Once you’ve enabled the Developer tab, you can add a reference to the Microsoft CDO Library. To do this, open the VBA editor by clicking on the Developer tab, then selecting Visual Basic. In the VBA editor, click on Tools, then References.

In the References dialog box, scroll down until you find Microsoft CDO for Windows 2000 Library. Check the box next to it, then click OK to close the dialog box. You’re now ready to start writing your VBA code to send emails.

Writing the VBA Code to Send Emails

The next step is to write the VBA code that will send your emails. This code will use the CDO Library to create and send an email. The code will need to include the SMTP server details for your email provider, as well as the email address and password for the account you want to send the email from.

Here’s an example of what the code might look like:

Sub SendEmail()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1
    Set Flds = iConf.Fields

    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Update
    End With

    strbody = "This is a test email from Excel VBA."

    With iMsg
        Set .Configuration = iConf
        .To = "recipient@example.com"
        .CC = ""
        .BCC = ""
        .From = "sender@example.com"
        .Subject = "Test Email"
        .TextBody = strbody
        .Send
    End With

    Set iMsg = Nothing
    Set iConf = Nothing
End Sub

This code creates a new email, sets the SMTP server details, and sends the email. You’ll need to replace the SMTP server, port, and email details with your own information.

Conclusion

Sending emails from Excel VBA without Outlook can be a powerful tool for automating tasks and improving your productivity. By following the steps outlined in this guide, you can set up your VBA environment and write the code needed to send emails in just a few minutes. Remember to always test your code thoroughly to ensure it’s working correctly and sending emails as expected.