Re: Emailing a Report

Hi Gus,


** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

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

the debug window, also called the immediate window, is another good resource. When you are executing code, you can query the value of any variable, field, control, ...

? pSQL
and then press ENTER
-- or whatever is your variable name or control name or what you want to know...

You can also use the debug window to get help on a topic -- type or paste a keyword into the window and press F1

'~~~~

Stop: Resume

When the execution comes to STOP, it stops and goes into code

Press F8 to go to the next statement (F5 to cintinue running automatically)

RESUME causes execution to go back to the offending line so you can fix it ... are all your line continuation codes in there?

I see I forgot one in the example I posted for you ...
--> & ", tblUSers.Email "
should have space, underscore at the end of the line

~~~

once the routine is debugged and working, comment out the Stop:Resume line so the user will resume with the exit code if they hit an error

I also made another mistake ... sorry, <blush>

GoTo Exit_proc --> Resume Exit_proc


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



Gus wrote:
Hi Crystal,

Looks like we are close. The code seems to be almost ready. I get this error now (Syntax error(missing operator) in query expression 'INNER JOIN tblAgreements ON tblUsers.USer_ID=tblAgreements.UserID'.) It points to ...

---> Stop: Resume

It also points to this part when I hit F8.

----> Set r = CurrentDb.OpenRecordset(pSQL, dbOpenSnapshot)

----> MsgBox Err.Description _
, , "ERROR " & Err.Number _
& " LoopAgmtsSendEmail"

Have a great evening!




"strive4peace" wrote:

Hi Gus

you're welcome

~~~ DAO Library Reference ~~~

make sure you have a reference to a Microsoft DAO Library

Tools, References... from a module window

scroll to Microsoft DAO 3.6 Object Library and check it


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



Gus wrote:
Cool! Thanks!

The (strSQL ="SELECT [User_FName] AS UserName" _ ) seems to be working after I made a minor change.

Now I get an error (User-deifned type not defined). The Sub LoopAgmtsSendEmail is highlighted by the Debugger. Also it points to the (Dim r As DAO.Recordset) and (Dim i As Integer).

----> 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

Crystal, Thank you so much for being patient with me and guiding me with this.


Hi gus,

you're welcome

You must remember to enclose literal values in quotes and concatenate them; the final string must have commas too, so commas will be literals.


"SELECT [User_FName] & " " & [User_LName] AS UserName,
-->
"SELECT " & [User_LName] & " AS UserName,"

.... but this doesn't really make sense -- the only reason to take [User_LName] out of the string is if it is a variable or control or something that has a set value at the time the SQL statement is being constructed...

~~~

just a guess, but try something like this:

'~~~~~~~~~~~~
strSQL = "SELECT [User_FName] AS UserName " _
& ", tblUSers.Team " _
& ", tblAgreements.RACA_Agr_Num " _
& ", tblAgreements.Action_Item " _
& ", tblAgreements.Date_Action_Reminder " _
& ", tblAgreements.Date_Action_Required " _
& ", tblUSers.Email "
& " FROM tblUSers " _
& " WHERE INNER JOIN tblAgreements " _
& " ON tblUSers.User_ID=tblAgreements.UserID " _
& " ORDER BY UserName;"

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

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



Gus wrote:
Hi Crystal,

Hope your day is going better then mine. I am still not having any luck with the code. Here is what I have. You now know my tables name and the fields I am working with so, you will have a much better picture of what I am trying to do.

Thanks once again. I do appreciate your time very much.


Private Sub cmdSendEmail_Click()
Dim strSQL As String
strSQL = "SELECT [User_FName] & " " & [User_LName] AS UserName, tblUSers.Team, tblAgreements.RACA_Agr_Num, tblAgreements.Action_Item, tblAgreements.Date_Action_Reminder, tblAgreements.Date_Action_Required, tblUSers.Email "
& " FROM tblUSers " _
& " WHERE INNER JOIN tblAgreements ON tblUSers.User_ID=tblAgreements.UserID "
& " ORDER BY UserName;"
LoopAgmtsSendEmail strSQL
End Sub

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

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


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
GoTo Exit_proc

End Sub



"strive4peace" wrote:

Hi Gus,

"I began to read your access basics for programming, great insight. I can see your passion for databases in the way in which wrote this guide."

thank you, that is very kind of you to say; I am glad you are getting some useful information ;)

the problem is that you have

Private Sub Command18_Click()

and no End Sub ...

the Sub/Function declaration and the End Sub/Function are like parentheses -- they must be balanced.

You also have to send the SQL string to the LoopThroughtblAgreementsAndSendObject routine (which I renamed to be shorter) ...

so, you should have something like this:

Private Sub Command18_Click()
'statements
End Sub

Sub LoopThroughtblAgreementsAndSendObject( _
pSQL As String)
'statements
End Sub

'~~~~~~~~~~~
.... BUT! Before you write code for a command button, you really should give it a meaningful name; Command18 is not descriptive.

perhaps change the Name property of the button to something like this
Command18 --> cmdSendEmail

then, you would have something like this for your code:

'~~~~~~~~~~~
Private Sub cmdSendEmail_Click()
dim strSQL as string
strSQL = "SELECT email " _
& " FROM [YourTablename] " _
& " WHERE conditions " _
& " ORDER BY somefieldname;"
LoopAgmtsSendEmail strSQL
End Sub
'~~~~~~~~~~~

You will probably collect criteria on your form for the 'conditions' part of the SQL statement in cmdSendEmail. Obviously, you will have to rename things as I do not know your field/tablenames except for 'email' ;)

LoopAgmtsSendEmail could go into a general module or, if you are only going to use it in one place, you can put it behind the form with Private Sub cmdSendEmail_Click

'~~~~~~~~~~~
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

SendObject _
.