Sub Send_Email_With_Gmail()
Dim lrs As Long
Dim newMail As CDO.Message
Dim mailConfiguration As CDO.Configuration
Dim fields As Variant
Dim msConfigURL As String
Dim subject, from, dest, cc, attchmnt, txtbdy, usrname, pass As String
lrs = Cells(Rows.Count, "B").End(xlUp).Row
subject = Sheet1.Range("D2").Value
from = Sheet1.Range("A2").Value
cc = Sheet1.Range("c2").Value
attchmnt = ""
txtbdy = Sheet1.Range("E2").Value
usrname = Sheet1.Range("f2").Value
pass = Sheet1.Range("G2").Value
On Error GoTo errHandle
Set newMail = New CDO.Message
Set mailConfiguration = New CDO.Configuration
mailConfiguration.Load -1
For i = 2 To lrs
dest = Sheet1.Range("B" & i).Value
Set fields = mailConfiguration.fields
With newMail
.subject = subject
.from = from
.To = dest
.cc = ""
.BCC = ""
' To set email body as HTML, use .HTMLBody
' To send a complete webpage, use .CreateMHTMLBody
.TextBody = txtbdy
.AddAttachment "C:\Users\s.abhijeet\Desktop\Self-Assesment\Vba\Projects\project.xlsx"
End With
msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
With fields
.Item(msConfigURL & "/smtpusessl") = True
.Item(msConfigURL & "/smtpauthenticate") = 1
.Item(msConfigURL & "/smtpserver") = "smtp.gmail.com"
.Item(msConfigURL & "/smtpserverport") = 465
.Item(msConfigURL & "/sendusing") = 2
.Item(msConfigURL & "/sendusername") = usrname
.Item(msConfigURL & "/sendpassword") = pass
.Update
End With
newMail.Configuration = mailConfiguration
newMail.Send
Next i
MsgBox "E-Mail has been sent", vbInformation
exit_line:
'// Release object memory
Set newMail = Nothing
Set mailConfiguration = Nothing
Exit Sub
errHandle:
MsgBox "Error: " & Err.Description, vbInformation
GoTo exit_line
End Sub