Re: Emailing a Report

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi Crystal,

Hope you are doing well. Just wanted to see what your input is for having
the loop code to be able to only generate one email even if the user has
multiple reminders.

Thanks,

Option Compare Database

Sub LoopAgmtsSendEmail( _
pSQL As String)

'Crystal
'strive4peace2007 at yahoo dot com

'NEED reference to
'Microsoft DAO Library

'PARAMETERS
'pSQL -- defines the recordset to open
' -- this can be a tablename, queryname, or SQL statement..."

'dimension variables
Dim r As DAO.Recordset
Dim i As Integer

'Set up error handler
On Error GoTo Err_proc

'comment or take this line out after procedure is tested
Debug.Print pSQL

'open the Recordset
Set r = CurrentDb.OpenRecordset(pSQL, dbOpenSnapshot)

'loop through the Recordset until the end

i = 0

Do While Not r.EOF

Docmd.SendObject _
acReport _
, "Daily Reminders All Teams Report" _
, acFormatHTML _
, r!email _
, _
, _
, "Your Reminder for " & Format(Date, "ddd m-d-yy"), "Good
Morning, your report is attached" _
, True _


i = i + 1

r.MoveNext
Loop

MsgBox i & " emails were sent", , "Done"

Exit_proc:
On Error Resume Next

'close the recordset
r.Close

'release object variables
Set r = Nothing

Exit Sub

Err_proc:
MsgBox Err.Description _
, , "ERROR " & Err.Number _
& " LoopAgmtsSendEmail"

'press F8 to step through code and see where problem is
'comment next line after routine is debugged
Stop: Resume
Resume Exit_proc

End Sub






"Gus" wrote:

Hi Crystal,

Hope you had a great weekend. Yeah, this is true "the lightbulb will come on
for you too", I am beggining to understand in greater detail.

Okay, I made all the updates to the code as you suggested. It is working, as
far generating multiple emails with a report for each reminder the user has.

How do the reminders work? --> User input their reminders in the New
Reminders Log Form. As of now someone assigned prints out reminders that fit
the =Date() criteria and hand it off to the user.

If someone gets 2 reminders, how do they
know what they are for? --->They use the Agr_Num, Action_Item and Notes
fields to determine what agreement the reminder is for and what they needed
to do from the Action_Item and the Notes. Hope I made some sense here : )

Thanks. Hope you day is going great!

"strive4peace" wrote:

Add Grouping to report
---

Hi Gus,

"You are right on, again!"

Thank you -- its just logic ... the lightbulb will come on for you too,
give it time -- from a different perspective :)

The report will need to be grouped by User_ID -- above whatever else is
there, if anything

make sure User_ID is in the report recordset

Click Sorting & Grouping from the report design (from the menu: View,
Sorting & Grouping)

If there is something there already, make a blank row on top

1. click the row-selector box on the first row of the Sorting & Grouping
window
2. press the INSERT key on your keyboard

On the new blank line, Choose User_ID as the grouping field in the
Field/Expression column
Group Header --> Yes (if desired) -- so you can put User_ID, name, etc here

Now, we need to modify the SQL to get just the criteria that is needed
and sort on User_ID

something like this:
strSQL = "SELECT tblUSers.User_ID" _
& ", tblUSers.Email " _
& " FROM tblUSers " _
& " INNER JOIN tblAgreements" _
& " ON tblUSers.User_ID = tblAgreements.UserID" _
& " WHERE YourCriteria " _
& " ORDER BY UserID;"

Since the report recordset has the rest of the fields, you don't need
them here -- but your criteria for limiting the report should go here.

Let just do these steps next ... you will get multiple reports for each
user with more than one reminder since we haven't modified the loop code
yet, but lets get this working first -- then we will do that <smile>

How do the reminders work? If someone gets 2 reminders, how do they
know what they are for?


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gus wrote:
Hi Crystal,

You are right on, again! I went back to the query and made a minor change
and it no longer pops up with a parameter. Wow, That is cool! I know it is
problably not a big deal, but for me it is very exciting.....

Okay, now when there are no records that fit my criteria (Date=now())), it
says "0 emails were sent". That is cool!

Okay, looks like we are in business! Everything is working properly.

I think this is next ---->then we will modify the report and the SQL to loop
to
put all reminders together.

What do you suggest?

I am so impressed that you are able to guide me through what I want to do,
even when I may not do the best at explaining things properly. You are good.
Thank you.

Have a good weekend.


"strive4peace" wrote:

check Report Recordsource to get rid of parameter popup
---

Hi Gus,

"Hurray, the code is working."

Great news, Gus!

"parameter window pops up asking for RACA_Agr_Num"

check the RecordSource for your report, "Daily Reminders All Teams
Report", it must still be pointing to RACA_Agr_Num in the query it is
based on instead of Agr_Num, wehich is what your field is called

"As to the parameter box, if I hit Cancel"

we need to get the parameter popup to stop ...

"How could I have the code
> format just one email even if I have multiple reminders."

