Re: Emailing a Report

Hi Gus,

when you land on the Stop line, press F8 to go to the Resume statement, then F8 again to go to the statement that really caused the problem ;)

I see the problem ...

" WHERE tblAgreements.Date_Action_Reminder)=[Enter Date 01/26/2007?]"

anytime you make a reference to a control or something that must be looked up, it needs to be taken outside the literal string and delimited

" Where MyDate = #" & me.Date_controlname & "#"
" Where MyDText = '" & me.Text_controlname & "'"

Anyway, you cannot use a parameter here -- you must specify the criteria in the code


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



Gus wrote:
Hi Crystal,

Thanks. I did as you kindly instructed and the code gives the following error (Too Few parameters.Expceted 1.) Have a great day!

----> Stop: Resume


Private Sub cmdSendEmail_Click()
Dim strSQL As String
strSQL = "SELECT tblUSers.User_FName AS UserName " _
& ", tblAgreements.RACA_Agr_Num " _
& ", tblAgreements.Action_Item " _
& ", tblAgreements.Date_Action_Reminder " _
& ", tblAgreements.Date_Action_Required " _
& ", tblUSers.Team " _
& ", tblUSers.Email " _
& " FROM tblUSers " _
& " INNER JOIN tblAgreements" _
& " ON tblUSers.User_ID = tblAgreements.UserID" _
& " WHERE (((tblAgreements.Date_Action_Reminder)=[Enter Date 01/26/2007?]))"
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

Docmd.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
Resume Exit_proc

End Sub

"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 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 -- 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: 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)
Loading