Send email using CDO, get email addresses from table

[marvinac1]

Hi Please help me!!!! I have little experience in programming. I have a table called employees that has names and email addresses. What I would like to do is send automated email to all the people in the table using the email addresses. I have the code that will send email but the amount of people who are getting it is too many and may change often. The code below creates a report then send emails using CDO. I just don't understand how to pull the emails from the tables into the code.



X = is used to block out the real information.

Private Sub Email_Report_Click()

 On Error GoTo error

DoCmd.OutputTo acOutputReport, "NAMEOFREPORT", acFormatPDF, "C:\reports\Report1.pdf", 0
MsgBox "Your report has been generated and sent via email", vbOKOnly, "Make Labels"
 
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Export License Track"
objMessage.From = "XXX@XXX.com"      
objMessage.To = "XXX@XXX.com"
objMessage.TextBody = "Report has been created and emailed. Please do not reply back to this email"
objMessage.AddAttachment "C:\reports\Report1.pdf"
 
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "XXX.XXX.com"
'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/con...smtpserverport") = 25
objMessage.Configuration.Fields.Update

objMessage.Send
error: Exit Sub
End Sub

[June7]

Can open a recordset of the email addresses table and loop through the recordset. This is a common topic. Review http://www.access-programmers.co.uk/...d.php?t=180507

[marvinac1]

That worked!!!! For all who is looking at this and wondered what I used. Also for those he programming skills is not that great like me, here is the code below from the link that was provided above.

All you have to do is have a table with email addresses in it. put the name of the table where it say below "tablename goes here." Now your biggest challenge is going to be what field is you email addresses are in. The columns read from left to right with the first column being 0. Normally the first column is the primary key. So for an example if you have in you table as followed:

column 0 is your primary key
column 1 is the person name
column 2 is the email address

then Rs.field = 2
so you will replace below Rs.field(0) with Rs.field(2)

or another example

column 0 is your primary key
column 1 is the email address

then Rs.field = 1
so you will replace below Rs.field(0) with Rs.field(1)

Dim mydb As DAO.Database
Dim rs As DAO.Recordset
Set mydb = CurrentDb()
Set rs = mydb.OpenRecordset("TableName goes here", dbOpenSnapshot)
With rs
.MoveFirst
Do Until rs.EOF
If IsNull(rs.Fields(0)) = False Then
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Todays Testing for " & rs.Fields(1)
objMessage.From = "Project Testing <bwbsl.testing@bwbsl.co.uk>"
objMessage.Sender = "Project Testing <bwbsl.testing@bwbsl.co.uk>"
objMessage.To = rs.Fields(0)
objMessage.TextBody = "What ever you want goes in the body here.  " 
               
 
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "smtp.server.com"

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/con...smtpserverport") = 25
objMessage.Configuration.Fields.Update
 
objMessage.Send
End If
.MoveNext
Loop
End With
rs.Close
Set mydb = Nothing
Set rs = Nothing

[June7]

Can reference recordset fields by name, don't have to know or use the index so field order in recordset won't matter.

objMessage.To = rs!fieldname