Excel VBA - Gmail

benjszkie · Feb 15th 2021

Hi,

I need help on my excel VBA project.

This is kinda complicated and I am not sure if I am on the right track. I was able to set up my codes to send email to the list of names. My problem is to change their email addresses everytime I change the name on "payslip" tab?

Code

Sub SendGmail()
'creating a CDO object
Dim Mail As CDO.Message
Set Mail = New CDO.Message
Dim rngemail As Range
'Enable SSL Authentication
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
'Make SMTP authentication Enabled=true (1)
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
'Set the SMTP server and port Details
'Get these details from the Settings Page of your Gmail Account
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"smtp.gmail.com"
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Set your credentials of your Gmail Account
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = _
"[email protected]"
Mail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = _
'Update the configuration fields
Mail.Configuration.Fields.Update
'Creating PDF
Filename = Sheet1.Range("B8").Value & " - " & Range("B8").Value & ".PDF"
'Sheet1.PrintOut from:=1, to:=1
Sheet1.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & Filename
'Set All Email Properties
With Mail
.Subject = "PAYSLIP"
.From = "[email protected]"
.To = "[email protected]"
.CC = ""
.BCC = ""
.textbody = "write your mail here"
.AddAttachments (ThisWorkbook.Path & "\" & Filename) 'To attach Documents in mail
End With
'to send the mail
Mail Send
End Sub
Sub Macro1()
Dim rngMyCell As Range
Application.ScreenUpdating = False
For Each rngMyCell In Sheets("PAYROLL INFO").Range("A2:A29") 'Name list range.
Sheets("PAYSLIP").Range("B8").Value = rngMyCell
Call SendGmail '<= Change to suit the name of the macro that uses the name in cell B8 of PAYSLIP
Next rngMyCell
Application.ScreenUpdating = True
End Sub

Display More

**Carim** · Feb 15th 2021

Hello and Welcome to the Forum

When posting Code ... you should use Code Tags ... to make things clearer ...

Regarding your question ... you seem to say your CDO code is already fully functional ... and you are using GMail SMTP server from Google

Does it mean you are only left with a loop to deal with all the different mail addresses ?

benjszkie · Feb 15th 2021

Thanks for the warm welcome. Yes, seems like CDO code is already working but I was able to test it on one email address only. But everytime I am adding a loop, it gives me error. So I am not pretty confident that CDO code is running smoothly.

**Carim** · Feb 16th 2021

Based on what you have already tested ...the CDO code is already working ...

At this stage, seems to me there is some analysis to carry out in order to properly list all the information which must be adapted to each individual ... such as email address, your text for the mail text body ...and mail attachments ...

Have you already clarified all these arguments ... in some sort of a list or database ...?

**royUK** · Feb 16th 2021

Maybe change this line.

Code

.To = range of cell containing email recipient

Are you using Loop to populate the payslips?

Excel VBA - Gmail

Share

Similar Threads

VBA button that activate cell with HYPERLINK("mailto:

Image placement in an e-mail via VBA

Email Notification while open excel file

Tags