Dear Readers,

In this article we are going to learn how to send an email automatically by Excel Excel VBA. While executing the Excel Macro some times you may need to send some Details or Result directly to your email ID or any other ID using Excel Macro. If you have some report or details which you want to send in a mail without log-in to your email account.
This you are going to achieve by using CDO Object.

What is CDO (Collaboration Data Objects)??

. This Object is basically used for Active Messaging, Enables user to Access Global Server Objects and Address List For more about this read this Microsoft Help.

In Microsoft Excel, to use this Object you need to Add CDO Reference.

Add CDO Reference

Add CDO Reference

Note: Without adding this reference you can Run the below Code. For running this Code, you can use below line to create Object runtime:


Set myMail = CreateObject("CDO.Message")

In this article, i will explain you 2 simple methods, to send an email by Excel VBA.

1. Excel VBA: Send email from Gmail



Sub SendEmailUsingGmail()
    
    On Error GoTo Err
    
    Dim NewMail As Object
    Dim mailConfig As Object
    Dim fields As Variant
    Dim msConfigURL As String
    
    Set NewMail = CreateObject("CDO.Message")
    Set mailConfig = CreateObject("CDO.Configuration")
    
    ' load all default configurations
    mailConfig.Load -1
    
    Set fields = mailConfig.fields
    
'Set All Email Properties
    
    With NewMail
        .Subject = "Test Mail from LearnExcelMacro.com"
        .From = "[email protected]"
        .To = "[email protected];[email protected]"
        .CC = "[email protected]"
        .BCC = ""
        .textbody = ""
    End With
    
    msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
    
    With fields
        'Enable SSL Authentication
        .Item(msConfigURL & "/smtpusessl") = True
        
        'Make SMTP authentication Enabled=true (1)
        .Item(msConfigURL & "/smtpauthenticate") = 1
        
        'Set the SMTP server and port Details
        'To get these details you can get on Settings Page of your Gmail Account
        .Item(msConfigURL & "/smtpserver") = "smtp.gmail.com"
        .Item(msConfigURL & "/smtpserverport") = 465
        .Item(msConfigURL & "/sendusing") = 2
        
        'Set your credentials of your Gmail Account
        .Item(msConfigURL & "/sendusername") = "[email protected]"
        .Item(msConfigURL & "/sendpassword") = "********"
        
        'Update the configuration fields
        .Update
        
    End With
    NewMail.Configuration = mailConfig
    NewMail.Send
    MsgBox ("Mail has been Sent")
    
Exit_Err:
    
    Set NewMail = Nothing
    Set mailConfig = Nothing
    End
    
Err:
    Select Case Err.Number
    
    Case -2147220973  'Could be because of Internet Connection
        MsgBox " Could be no Internet Connection !!  -- " & Err.Description
        
    Case -2147220975  'Incorrect credentials User ID or password
        MsgBox "Incorrect Credentials !!  -- " & Err.Description
        
    Case Else   'Rest other errors
        MsgBox "Error occured while sending the email !!  -- " & Err.Description
    End Select
    
    Resume Exit_Err
    
End Sub

2. Excel VBA: Send email from Yahoo



Sub SendEmailUsingYahoo()
    
    On Error GoTo Err
    
    Dim NewMail As Object
    Dim mailConfig As Object
    Dim fields As Variant
    Dim msConfigURL As String
    
    Set NewMail = CreateObject("CDO.Message")
    Set mailConfig = CreateObject("CDO.Configuration")
    
    ' load all default configurations
    mailConfig.Load -1
    
    Set fields = mailConfig.fields
    
'Set All Email Properties
    
    With NewMail
        .Subject = "Test Mail from LearnExcelMacro.com"
        .From = "[email protected]"
        .To = "[email protected];[email protected]"
        .CC = "[email protected]"
        .BCC = ""
        .textbody = ""
    End With
    
    msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
    
    With fields
        'Enable SSL Authentication
        .Item(msConfigURL & "/smtpusessl") = True
        
        'Make SMTP authentication Enabled=true (1)
        .Item(msConfigURL & "/smtpauthenticate") = 1
        
        'Set the SMTP server and port Details
        'To get these details you can get on Settings Page of your yahoo Account
        .Item(msConfigURL & "/smtpserver") = "smtp.mail.yahoo.com"
        .Item(msConfigURL & "/smtpserverport") = 465
        .Item(msConfigURL & "/sendusing") = 2
        
        'Set your credentials of your yahoo Account
        .Item(msConfigURL & "/sendusername") = "[email protected]"
        .Item(msConfigURL & "/sendpassword") = "******"
        
        'Update the configuration fields
        .Update
        
    End With
    NewMail.Configuration = mailConfig
    NewMail.Send
    MsgBox ("Mail has been Sent")
    
Exit_Err:
    
    Set NewMail = Nothing
    Set mailConfig = Nothing
    End
    
Err:
    Select Case Err.Number
    
    Case -2147220973  'Could be because of Internet Connection
        MsgBox " Could be no Internet Connection !!  -- " & Err.Description
        
    Case -2147220975  'Incorrect credentials User ID or password
        MsgBox "Incorrect Credentials !!  -- " & Err.Description
        
    Case Else   'Rest other errors
        MsgBox "Error occured while sending the email !!  -- " & Err.Description
    End Select
    
    Resume Exit_Err
    
End Sub
 

Excel VBA to Send Email with HTML Body

For sending HTML Body you just need to Change the Email Properties which you are setting above



With NewMail
  .Subject = "Test Mail from LearnExcelMacro.com"
  .From = "[email protected]"
  .To = "[email protected];[email protected]"
  .CC = "[email protected]"
  .BCC = ""
  .HTMLBody = "Write your complete HTML Page"
End With

Excel VBA to Send Email with an Attachment


With NewMail
  .Subject = "Test Mail from LearnExcelMacro.com"
  .From = "[email protected]"
  .To = "[email protected];[email protected]"
  .CC = "[email protected]"
  .BCC = ""
  .HTMLBody = "Write your complete HTML Page"

' For multiple Attachment you can add below lines as many times

  .AddAttachment "C:\ExcelMacro-help.xls"
  .AddAttachment "C:\ExcelMacro-help2.xls"
End With

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!