7 Ways To Send Email From Excel Using Vba

Sending emails from Excel using VBA can be a powerful tool for automating tasks and streamlining workflows. With just a few lines of code, you can send emails to clients, colleagues, or anyone else, directly from your Excel spreadsheet. In this article, we'll explore seven ways to send email from Excel using VBA.

Why Send Email from Excel?

Before we dive into the seven methods, let's explore why sending email from Excel is so useful. By integrating email functionality into your Excel spreadsheets, you can:

• Automate routine tasks, such as sending reports or updates
• Personalize emails with data from your spreadsheet
• Use Excel formulas to generate email content
• Simplify workflows and save time

Method 1: Using the MailEnvelope Object

The MailEnvelope object is a built-in VBA object that allows you to send emails from Excel. To use this method, follow these steps:

• Open the Visual Basic Editor (VBE) by pressing Alt + F11 or navigating to Developer > Visual Basic
• In the VBE, insert a new module by clicking Insert > Module
• Paste the following code into the module:

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

• Replace the recipient email address and subject line with your own values
• Run the macro by clicking Developer > Macros > SendEmailUsingMailEnvelope

Method 2: Using Late Binding with Outlook

Late binding allows you to use Outlook objects without setting a reference to the Outlook library. To use this method, follow these steps:

• Open the VBE and insert a new module
• Paste the following code into the module:

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

• Replace the recipient email address and subject line with your own values
• Run the macro by clicking Developer > Macros > SendEmailUsingLateBinding

Method 3: Using Early Binding with Outlook

Early binding requires you to set a reference to the Outlook library. To use this method, follow these steps:

• Open the VBE and insert a new module
• Click Tools > References and check the box next to "Microsoft Outlook XX.X Object Library"
• Paste the following code into the module:

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

• Replace the recipient email address and subject line with your own values
• Run the macro by clicking Developer > Macros > SendEmailUsingEarlyBinding

Method 4: Using a SMTP Server

You can also use a SMTP server to send emails from Excel. To use this method, follow these steps:

• Open the VBE and insert a new module
• Paste the following code into the module:

Sub SendEmailUsingSMTP()
    Dim smtp As Object
    Dim mail As Object
    
    Set smtp = CreateObject("WinHttp.WinHttpRequest.5.1")
    Set mail = CreateObject("ADODB.Stream")
    
    smtp.Open "POST", "smtp://smtp.example.com", False
    smtp.setRequestHeader "Content-Type", "text/plain"
    smtp.send "To: recipient@example.com" & vbCrLf & "Subject: Test Email from Excel" & vbCrLf & "This is a test email sent from Excel using VBA."
    
    Set mail = Nothing
    Set smtp = Nothing
End Sub

• Replace the SMTP server address, recipient email address, and subject line with your own values
• Run the macro by clicking Developer > Macros > SendEmailUsingSMTP

Method 5: Using a Third-Party Library

You can also use a third-party library, such as MailKit, to send emails from Excel. To use this method, follow these steps:

• Open the VBE and insert a new module
• Download and install the MailKit library
• Paste the following code into the module:

Sub SendEmailUsingMailKit()
    Dim mail As New MailKit.MailMessage
    
    mail.To.Add "recipient@example.com"
    mail.Subject = "Test Email from Excel"
    mail.Body = "This is a test email sent from Excel using VBA."
    
    Dim smtp As New MailKit.SmtpClient
    smtp.Connect "smtp.example.com", 587, False
    smtp.Authenticate "username", "password"
    smtp.Send mail
    smtp.Disconnect
    
    Set mail = Nothing
    Set smtp = Nothing
End Sub

• Replace the recipient email address, subject line, SMTP server address, username, and password with your own values
• Run the macro by clicking Developer > Macros > SendEmailUsingMailKit

Method 6: Using a CDO (Collaboration Data Objects) Object

You can also use a CDO object to send emails from Excel. To use this method, follow these steps:

• Open the VBE and insert a new module
• Paste the following code into the module:

Sub SendEmailUsingCDO()
    Dim cdo As Object
    Dim config As Object
    Dim message As Object
    
    Set cdo = CreateObject("CDO.Message")
    Set config = CreateObject("CDO.Configuration")
    
    config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.example.com"
    config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"
    config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
    
    cdo.Configuration = config
    cdo.To = "recipient@example.com"
    cdo.Subject = "Test Email from Excel"
    cdo.TextBody = "This is a test email sent from Excel using VBA."
    
    cdo.Send
    
    Set message = Nothing
    Set config = Nothing
    Set cdo = Nothing
End Sub

• Replace the recipient email address, subject line, SMTP server address, username, and password with your own values
• Run the macro by clicking Developer > Macros > SendEmailUsingCDO

Method 7: Using a Gmail Account

You can also use a Gmail account to send emails from Excel. To use this method, follow these steps:

• Open the VBE and insert a new module
• Paste the following code into the module:

Sub SendEmailUsingGmail()
    Dim smtp As Object
    Dim mail As Object
    
    Set smtp = CreateObject("WinHttp.WinHttpRequest.5.1")
    Set mail = CreateObject("ADODB.Stream")
    
    smtp.Open "POST", "smtp.gmail.com", False
    smtp.setRequestHeader "Content-Type", "text/plain"
    smtp.send "To: recipient@example.com" & vbCrLf & "Subject: Test Email from Excel" & vbCrLf & "This is a test email sent from Excel using VBA."
    
    Set mail = Nothing
    Set smtp = Nothing
End Sub

• Replace the recipient email address and subject line with your own values
• Note: You need to allow less secure apps to access your Gmail account for this method to work
• Run the macro by clicking Developer > Macros > SendEmailUsingGmail

Conclusion

In this article, we explored seven ways to send email from Excel using VBA. Each method has its own advantages and disadvantages, and the choice of method depends on your specific needs and requirements. By using VBA to send emails from Excel, you can automate routine tasks, personalize emails with data from your spreadsheet, and simplify workflows.