Re: Emailing a Report

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

I added the counter and a MsgBox when it is done; also modified the
SendObject statement so that it is sent automatically instead of letting
you edit it -- that is what the last parameter, false does -- you may
will probably want to remove it until you have things tested unless you
do want to edit each email...

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

.