Re: Emailing a Report -- SendObject parameters

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
strive4peace2006 at yahoo.com
*



Gus wrote:
Hi, Thanks for your help.

I have a DB that stores user reminders. A report has been created to get users who need to be reminded of something they must do on a spcific date.
I am trying to formulate code to be able to send an email with the report for the users that appear on the report. I am using Lotus Notes.
This is the code that I am currently using. Private Sub Command18_Click()
DoCmd.SendObject _
acSendReport, _
"Daily Reminders All Teams Report", _
acFormatRTF, _
"UserEmail", _
, _
, _
"Your Reminder", _
"Have a good day!", _
False

End Sub
.

Relevant Pages

  • Re: Emailing a Report -- SendObject parameters
    ... I began to read your access basics for programming, ... My next question and problem is regarding the Private Sub Command Click ... pSQL "Daily Reminders All Teams Report" ... 'loop through the Recordset until the end ...
    (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)
  • Re: Emailing a Report
    ... Sub LoopAgmtsSendEmail(_ ... 'pSQL -- defines the recordset to open ... your report is attached" _ ... I have a query that looks up all the reminders based on date. ...
    (microsoft.public.access.modulesdaovba)
  • 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: Get report to print data from ADO recordset created at runtime
    ... I plan to create a recordset,> assign ... > to a report and use SendTo to email the report as an .rtf file. ... > On Error GoTo cboFindObject_AfterUpdate_Err ... > Private Sub First_Name_Enter ...
    (microsoft.public.access.modulesdaovba)