Re: Emailing a Report

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

keep compiling until nothing happens (this is good!)
'~~~~~~~~~~~~~~~~~~~~~~~~~

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



Gus wrote:
Hi Crystal,

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.

Now that I made the changes to the SendObject that string is working.

My next question and problem is regarding the Private Sub Command Click( ) when I try to run the code it gives me this (Compile Error Expected End Sub). Are there any other errors you see in my code so far?

Thank you very much. Have an a great day!

Private Sub Command18_Click()
Sub LoopAgmtsSendEmail( _
pSQL As String)

Dim r As DAO.Recordset

'Set up error handler
On Error GoTo Err_proc

'PARAMETERS
pSQL "Daily Reminders All Teams Report"
Debug.Print strSQL

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

r.MoveFirst

'loop through the Recordset until the end

Do While Not r.EOF

SendObject _
acReport, _
"Daily Reminders All Teams", _
acFormatHTML, _
r!email, _
, _
, _
"Your Reminder", _
"Good Morning"

r.MoveNext
Loop

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 & " "
LoopThroughTableAndSendObject ""

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

sorry I did not explain the arguments in brackets ... they are optional. Since you are not specifying anything after the messagetext... change the statement to -->

SendObject _
acReport, _
"Daily Reminders All Teams", _
acFormatHTML, _
r!email, _
, _
, _
"Your Reminder", _
"Good Morning"
'~~~~~~~~~~~~

'[objecttype] --> acReport
'[, objectname] --> "Daily Reminders All Teams"
'[, outputformat] --> acFormatHTML
'[, to] --> r!email
'[, cc]
'[, bcc]
'[, subject] --> "Your Reminder"
'[, messagetext] --> "Good Morning"


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



Gus wrote:
Hi Crystal,

This is what I have so far and as you will problably be able to tell it is not working. I try to test it but, it gives me a compile error expected end sub. I tried to modify the code for the email string (SendObject ) and if I take out , [templatefile] it tells me it is wrong (turns red). I will appreciate your helping suggestions.

P.S. I am sure I am not combining the Command_Click correctly.



Private Sub Command18_Click()

Sub LoopAgmtsSendEmail( _
pSQL As String)

Dim r As DAO.Recordset

'Set up error handler
On Error GoTo Err_proc
.

Loading