5 Ways To Send Email Using Vba

Using VBA to send emails can greatly enhance productivity and efficiency in various applications. Whether you're working with Excel, Word, or Outlook, VBA provides a powerful toolset to automate email-related tasks. Here, we'll explore five ways to send email using VBA, focusing on different aspects of email automation.

1. Sending Emails with Outlook Using VBA

One of the most straightforward methods to send emails using VBA is by leveraging Outlook's capabilities. This approach is especially useful when working within the Outlook application or when integrating with other Office applications.

Example Code:

Sub SendEmailUsingOutlook()
    Dim olApp As Object
    Dim olMail As Object
    
    ' Create a new instance of Outlook
    Set olApp = CreateObject("Outlook.Application")
    
    ' Create a new email
    Set olMail = olApp.CreateItem(0)
    
    ' Set email properties
    With olMail
       .To = "recipient@example.com"
       .Subject = "Test Email from VBA"
       .Body = "This is a test email sent from VBA."
       .Send
    End With
    
    ' Clean up
    Set olMail = Nothing
    Set olApp = Nothing
End Sub

2. Sending Emails with CDO (Collaboration Data Objects)

CDO provides another way to send emails without requiring the Outlook application to be open. This method is useful for server-side applications or scenarios where Outlook is not installed.

Example Code:

Sub SendEmailUsingCDO()
    Dim cdoMail As Object
    Dim cdoConfig As Object
    
    ' Create a new CDO mail object
    Set cdoMail = CreateObject("CDO.Message")
    
    ' Create a new CDO configuration object
    Set cdoConfig = CreateObject("CDO.Configuration")
    
    ' Set configuration fields
    With cdoConfig.Fields
       .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.example.com"
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
       .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "your_username"
       .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your_password"
       .Update
    End With
    
    ' Set email properties
    With cdoMail
        Set.Configuration = cdoConfig
       .To = "recipient@example.com"
       .Subject = "Test Email from VBA using CDO"
       .TextBody = "This is a test email sent from VBA using CDO."
       .Send
    End With
    
    ' Clean up
    Set cdoMail = Nothing
    Set cdoConfig = Nothing
End Sub

3. Sending Emails with Gmail Using VBA

For sending emails through Gmail, you can use the SchUseGmail utility or integrate with the Gmail API. However, for simplicity and without requiring API keys, you can use CDO and configure it to work with Gmail's SMTP server.

Example Code (Modified from CDO Example):

Sub SendEmailUsingGmail()
    Dim cdoMail As Object
    Dim cdoConfig As Object
    
    ' Create a new CDO mail object
    Set cdoMail = CreateObject("CDO.Message")
    
    ' Create a new CDO configuration object
    Set cdoConfig = CreateObject("CDO.Configuration")
    
    ' Set configuration fields for Gmail
    With cdoConfig.Fields
       .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/smtpport") = 465
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
       .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "your_gmail_username"
       .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your_gmail_password"
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
       .Update
    End With
    
    ' Set email properties
    With cdoMail
        Set.Configuration = cdoConfig
       .To = "recipient@example.com"
       .Subject = "Test Email from VBA using Gmail"
       .TextBody = "This is a test email sent from VBA using Gmail."
       .Send
    End With
    
    ' Clean up
    Set cdoMail = Nothing
    Set cdoConfig = Nothing
End Sub

4. Sending Emails with Outlook Using Late Binding

Late binding provides flexibility by not requiring early binding references to Outlook libraries. This approach is useful in scenarios where different versions of Outlook might be installed.

Example Code:

Sub SendEmailUsingOutlookLateBinding()
    Dim olApp As Object
    Dim olMail As Object
    
    ' Create a new instance of Outlook
    Set olApp = CreateObject("Outlook.Application")
    
    ' Create a new email
    Set olMail = olApp.CreateItem(0)
    
    ' Set email properties
    With olMail
       .To = "recipient@example.com"
       .Subject = "Test Email from VBA using Late Binding"
       .Body = "This is a test email sent from VBA using Late Binding."
       .Send
    End With
    
    ' Clean up
    Set olMail = Nothing
    Set olApp = Nothing
End Sub

5. Sending Emails with Mail Envelope Using VBA

Mail Envelope is a simple and less commonly used method for sending emails from VBA. It requires creating a new mail session and then using the SendMail method.

Example Code:

Sub SendEmailUsingMailEnvelope()
    Dim ms As Object
    Set ms = CreateObject("MailEnvelope")
    
    ' Set email properties
    With ms
       .To = "recipient@example.com"
       .Subject = "Test Email from VBA using Mail Envelope"
       .Body = "This is a test email sent from VBA using Mail Envelope."
       .Send
    End With
    
    ' Clean up
    Set ms = Nothing
End Sub

Gallery of Email Sending Methods

FAQs

We hope this comprehensive guide to sending emails using VBA has been helpful in exploring different methods to suit your needs. Remember to consider factors such as ease of use, flexibility, and security when choosing the best approach for your project.