let's take one step at a time -- lets first get it to work with no
parameter popup, then we will modify the report and the SQL to loop to
put all reminders together

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gus wrote:
Hurray, the code is working. Well, for the most part, but it is really close,
I can feel it. I no longer get any error messages, but when the code runs a
parameter window pops up asking for RACA_Agr_Num, If I hit Okay the code runs
and formats an email.

If there are more than one Reminders for a person it continues to bring up
the parameter box and format an email with the report for each reminder for
that person.

For example I currently have two reminders, and when I ranned the code it
formatted two emails for with the same report. How could I have the code
format just one email even if I have multiple reminders.

As to the parameter box, if I hit Cancel (ERROR 25010 LoopAgmtsSendEmail)
message pops up and it read (The SendObject action was canceled).

Then it points to this.
---> Stop: Resume
Resume Exit_proc

Private Sub cmdSendEmail_Click()
Dim strSQL As String
strSQL = "SELECT tblUSers.User_FName AS UserName " _
& ", tblAgreements.Agr_Num " _
& ", tblAgreements.Action_Item " _
& ", tblAgreements.Date_Action_Reminder " _
& ", tblAgreements.Date_Action_Required " _
& ", tblUSers.Team " _
& ", tblUSers.Email " _
& " FROM tblUSers " _
& " INNER JOIN tblAgreements" _
& " ON tblUSers.User_ID = tblAgreements.UserID" _
& " WHERE (((tblAgreements.Date_Action_Reminder)=Date()))" _

LoopAgmtsSendEmail strSQL
End Sub

Option Compare Database

Sub LoopAgmtsSendEmail( _
pSQL As String)

'Crystal
'strive4peace2007 at yahoo dot com

'NEED reference to
'Microsoft DAO Library

'PARAMETERS
'pSQL -- defines the recordset to open
' -- this can be a tablename, queryname, or SQL statement..."

'dimension variables
Dim r As DAO.Recordset
Dim i As Integer

'Set up error handler
On Error GoTo Err_proc

'comment or take this line out after procedure is tested
Debug.Print pSQL

'open the Recordset
Set r = CurrentDb.OpenRecordset(pSQL, dbOpenSnapshot)

'loop through the Recordset until the end

i = 0

Do While Not r.EOF

Docmd.SendObject _
acReport _
, "Daily Reminders All Teams Report" _
, acFormatHTML _
, r!email _
, _
, _
, "Your Reminder for " & Format(Date, "ddd m-d-yy"), "Good
Morning, your report is attached" _
, True _


i = i + 1

r.MoveNext
Loop

MsgBox i & " emails were sent", , "Done"

Exit_proc:
On Error Resume Next

'close the recordset
r.Close

'release object variables
Set r = Nothing

Exit Sub

Err_proc:
MsgBox Err.Description _
, , "ERROR " & Err.Number _
& " LoopAgmtsSendEmail"

'press F8 to step through code and see where problem is
'comment next line after routine is debugged
Stop: Resume
Resume Exit_proc

End Sub


Thanks Crystal. I am excited to see I have learned somethings with your help.



"Gus" wrote:

Hi Crystal,

My criteria would be Date_Action_Reminder, I think you mentioned it below,
but would 'Date_Action_Reminder >= (Date()-1) pull the data for the day that
I run the code?

To be more specific the code would run daily and it would pull all reminders
that have a Date_Action_Reminder for that day.

Thanks. Hope that I do not confuse you too much with my way of explaining
that.

Have a nice day!


"strive4peace" wrote:

Hi Gus,

"I want the Code to pull the information based on a date criteria…"

what is your criteria?

more comments in-line...


Warm Regards,
Crystal
*
(: have an awesome day :)
.

Relevant Pages

  • Re: Emailing a Report
    ... Private Sub Command18_Click ... pSQL "Daily Reminders All Teams Report" ... 'loop through the Recordset until the end ...
    (microsoft.public.access.modulesdaovba)
  • Re: Emailing a Report
    ... limiting the loop to just one report for each User_ID ... on the report -- when it generates the reports, if a User_ID has 2 reminders, are they now both showing up on the report? ... Since the report recordset has the rest of the fields, you don't need them here -- but your criteria for limiting the report should go here. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Emailing a Report
    ... Sub LoopThroughTableAndSendObject(_ ... 'pSQL -- defines the recordset to open ... I have a query that looks up all the reminders based on date. ... My question is how can I create the code for sending a message to all the users that appear on the report without having to type in there email address in the address line of the message? ...
    (microsoft.public.access.modulesdaovba)
  • Re: Emailing a Report
    ... Sub LoopAgmtsSendEmail(_ ... 'pSQL -- defines the recordset to open ... I have a query that looks up all the reminders based on date. ... a report will be produced with a list of all users who have an item to be reminded of on the mentioned date. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Emailing a Report
    ... I don't think it is a problem with the report I have ... reminders, are they now both showing up on the report? ... Since the report recordset has the rest of the fields, ... > format just one email even if I have multiple reminders." ...
    (microsoft.public.access.modulesdaovba)