We can send email using Google mail directly from Excel with VBA. We can do this using CDO. What is CDO? CDO stands for Collaboration Data Objects. Using the CDO library from References via Tools in the Visual Basic for Applications editor, we can add email capabilities to our VBA code. In end effect, we actually use the SMTP (Simple Mail Transfer Protocol) server of the mail provider. We then configure the details like SSL (Secure Socket Layer) authentication, SMTP authentication, SMTP server name, SMTP port, username and password via VBA. Once that is done, we set the email properties like Subject, From, To, Body, CC, BCC, etc. We can also send attachments like text, word documents, PowerPoint, Excel files, etc.
View the training video before you study the complete VBA code given below:

View this video on YouTube.

Sub send_email_via_Gmail()
Dim myMail As CDO.Message

Set myMail = New CDO.Message

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25

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

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “[email protected]”

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “password”

myMail.Configuration.Fields.Update

With myMail
.Subject = “Test Email from Dr. Takyar”
.From = “[email protected]”
.To = “[email protected]; [email protected]”
.CC = “[email protected]”
.BCC = “”
.TextBody = “Good morning!”
.AddAttachment “C:\Users\takyar\Desktop\email-via-gmail.txt”
End With
On Error Resume Next
myMail.Send
‘MsgBox(“Mail has been sent”)
Set myMail = Nothing

End Sub

Using Yahoo with VBA:
Sub email_using_Yahoo_VBA()

Dim myMail As CDO.Message

Set myMail = New CDO.Message

‘Enable SSL Authentication
myMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

‘SMTP authentication Enabled

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1

‘Set the SMTP server and port details

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 username and password for your Yahoo Account

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “[email protected]”

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “password”

‘Update all configuration fields
myMail.Configuration.Fields.Update

‘Set the email properties

With myMail
.Subject = “Test Mail from Dr. takyar”
.From = “[email protected]”
.To = “[email protected]; [email protected]”
.CC = “[email protected]”
.BCC = “”
.TextBody = “Welcome to MS Excel Training!”
End With

myMail.Send
MsgBox (“Mail sent”)

‘Set myMail Variable to Nothing to free memory
Set myMail = Nothing

End Sub

Download a sample file:

Further reading:
Sending mail from Excel with CDO
How to send mail from VBA using CDO