5 Ways To Send Email From Excel Vba

Sending emails from Excel VBA can greatly enhance your workflow by automating tasks and streamlining communication. Whether you're sending reports, notifications, or updates, Excel VBA provides several methods to achieve this. Here, we'll explore five ways to send emails from Excel VBA, each with its own strengths and suitable scenarios.

1. Using the CDO (Collaboration Data Objects) Library

The CDO library is a powerful tool for sending emails via VBA. It supports various email protocols, including SMTP and POP3.

How to Use CDO in Excel VBA:

Step 1: Set up your email account and obtain the SMTP server details.
Step 2: Open the Visual Basic Editor in Excel, go to Tools > References, and check if "Microsoft CDO Library" is listed. If not, add it.
Step 3: Write a VBA script using the CDO library to create a new email message, specifying the recipient, subject, and body.

Example Code:

Sub SendEmailUsingCDO()
    Dim cdoMail As New CDO.Message
    Dim cdoConfig As New CDO.Configuration
    
    ' Load the configuration
    cdoConfig.Load -1
    
    ' Set the SMTP server and port
    cdoConfig.Fields(cdoSMTPServer) = "smtp.yourserver.com"
    cdoConfig.Fields(cdoSMTPAuthenticate) = cdoBasic
    cdoConfig.Fields(cdoSendUsingMethod) = cdoSendUsingPort
    cdoConfig.Fields(cdoSMTPServerPort) = 25
    cdoConfig.Fields.Update
    
    ' Create the email
    With cdoMail
       .To = "recipient@example.com"
       .From = "sender@example.com"
       .Subject = "Email from Excel VBA using CDO"
       .TextBody = "This is a test email"
       .Configuration = cdoConfig
       .Send
    End With
    
    ' Clean up
    Set cdoMail = Nothing
    Set cdoConfig = Nothing
End Sub

2. Using the Outlook Object Library

If you have Outlook installed on your system, you can use the Outlook Object Library to send emails. This method integrates well with your existing Outlook account settings.

How to Use Outlook in Excel VBA:

Step 1: Ensure Outlook is installed and configured on your system.
Step 2: In the Visual Basic Editor, go to Tools > References and check if "Microsoft Outlook Object Library" is listed. If not, add it.
Step 3: Write a VBA script using the Outlook library to create a new email, specifying the recipient, subject, and body.

Example Code:

Sub SendEmailUsingOutlook()
    Dim olApp As New Outlook.Application
    Dim olMail As Outlook.MailItem
    
    ' Create a new email
    Set olMail = olApp.CreateItem(olMailItem)
    
    With olMail
       .To = "recipient@example.com"
       .Subject = "Email from Excel VBA using Outlook"
       .Body = "This is a test email"
       .Send
    End With
    
    ' Clean up
    Set olMail = Nothing
    Set olApp = Nothing
End Sub

3. Using the WinHttp Library

The WinHttp library allows you to send HTTP requests, which can be used to send emails via SMTP using an external service.

How to Use WinHttp in Excel VBA:

Step 1: In the Visual Basic Editor, go to Tools > References and check if "Microsoft WinHTTP Services" is listed. If not, add it.
Step 2: Find an external email service that accepts HTTP requests (e.g., Gmail's SMTP server).
Step 3: Write a VBA script using the WinHttp library to send an HTTP request to the email service, specifying the recipient, subject, and body.

Example Code:

Sub SendEmailUsingWinHttp()
    Dim http As New WinHttp.WinHttpRequest
    Dim url As String
    Dim body As String
    
    ' Set up the email service URL and authentication
    url = "https://smtp.gmail.com/v1/users/your_email/messages/send"
    body = "Your email body here"
    
    ' Create the HTTP request
    http.Open "POST", url, False
    
    ' Set headers and authentication
    http.SetRequestHeader "Content-Type", "application/json"
    http.SetRequestHeader "Authorization", "Bearer your_api_key"
    
    ' Send the request
    http.Send body
    
    ' Clean up
    Set http = Nothing
End Sub

4. Using the MailEnvelopes Add-in

The MailEnvelopes add-in is a free tool that simplifies sending emails from Excel.

How to Use MailEnvelopes:

Step 1: Download and install the MailEnvelopes add-in.
Step 2: Restart Excel and access the MailEnvelopes toolbar.
Step 3: Write a VBA script using the MailEnvelopes library to create a new email, specifying the recipient, subject, and body.

Example Code:

Sub SendEmailUsingMailEnvelopes()
    Dim me As New MailEnvelopes.Application
    
    ' Create a new email
    me.CreateMailEnvelope
    
    ' Set the recipient, subject, and body
    me.MailEnvelope.To = "recipient@example.com"
    me.MailEnvelope.Subject = "Email from Excel VBA using MailEnvelopes"
    me.MailEnvelope.Body = "This is a test email"
    
    ' Send the email
    me.MailEnvelope.Send
    
    ' Clean up
    Set me = Nothing
End Sub

5. Using Late Binding with CDO

Late binding allows you to use the CDO library without setting a reference in the Visual Basic Editor.

How to Use Late Binding with CDO:

Step 1: Write a VBA script using late binding to create a new email, specifying the recipient, subject, and body.

Example Code:

Sub SendEmailUsingLateBinding()
    Dim cdoMail As Object
    Dim cdoConfig As Object
    
    ' Create the CDO objects
    Set cdoMail = CreateObject("CDO.Message")
    Set cdoConfig = CreateObject("CDO.Configuration")
    
    ' Load the configuration
    cdoConfig.Load -1
    
    ' Set the SMTP server and port
    cdoConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.yourserver.com"
    cdoConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    cdoConfig.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    cdoConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    cdoConfig.Fields.Update
    
    ' Create the email
    With cdoMail
       .To = "recipient@example.com"
       .From = "sender@example.com"
       .Subject = "Email from Excel VBA using Late Binding"
       .TextBody = "This is a test email"
       .Configuration = cdoConfig
       .Send
    End With
    
    ' Clean up
    Set cdoMail = Nothing
    Set cdoConfig = Nothing
End Sub

Each of these methods has its own strengths and weaknesses. When choosing a method, consider factors such as your email provider, the complexity of your email needs, and your personal preference.