Sending mail from excel data to GMAILS using VBA Script

[satyarankireddy]

Hello Guru’s

I would require your help on below requirement. I am new in VBA script this is my first attempt.
Currently am sending mails one by one with manual change in below code. Now I am planning to change code in automation way.
I have excel with data as below.

[TABLE="width: 475"]
<tbody>[TR]
[TD]SNO[/TD]
[TD]Name[/TD]
[TD]Mail ID[/TD]
[TD]Amount[/TD]
[TD]PDF attachement path[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]vardhan[/TD]
[TD]vardhan@gmail.com[/TD]
[TD]1000[/TD]
[TD]D:\vardhan.pdf[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]vihas[/TD]
[TD]vihas@gmail.com[/TD]
[TD]1000[/TD]
[TD]D:\vihas.pdf[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]satya[/TD]
[TD]satya@gmail.com[/TD]
[TD]1000[/TD]
[TD]D:\Satya.pdf[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]varun[/TD]
[TD]varun@gmail.com[/TD]
[TD]1000[/TD]
[TD]D:\Varun.pdf[/TD]
[/TR]
</tbody>[/TABLE]

Based on above cell values need to send mail each one with pdf attachment in separately with one click.
Currently am using below code changing values manually one by one. It is consuming time.

Code :-
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") = 465
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") ="xxxx@gmail.com"
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxx"

myMail.Configuration.Fields.Update

With myMail
.Subject = "Test Email from Dr.xxx"
.From = "xxx@gmail.com"
.To = "xxxx@gmail.com"
.CC = "xxx@gmail.com"
.BCC = ""
.TextBody = "Good morning!"
.AddAttachment "D:\xxx.txt"
End With
On Error Resume Next
myMail.Send
'MsgBox ("Mail has been sent")
Set myMail = Nothing

End Sub

Can you please help or provide me code which is similar for my requirement. Highly appreciated.

 

[yky]

Assuming SNO, Name, etc. are on row 1, try the following modification:

Hello Guru’s

I would require your help on below requirement. I am new in VBA script this is my first attempt.
Currently am sending mails one by one with manual change in below code. Now I am planning to change code in automation way.
I have excel with data as below.

[TABLE="width: 475"]
[TR]
[TD]SNO[/TD]
[TD]Name[/TD]
[TD]Mail ID[/TD]
[TD]Amount[/TD]
[TD]PDF attachement path[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]vardhan[/TD]
[TD]vardhan@gmail.com[/TD]
[TD]1000[/TD]
[TD]D:\vardhan.pdf[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]vihas[/TD]
[TD]vihas@gmail.com[/TD]
[TD]1000[/TD]
[TD]D:\vihas.pdf[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]satya[/TD]
[TD]satya@gmail.com[/TD]
[TD]1000[/TD]
[TD]D:\Satya.pdf[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]varun[/TD]
[TD]varun@gmail.com[/TD]
[TD]1000[/TD]
[TD]D:\Varun.pdf[/TD]
[/TR]
[/TABLE]

Based on above cell values need to send mail each one with pdf attachment in separately with one click.
Currently am using below code changing values manually one by one. It is consuming time.

Code :-
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") = 465
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") ="xxxx@gmail.com"
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxx"

myMail.Configuration.Fields.Update

Dim i As Integer

for i = 1 to 4 step 1

With myMail
.Subject = "Test Email from Dr.xxx"
.From = "xxx@gmail.com"
.To = ActiveSheet.Cells(i+1,3)
.CC = "xxx@gmail.com"
.BCC = ""
.TextBody = "Good morning!"
.AddAttachment ActiveSheet.Cells(i+1,5)
End With
On Error Resume Next
myMail.Send
'MsgBox ("Mail has been sent")

Next i

Set myMail = Nothing

End Sub