• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Sending mail based on excel data to GMAILS using VBA Script|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Sending mail based on excel data to GMAILS using VBA Script|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & Macros Sending mail based on excel data t…
sp_PrintTopic sp_TopicIcon
Sending mail based on excel data to GMAILS using VBA Script
Avatar
SATYA RANKIREDDY

New Member
Members
Level 0
Forum Posts: 1
Member Since:
December 16, 2018
sp_UserOfflineSmall Offline
1
December 16, 2018 - 9:07 am
sp_Permalink sp_Print

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.

SNO Name Mail ID Amount PDF attachement path
1 vardhan vardhan@gmail.com 1000 D:\vardhan.pdf
2 vihas vihas@gmail.com 1000 D:\vihas.pdf
3 satya satya@gmail.com 1000 D:\Satya.pdf
4 varun varun@gmail.com 1000 D:\Varun.pdf

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
December 19, 2018 - 3:17 pm
sp_Permalink sp_Print

Hi,

I updated our code and inserted a loop. Make sure you change the column letters as needed, at this moment they are all pointing to column A to take data from, change the column in Tbl.Cells(i, "A").Value:

Sub send_email_via_Gmail()
Dim myMail As CDO.Message
Set myMail = New CDO.Message
Dim Tbl as Worksheet, i as long
Set Tbl=ThisWorkbook.Worksheets("Sheet1")
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

For i= 2 to tbl.Cells.Find("*", Tbl.Cells(1), , , xlByRows, xlPrevious).Row
With myMail
.Subject = "Test Email from Dr." & Tbl.Cells(i, "A").Value
.From = Cstr(Tbl.Cells(i, "A").Value)
.To = Cstr(Tbl.Cells(i, "A").Value)
.CC = Cstr(Tbl.Cells(i, "A").Value)
.BCC = ""
.TextBody = "Good morning!"
.AddAttachment Cstr(Tbl.Cells(i, "A").Value)
End With
'On Error Resume Next
myMail.Send
Next
'MsgBox ("Mail has been sent")
Set myMail = Nothing

End Sub

Avatar
Sama Ali

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
June 20, 2021
sp_UserOfflineSmall Offline
3
June 20, 2021 - 7:01 am
sp_Permalink sp_Print

Hi It worked but the attachment macro seems to add all the attachments for each sender. Could you please see if there is an alternative coding you can suggest?

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
June 22, 2021 - 3:47 pm
sp_Permalink sp_Print sp_EditHistory

Hi,

You can reorganize the code:

Sub SendAll()
Dim Tbl as Worksheet, i as long
Set Tbl=ThisWorkbook.Worksheets("Sheet1")
For i= 2 to tbl.Cells.Find("*", Tbl.Cells(1), , , xlByRows, xlPrevious).Row
send_email_via_Gmail i, Tbl
Next
End Sub

Sub send_email_via_Gmail(ByVal i as long, Byval Tbl as ListObject)
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." & Tbl.Cells(i, "A").Value
.From = Cstr(Tbl.Cells(i, "A").Value)
.To = Cstr(Tbl.Cells(i, "A").Value)
.CC = Cstr(Tbl.Cells(i, "A").Value)
.BCC = ""
.TextBody = "Good morning!"
.AddAttachment Cstr(Tbl.Cells(i, "A").Value)
'On Error Resume Next
.Send
End With

'MsgBox ("Mail has been sent")
Set myMail = Nothing

End Sub

Avatar
Sama Ali

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
June 20, 2021
sp_UserOfflineSmall Offline
5
June 22, 2021 - 11:43 pm
sp_Permalink sp_Print sp_EditHistory

Catalin Bombea,

You're A Genius! 

Avatar
Sama Ali

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
June 20, 2021
sp_UserOfflineSmall Offline
6
June 27, 2021 - 6:18 am
sp_Permalink sp_Print sp_EditHistory

Catalin Bombea,

Could you please help me out for the coding for gmail to draft the email but not send it. 
For outlook its .Display but for gmail, been trying many ways, no luck!

would be of great help! Thx

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
June 27, 2021 - 12:54 pm
sp_Permalink sp_Print

Not possible.

That's CDO (not gmail) and it's a library, not an email client like outlook.

Avatar
Sama Ali

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
June 20, 2021
sp_UserOfflineSmall Offline
8
June 28, 2021 - 4:30 pm
sp_Permalink sp_Print

Oh ok. Actually i want to add my default signature before sending emails. Is there any way i can do that?

thanks for all your advice!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
June 28, 2021 - 5:05 pm
sp_Permalink sp_Print

CDO is not outlook, thought I just mentioned that.

Are you sure you have a CDO signature? To help you answer this question, there is no signature in CDO, you might have one in an EMAIL CLIENT (like outlook, thunderbird).

Where is your default signature?

Just format the .HTMLBody in cdo (instead of .TextBody) to build your signature directly in code.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Ben Hughes, Alison West, Francis Drouillard
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6201
Posts: 27185

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.