Re: Emailing a Report -- SendObject parameters

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 LoopThroughtblAgreementsAndSendObject( _
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 LoopThroughtblAgreementsAndSendObject( _
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", _
, _
, [templatefile]


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,

I did have strSQL in the program but changed it to a parameter and see I
didn't change strSQL --> pSQL in all the instances ...

obviously, also this
Dim r As DAO.Recordset, strSQL as string
needs to be come just this -->
Dim r As DAO.Recordset

as pSQL is defined as a string in the function declaration...

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



strive4peace wrote:
Hi Gus,

here is some basic code you can modify ... if you have trouble, we can
help you out more.

If you are confused about creating the SQL statement to get the data
that you want, send me an email and request my 30-page Word document on
Access Basics (for Programming) -- it doesn't cover VBA, but prepares
you for it because it covers essentials in Access.

Be sure to put "Access Basics" in the subject line so that I see your
message...

'~~~~~~~~~~~~~~~~~~~
Sub LoopThroughTableAndSendObject( _
pSQL as string )

'Crystal
'strive4peace2007 at yahoo dot com

'NEED reference to
'Microsoft DAO Library

'dimension variables
Dim r As DAO.Recordset, strSQL as string

'Set up error handler
On Error GoTo Err_proc

'PARAMETERS
'pSQL -- defines the recordset to open
' -- this can be a tablename, queryname, or SQL statement..."

'comment or take this line out after procedure is tested
debug.print strSQL

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

'move to the first record
r.MoveFirst

'loop through the Recordset until the end

Do While Not r.EOF

SendObject _
objecttype _
, objectname _
, outputformat _
, r!emailAddressFieldname
, [cc] _
, [bcc] _
, [subject] _
, [messagetext] _
, [editmessage] _
, [templatefile]

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


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



Gus wrote:
Thanks for your help Crystal.

My database serves as a reminder system. It stores user inputted
nformation about action he or she needs to take on a contract(s) on or
by a certain date.
I have a query that looks up all the reminders based on date. For
example 01/23/2007, a report will be produced with a list of all users
who have an item to be reminded of on the mentioned 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?
This is the code I am currently using. With this code I can only get
one address in the address line of the message, also if the report has
no data I get a Run-time error '2498'. I am not yet a very savy VB
user, so please, if you can provide as much detail as possible.
Have a great day!

Private Sub Command18_Click()
DoCmd.SendObject _
acSendReport, _
"Daily Reminders All Teams Report", _
acFormatHTML, _
[email], _
, _
, _
"Your Reminder", _
"Good Morning!", _
True
"strive4peace" wrote:

Hi Gus

here are the parameters for SendObject

'========================================= Email
'SendObject
'[objecttype]
'[, objectname]
'[, outputformat]
'[, to]
'[, cc]
'[, bcc]
'[, subject]
'[, messagetext]
'[, editmessage]
'[, templatefile]

What is your question?


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

Relevant Pages

  • 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 -- balance Sub/End Sub, compile
    ... "I began to read your access basics for programming, ... Private Sub Command18_Click ... 'pSQL -- defines the recordset to open ... pSQL "Daily Reminders All Teams Report" ...
    (microsoft.public.access.modulesdaovba)
  • 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 -- SendObject parameters
    ... I try to test it but, it gives me a compile error expected end sub. ... pSQL "Daily Reminders All Teams Report" ... 'loop through the Recordset until the end ... 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
    ... 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)