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()
 
Dim NewMail As CDO.Message
 
Set NewMail = New CDO.Message

'Enable SSL Authentication
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

'Make SMTP authentication Enabled=true (1)

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

'Set the SMTP server and port Details
'To get these details you can get on Settings Page of your Gmail Account

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Set your credentials of your Gmail Account

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "vishwamitra01@gmail.com"

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*********"

'Update the configuration fields
NewMail.Configuration.Fields.Update
 
'Set All Email Properties

With NewMail
  .Subject = "Test Mail from LearnExcelMacro.com"
  .From = "vishwamitra01@gmail.com"
  .To = "vishwamitra02@gmail.com;info@learnexcelmacro.com"
  .CC = "vishwamitra01@gmail.com"
  .BCC = ""
  .textbody = ""
End With


NewMail.Send
MsgBox ("Mail has been Sent")

'Set the NewMail Variable to Nothing
Set NewMail = Nothing
 
End Sub

2. Excel VBA: Send email from Yahoo



Sub SendEmailUsingYahoo()
 
Dim NewMail As CDO.Message
 
Set NewMail = New CDO.Message

'Enable SSL Authentication
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

'Make SMTP authentication Enabled=true (1)

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

'Set the SMTP server and port Details
'To get these details you can get on Settings Page of your Yahoo Account

myMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mail.yahoo.com"

myMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465

myMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Set your credentials of your Gmail Account

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "vishwamitra01@yahoo.com"

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "**********"

'Update the configuration fields
NewMail.Configuration.Fields.Update
 
'Set All Email Properties

With NewMail
  .Subject = "Test Mail from LearnExcelMacro.com"
  .From = "vishwamitra01@yahoo.com"
  .To = "vishwamitra02@gmail.com;info@learnexcelmacro.com"
  .CC = "vishwamitra01@gmail.com"
  .BCC = ""
  .textbody = ""
End With


NewMail.Send
MsgBox ("Mail has been Sent")

'Set the NewMail Variable to Nothing
Set NewMail = Nothing
 
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 = "vishwamitra01@yahoo.com"
  .To = "vishwamitra02@gmail.com;info@learnexcelmacro.com"
  .CC = "vishwamitra01@gmail.com"
  .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 = "vishwamitra01@yahoo.com"
  .To = "vishwamitra02@gmail.com;info@learnexcelmacro.com"
  .CC = "vishwamitra01@gmail.com"
  .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!