3 Ways To Send Email From Excel Using Vba

Sending emails from Excel can greatly enhance your productivity and workflow. Whether you need to send automated reports, notifications, or updates, Excel's Visual Basic for Applications (VBA) provides a robust platform to achieve this. In this article, we will explore three methods to send emails from Excel using VBA.

Method 1: Using the CDO (Collaboration Data Objects) Library

The CDO library is a powerful tool for sending emails from Excel. To use this method, you need to add the CDO library to your Excel VBA project. Here's how:

Open the Visual Basic Editor in Excel by pressing Alt + F11 or navigating to Developer > Visual Basic.
In the Visual Basic Editor, click Tools > References.
Scroll down and check the box next to "Microsoft CDO Library".
Click OK.

Now you can use the CDO library to send emails. Here's an example code:

Sub SendEmailUsingCDO()
    Dim cdoConfig As Object
    Dim cdoMessage As Object
    
    Set cdoConfig = CreateObject("CDO.Configuration")
    Set cdoMessage = CreateObject("CDO.Message")
    
    With cdoConfig
       .Fields(cdoSMTPServer) = "smtp.gmail.com"
       .Fields(cdoSMTPServerPort) = 465
       .Fields(cdoSMTPUseSSL) = True
       .Fields(cdoSendUsingMethod) = cdoSendUsingPort
    End With
    
    With cdoMessage
       .To = "recipient@example.com"
       .From = "sender@example.com"
       .Subject = "Test Email from Excel VBA"
       .TextBody = "This is a test email sent from Excel VBA using CDO library."
    End With
    
    cdoMessage.Configuration = cdoConfig
    cdoMessage.Send
    
    Set cdoMessage = Nothing
    Set cdoConfig = Nothing
End Sub

This code creates a new email message using the CDO library and sends it to the specified recipient.

Method 2: Using the Outlook Object Library

Using the Outlook Object Library in Excel VBA

If you have Microsoft Outlook installed on your system, you can use the Outlook Object Library to send emails from Excel. To use this method, you need to add the Outlook Object Library to your Excel VBA project. Here's how:

Open the Visual Basic Editor in Excel by pressing Alt + F11 or navigating to Developer > Visual Basic.
In the Visual Basic Editor, click Tools > References.
Scroll down and check the box next to "Microsoft Outlook XX.X Object Library" (where XX.X is the version of Outlook installed on your system).
Click OK.

Now you can use the Outlook Object Library to send emails. Here's an example code:

Sub SendEmailUsingOutlook()
    Dim olApp As Object
    Dim olMail As Object
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0)
    
    With olMail
       .To = "recipient@example.com"
       .From = "sender@example.com"
       .Subject = "Test Email from Excel VBA"
       .Body = "This is a test email sent from Excel VBA using Outlook library."
    End With
    
    olMail.Send
    
    Set olMail = Nothing
    Set olApp = Nothing
End Sub

This code creates a new email message using the Outlook Object Library and sends it to the specified recipient.

Method 3: Using the Mail Envelope Method

Using the Mail Envelope Method in Excel VBA

The Mail Envelope method is a simple and straightforward way to send emails from Excel. This method uses the Shell function to execute a command that opens the default email client on the system.

Here's an example code:

Sub SendEmailUsingMailEnvelope()
    Dim mailTo As String
    Dim subject As String
    Dim body As String
    
    mailTo = "recipient@example.com"
    subject = "Test Email from Excel VBA"
    body = "This is a test email sent from Excel VBA using Mail Envelope method."
    
    Dim shellCommand As String
    shellCommand = "mailto:" & mailTo & "?subject=" & subject & "&body=" & body
    
    Shell "start " & shellCommand, vbNormalFocus
End Sub

This code creates a new email message using the Mail Envelope method and opens the default email client on the system.

Comparison of Methods

Each of the three methods has its own advantages and disadvantages. Here's a brief comparison:

CDO Library: This method provides a high degree of control over the email message, including the ability to specify the SMTP server and port. However, it requires the CDO library to be installed on the system.
Outlook Object Library: This method provides a high degree of control over the email message, including the ability to specify the Outlook application and account. However, it requires Outlook to be installed on the system.
Mail Envelope Method: This method is simple and straightforward, but provides limited control over the email message.

Conclusion

Sending emails from Excel using VBA can greatly enhance your productivity and workflow. In this article, we explored three methods for sending emails from Excel: using the CDO library, using the Outlook Object Library, and using the Mail Envelope method. Each method has its own advantages and disadvantages, and the choice of method depends on your specific needs and requirements.