Emailing a Report

[Gus]

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.

 

[strive4peace]

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]

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]

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]

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]

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]

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

 

[strive4peace]

Hi Gus,

sorry, I've been busy the last couple days -- anyway, I responded to
your previous message <smile> ... beat this message from you by a minute
... but you probably didn't see it (your ears must have been ringing)


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

 

[Gus]

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. ( :

 

[Gus]

Was just reading your email about being busy. It is interesting that we have
bee working together on this project for a while. I think that is cool : ).
I guess you being busy is the only thing that does not surprise me <smile>
because you are so knowledgeable and kind to share and help others.

: )

 

[strive4peace]

Add Align Buttons to Form/Report Design toolbar
---

Hi Gus,

yes, having a nice day, thank you

"It is working."

hip hip hooray!

the best format to use to show the report as you see it in Access is -->
acFormatSNP

"What do you suggest for making sure text on the report is not
overlapping, missing, and or out of order? "

overlapping --> make sure the controls that hold your data do not overlap

missing -- well, maybe you have no data here <smile>

out of order -- specify more rows in the Sorting/Grouping window

~~~

I always add icons to my toolbars to make it easier to format forms and
reports...

* CUSTOMIZING YOUR FORM/REPORT DESIGN TOOLBAR:

when you are in the design view of a form or report, RIGHT-CLICK in a
gray area to the right of your menu bar or an icon bar

choose "Customize" from the shortcut menu

in the dialog box...

choose the *Commands* tab

Category --> *Form/Report Design*

slide these icons to the Form Design toolbar:
(they are located about 3 "screens" down in the Commands list)

Align Right -- must have >1 control selected to be enabled
Align Left -- "
Align Top -- "
Align Bottom -- "
Bring To Front
Send To Back

If you don't know what these icons do, while the customize window is
open, click on the DESCRIPTION command button for each one to see what
is does.

to be efficient, the Align button are a MUST for form and report design

the Bring To Front and Send To Back are nice to have too -- since
everything is "layered" and this gives you control on the order

~~~~~~~

It is also a good idea to open the property sheet
(from the menu --> View, Properties)

on the Format tab, you can set values such as width, height, left, top, ...

~~~~~~~

in order to align, you have to have more than one control selected

to select multiple controls:

1. click and drag an imaginary line (or rectangle) touching every
control you want to select and then let go of the mouse

OR

2. click (and drag if desired) in the vertical ruler to select
everything on that extended horizontal line (or rectangle)

OR

3. click (and drag if desired) in the horizontal ruler to select
everything on that extended vertical line (or rectangle)

OR

4. click the first object and then SHIFT-CLICK objects to toggle their
selection status






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

 

[strive4peace]

thank you, Gus, I like to help others <smile> ... especially when they
appreciate it

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

 

[Gus]

Hi Crystal,

Hope you are having a good day. I made the changes to for my report output
on the email to SNP as you suggested and that works great. I can't believe I
am running out of questions to ask <smile>. This I guess is a good thing ...

I want to express my appreciation for your help and the time you dedicated
to help me with this project.

Thank you,

p.s. hope you would allow me the previledge of taping into your knowledge on
some other occasion. Since, I have your email : )....

 

[strive4peace]

Hi Gus,

yes, it's Friday! how could it be anything but good

you're welcome, Gus happy to help -- and glad to know you have
learned so much

If you post another question and it is something I can help with and no
one else has responded (and I have time), I will look. If you want me
to look, let me know (1) newsgroup (2) subject (3) date, time, and time
zone posted

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