Re: Emailing a Report

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Crystal,

Hope you are doing great today!

Well, what can I say. You got it! It is working.

One last question : ). I notice the report attached when the code runs is
not very well formatted. I don't think it is a problem with the report I have
in the DB because I have it formatted to how I want it. What do you suggest
for making sure text on the report is not overlapping, missing, and or out of
order?

Actually, I was thinking our code in the loop is structured so the report
will show as an "HTML snap shot" on the email. This is correct, right? Why is
it not doing this? I would prefer the report to show as HTML becuase when it
did, it had no formatting errors, but ofcourse this is fine too, if I can
get it to show exactly like the DB formatted report. You have helped me more
than I would have ever imagined and I would not want to be a pest : )

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 _

Oh, one other thing this is the modified code for the new query you
suggested.

Private Sub cmdSendEmail_Click()
Dim strSQL As String
strSQL = "SELECT tblUSers.User_ID, tblUSers.Email, tblUSers.User_FName" _
& " FROM tblUSers " _
& " INNER JOIN tblAgreements" _
& " ON tblUSers.User_ID = tblAgreements.UserID" _
& " WHERE (((tblAgreements.Date_Action_Reminder)=Date()))" _
& " GROUP BY tblUSers.User_ID, tblUSers.Email, tblUSers.User_FName" _
& " ORDER BY tblUSers.User_FName;"
LoopAgmtsSendEmail strSQL
End Sub

: ) Thank you. I hope your day continous to be a great one. ( :



"strive4peace" wrote:

limiting the loop to just one report for each User_ID
---

Hi Gus,

glad you are understanding this better :)

on the report -- when it generates the reports, if a User_ID has 2
reminders, are they now both showing up on the report? If so, we are
ready for the next step ... limiting the loop to just one report for
each User_ID

try something like this:

'~~~~~~~~~~~~
strSQL = "SELECT tblUSers.User_ID " _
& ", tblUSers.Email "
& " FROM tblUSers " _
& " INNER JOIN tblAgreements " _
& " ON tblUSers.User_ID=tblAgreements.UserID " _
& " WHERE YourDateCriteria "
& " GROUP BY tblUSers.User_ID, tblUSers.Email " _
& " ORDER BY tblUSers.UserName;"

'~~~~~~~~~~~~

substitute YourDateCriteria for your criteria clause


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



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
.

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
    ... 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
    ... 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. ... 'pSQL -- defines the recordset to open ... your report is attached" _ ... we need to modify the SQL to get just the criteria that is needed and sort on User_ID ...
    (microsoft.public.access.modulesdaovba)
  • Re: Emailing a Report
    ... "What do you suggest for making sure text on the report is not overlapping, missing, and or out of order? ... I always add icons to my toolbars to make it easier to format forms and reports... ... Align Right -- must have>1 control selected to be enabled ... on the report -- when it generates the reports, if a User_ID has 2 reminders, are they now both showing up on the report? ...
    (microsoft.public.access.modulesdaovba)