Send HTML report to each Email Address in my Query

[cw]

I use the following excellent code below from Alex Dybenko to send my report
of all Past Due Tickets via Outlook Body in HTML format to a single
Management email address.

(Here is a summary that works perfectly to send the whole report to one
address)
1) Macro: sendPastDueTickets_Recipient (calls the Module, runs nightly by
Windows Scheduler)
2) Module: modEmailOL_recipient (see code below)
3) Report: rptTicketsPast_Due_Recipient (uses query in #4)
4) Query: qryTicketsPastDue_Recipient (lists each ticket, including the
recipient email address)

Question: How can I alter it to send individual emails to each email address,
for just their tickets only?
- Some "Looping" code that pulls the "Email Address" from the query?
- Then filters the query by that "Email Address", creating the custom report
(s)?

I already have the automated emailing procedure down pat, just need help
getting the report filtered, and the code below to iterate down the list of
email addresses, creating the custom reports.

Thanks,
cw

'-------------------------------------------------------------------
Function exporthtml ()

Dim strline, strHTML
Dim OL As Outlook.Application
Dim MyItem As Outlook.MailItem
Set OL = New Outlook.Application
Set MyItem = Outlook.Application.CreateItem(olMailItem)

Dim strFileName As String
strFileName = Environ("Temp") & "\rep_temp.txt"
If Len(Dir(strFileName)) > 0 Then
Kill strFileName
End If
DoCmd.OutputTo acOutputReport, "rptTicketsPast_Due", acFormatHTML,
strFileName

Open strFileName For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1
' If OL2002 set the BodyFormat
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If
MyItem.HTMLBody = strHTML
MyItem.To = "(e-mail address removed)"
MyItem.Subject = "Past Due Tickets.."
MyItem.Send

Exit_btnPastDue_Click:
On Error Resume Next
Set MyItem = Nothing
Set OL = Nothing

End Function

 

[Tony Toews [MVP]]

- Some "Looping" code that pulls the "Email Address" from the query?

See the Sample Code illustrating looping through a DAO recordset page
at the Access Email FAQ at
http://www.granite.ab.ca/access/email/recordsetloop.htm

- Then filters the query by that "Email Address", creating the custom report
(s)?

For a page on how to print a report for a single record and how to
generate reports to attach to emails see the Emailing reports as
attachments from Microsoft Access page at
http://www.granite.ab.ca/access/email/reportsasattachments.htm

You could also send the report as a PDF file if desired.
Creating PDF files from within Microsoft Access
http://www.granite.ab.ca/access/pdffiles.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[cw via AccessMonster.com]

Tony, Thanks for the sample Code illustrating looping through a DAO recordset.

I'd be glad to use that technique, if someone could guide me on how to
implement it with my existing code from Alex. Since I have Alex's code
working well already, and it sends the report via the Email Body, I would
prefer to build on that..

The section in your code on filtering the query by that "Email Address", to
create the custom report is also very applicable.
Here is a sample listing of my query which feeds my Report:
--------------------------------------------------------------
email ticketNum status
(e-mail address removed) 14323 In Progress
(e-mail address removed) 14324 In Progress
(e-mail address removed) 14325 In Progress
(e-mail address removed) 14326 In Progress
--------------------------------------------------------------
When my nightly Macro runs, it opens the Module named: modEmailOL, which runs
Alex's code and sends the full report listing (see above) to my hard-coded
email address.

What I would like is to add your code to my Module, so that it sends out 3
additional reports:
- user1 gets an email showing data for his two tickets
- user2 gets an email showing data for his one ticket
- user3 gets an email showing data for his one ticket

I can create separate Macros/Modules/Queries/Reports for each user and
schedule them all. (This will work, but not much of a coding challenge..?)

Thanks again,
cw

 

[cw via AccessMonster.com]

I have decided to just create separate automated reports for each user, and
then run each one individually each evening. This works to create a custom
report for each email/user.

The only thing left to do is cancel the job (trap error 2501) if the
Report/Query is blank?
Since my DoCmd.OutputTo is run by my Module, via a nightly Macro, where does
the Error code go in the below code? or should it be in the Query? or the
Report?

Module Code:
-------------------
Function exporthtml ()

Dim strline, strHTML
Dim OL As Outlook.Application
Dim MyItem As Outlook.MailItem
Set OL = New Outlook.Application
Set MyItem = Outlook.Application.CreateItem(olMailItem)

Dim strFileName As String
strFileName = Environ("Temp") & "\rep_temp.txt"
If Len(Dir(strFileName)) > 0 Then
Kill strFileName
End If
DoCmd.OutputTo acOutputReport, "rptTicketsPast_Due", acFormatHTML,
strFileName

Open strFileName For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1
' If OL2002 set the BodyFormat
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If
MyItem.HTMLBody = strHTML
MyItem.To = "(e-mail address removed)"
MyItem.Subject = "Past Due Tickets.."
MyItem.Send

Exit_btnPastDue_Click:
On Error Resume Next
Set MyItem = Nothing
Set OL = Nothing

End Function
-----------------------

Tony, Thanks for the sample Code illustrating looping through a DAO recordset.

I'd be glad to use that technique, if someone could guide me on how to
implement it with my existing code from Alex. Since I have Alex's code
working well already, and it sends the report via the Email Body, I would
prefer to build on that..

The section in your code on filtering the query by that "Email Address", to
create the custom report is also very applicable.
Here is a sample listing of my query which feeds my Report:
--------------------------------------------------------------
email ticketNum status
(e-mail address removed) 14323 In Progress
(e-mail address removed) 14324 In Progress
(e-mail address removed) 14325 In Progress
(e-mail address removed) 14326 In Progress
--------------------------------------------------------------
When my nightly Macro runs, it opens the Module named: modEmailOL, which runs
Alex's code and sends the full report listing (see above) to my hard-coded
email address.

What I would like is to add your code to my Module, so that it sends out 3
additional reports:
- user1 gets an email showing data for his two tickets
- user2 gets an email showing data for his one ticket
- user3 gets an email showing data for his one ticket

I can create separate Macros/Modules/Queries/Reports for each user and
schedule them all. (This will work, but not much of a coding challenge..?)

Thanks again,
cw
[quoted text clipped - 15 lines]

Tony