send an e-mail to contacts in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that gets its data from a query, and their e-mail address is
included. I would like to be able to click a button and send an e-mail to
the people that the report generates. How do I do this? I have zero
experience with VB, so please be very detailed.
 
I have found out that you can't put functioning buttons in a report, but how
about finding a way to generate an email that is automatically addressed to
the list that query comes up with? That would work just as well (preferably
by clicking a button somewhere, wether via the switchboard or whatever).
 
Dim Directors As ADODB.Recordset

Set Directors = New ADODB.Recordset

Directors.Open "tblofyournamesandemailaddresses", CurrentProject.Connection,
adOpenStatic

Do Until Directors.EOF

[Forms]![frm_EMail].[txtDirector].Value = Directors![Name]
[Forms]![frm_EMail].[txtEmail].Value = Directors![E_Mail]

On Error Resume Next
DoCmd.SendObject acSendReport, "nameofyourreport", acFormatSNP,
[Forms]![frm_EMail].[txtEmail], , , "subjectofemail", , True

On Error Resume Next
Directors.MoveNext
Loop

This code placed in the onclick event will generate a report for each
individual and attach it to an email to the individual.

You need a table with the names and email addresses of all the people your
are including.

Directors is just a name but it refers to the table you have with all your
names.

the report must have a record source of a query with the criteria pointing
to textboxes on the form you are using to generate the reports and emails.
 
Jeff C, I'm trying to do exactly what you wrote here, although I too am
unfamiliar with VB. On the form, do I include text boxes of the fields in
Directors? My query will reference this as a text box and not a field?

Jeff C said:
Dim Directors As ADODB.Recordset

Set Directors = New ADODB.Recordset

Directors.Open "tblofyournamesandemailaddresses", CurrentProject.Connection,
adOpenStatic

Do Until Directors.EOF

[Forms]![frm_EMail].[txtDirector].Value = Directors![Name]
[Forms]![frm_EMail].[txtEmail].Value = Directors![E_Mail]

On Error Resume Next
DoCmd.SendObject acSendReport, "nameofyourreport", acFormatSNP,
[Forms]![frm_EMail].[txtEmail], , , "subjectofemail", , True

On Error Resume Next
Directors.MoveNext
Loop

This code placed in the onclick event will generate a report for each
individual and attach it to an email to the individual.

You need a table with the names and email addresses of all the people your
are including.

Directors is just a name but it refers to the table you have with all your
names.

the report must have a record source of a query with the criteria pointing
to textboxes on the form you are using to generate the reports and emails.
--
Jeff C
Live Well .. Be Happy In All You Do


Gntlhnds said:
I have found out that you can't put functioning buttons in a report, but how
about finding a way to generate an email that is automatically addressed to
the list that query comes up with? That would work just as well (preferably
by clicking a button somewhere, wether via the switchboard or whatever).
Click to expand...
Click to expand...
 
Jeff, Sorry to trouble you again. The code is telling me that it doesn't
recognize my field name for this line:
[Forms]![frm_EMail].[txtDirector].Value = Directors![Name]

Perhaps, it's not setting Directors to look at my table? Should the name be
in quotes?
I have exactly:
Private Sub Command4_Click()
Dim Directors As ADODB.Recordset

Set Directors = New ADODB.Recordset
Directors.Open "hrem_admin_tr", CurrentProject.Connection, adOpenStatic

Do Until Directors.EOF

[Forms]![frm_EMail].[txtDirector].Value = Directors![last_name]
[Forms]![frm_EMail].[txtEmail].Value = Directors![andrew_id]

On Error Resume Next
DoCmd.SendObject acSendReport, "Report for Division", acFormatSNP,
[Forms]![frm_EMail].[txtEmail], , , [last_name] & " HRIS June_07", , True

On Error Resume Next
Directors.MoveNext
Loop

End Sub

Jeff C said:
Dim Directors As ADODB.Recordset

Set Directors = New ADODB.Recordset

Directors.Open "tblofyournamesandemailaddresses", CurrentProject.Connection,
adOpenStatic

Do Until Directors.EOF

[Forms]![frm_EMail].[txtDirector].Value = Directors![Name]
[Forms]![frm_EMail].[txtEmail].Value = Directors![E_Mail]

On Error Resume Next
DoCmd.SendObject acSendReport, "nameofyourreport", acFormatSNP,
[Forms]![frm_EMail].[txtEmail], , , "subjectofemail", , True

On Error Resume Next
Directors.MoveNext
Loop

This code placed in the onclick event will generate a report for each
individual and attach it to an email to the individual.

You need a table with the names and email addresses of all the people your
are including.

Directors is just a name but it refers to the table you have with all your
names.

the report must have a record source of a query with the criteria pointing
to textboxes on the form you are using to generate the reports and emails.
--
Jeff C
Live Well .. Be Happy In All You Do


Gntlhnds said:
I have found out that you can't put functioning buttons in a report, but how
about finding a way to generate an email that is automatically addressed to
the list that query comes up with? That would work just as well (preferably
by clicking a button somewhere, wether via the switchboard or whatever).
Click to expand...
Click to expand...
 
Back
Top