Emailing a Report

G

Gus

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
 
S

strive4peace

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
*
 
S

strive4peace

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
*


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, _
, _
, _
, _
"Your Reminder", _
"Good Morning!", _
True
[QUOTE="strive4peace"]
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
*


[QUOTE]
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[/QUOTE][/QUOTE][/QUOTE]
 
S

strive4peace

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
*


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
*


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, _
, _
, _
, _
"Your Reminder", _
"Good Morning!", _
True
[QUOTE="strive4peace"]
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[/QUOTE][/QUOTE][/QUOTE]
 
S

strive4peace

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
*


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 said:
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
*


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, _
, _
, _
, _
"Your Reminder", _
"Good Morning!", _
True
:

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[/QUOTE][/QUOTE][/QUOTE]
 
S

strive4peace

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
*


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 said:
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
*


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

'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




:

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, _
, _
, _
, _
"Your Reminder", _
"Good Morning!", _
True
:

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[/QUOTE][/QUOTE][/QUOTE]
 
S

strive4peace

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
*


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 said:
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
*


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

:

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
 
G

Gus

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
*


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 said:
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
 
S

strive4peace

Hi Gus

you're welcome

~~~ DAO Library Reference ~~~

make sure you have a reference to a Microsoft DAO Library

Tools, References... from a module window

scroll to Microsoft DAO 3.6 Object Library and check it


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


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
*


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



:

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
 
G

Gus

Hi Crystal,

Looks like we are close. The code seems to be almost ready. I get this error
now (Syntax error(missing operator) in query expression 'INNER JOIN
tblAgreements ON tblUsers.USer_ID=tblAgreements.UserID'.) It points to ...

---> Stop: Resume

It also points to this part when I hit F8.

----> Set r = CurrentDb.OpenRecordset(pSQL, dbOpenSnapshot)

----> MsgBox Err.Description _
, , "ERROR " & Err.Number _
& " LoopAgmtsSendEmail"

Have a great evening!




strive4peace said:
Hi Gus

you're welcome

~~~ DAO Library Reference ~~~

make sure you have a reference to a Microsoft DAO Library

Tools, References... from a module window

scroll to Microsoft DAO 3.6 Object Library and check it


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


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



:

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 _
 
S

strive4peace

Hi Gus,


** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

'~~~~~~~~~~~~~~

the debug window, also called the immediate window, is another good
resource. When you are executing code, you can query the value of any
variable, field, control, ...

? pSQL
and then press ENTER
-- or whatever is your variable name or control name or what you want to
know...

You can also use the debug window to get help on a topic -- type or
paste a keyword into the window and press F1

'~~~~

Stop: Resume

When the execution comes to STOP, it stops and goes into code

Press F8 to go to the next statement (F5 to cintinue running automatically)

RESUME causes execution to go back to the offending line so you can fix
it ... are all your line continuation codes in there?

I see I forgot one in the example I posted for you ...
--> & ", tblUSers.Email "
should have space, underscore at the end of the line

~~~

once the routine is debugged and working, comment out the Stop:Resume
line so the user will resume with the exit code if they hit an error

I also made another mistake ... sorry, <blush>

GoTo Exit_proc --> Resume Exit_proc


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


Hi Crystal,

Looks like we are close. The code seems to be almost ready. I get this error
now (Syntax error(missing operator) in query expression 'INNER JOIN
tblAgreements ON tblUsers.USer_ID=tblAgreements.UserID'.) It points to ...

---> Stop: Resume

It also points to this part when I hit F8.

----> Set r = CurrentDb.OpenRecordset(pSQL, dbOpenSnapshot)

----> MsgBox Err.Description _
, , "ERROR " & Err.Number _
& " LoopAgmtsSendEmail"

Have a great evening!




strive4peace said:
Hi Gus

you're welcome

~~~ DAO Library Reference ~~~

make sure you have a reference to a Microsoft DAO Library

Tools, References... from a module window

scroll to Microsoft DAO 3.6 Object Library and check it


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


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



:

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 _
 
G

Gus

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 said:
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
*


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, _
, _
, _
, _
"Your Reminder", _
"Good Morning!", _
True
[QUOTE="strive4peace"]
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[/QUOTE][/QUOTE]
[/QUOTE]
 
S

strive4peace

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
*


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 said:
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
*


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, _
, _
, _
, _
"Your Reminder", _
"Good Morning!", _
True
:

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[/QUOTE][/QUOTE][/QUOTE]
 
G

Gus

Hi Crystal,

Well, what can I say. I just won't give up. The code runs and then I get a
message box (Lotus Notes MailMan Error : You must provide a file path) Click
OK. Then one more error message box pops up with (Lotus Notes MailMan Error:
Exception occurred.) Click OK. Then Lotus Notes opens with the Reminders
Report with a properly formatted email. In the TO: of the email only the
first users email address appears. At the same time the Microsoft Access
Debugger pops up and reads (Microsoft Access has encountered a problem and
needs to close. We are sorry for the inconvenienece) and Access Crashes.
Thanks. Have a good evening.

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" _

In my Module(Module1) sometimes points to the ---> Docmd.SendObject _ as
the one of the errors.
LoopAgmtsSendEmail strSQL
End Sub


Option Compare Database

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 Report" _
, acFormatHTML _
, r!email _
, _
, _
, "Your Reminder for " & Format(Date, "ddd m-d-yy"), "Good
Morning, your report is attached" _
, True _


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 said:
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
*


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 said:
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, _
, _
, _
, _
"Your Reminder", _
"Good Morning!", _
True
:

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.
[/QUOTE][/QUOTE][/QUOTE]
 
S

strive4peace

Hi Gus,

email your database to me (with some sample data!) -- compact/repair and
then zip -- I will take a look... please specify what to look at :)

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


Hi Crystal,

Well, what can I say. I just won't give up. The code runs and then I get a
message box (Lotus Notes MailMan Error : You must provide a file path) Click
OK. Then one more error message box pops up with (Lotus Notes MailMan Error:
Exception occurred.) Click OK. Then Lotus Notes opens with the Reminders
Report with a properly formatted email. In the TO: of the email only the
first users email address appears. At the same time the Microsoft Access
Debugger pops up and reads (Microsoft Access has encountered a problem and
needs to close. We are sorry for the inconvenienece) and Access Crashes.
Thanks. Have a good evening.

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" _

In my Module(Module1) sometimes points to the ---> Docmd.SendObject _ as
the one of the errors.
LoopAgmtsSendEmail strSQL
End Sub


Option Compare Database

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 Report" _
, acFormatHTML _
, r!email _
, _
, _
, "Your Reminder for " & Format(Date, "ddd m-d-yy"), "Good
Morning, your report is attached" _
, True _


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 said:
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
*


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

:

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, _
, _
, _
, _
"Your Reminder", _
"Good Morning!", _
True
:

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.
[/QUOTE][/QUOTE][/QUOTE]
 
G

Gus

Hi Crystal,

Thanks. I have emailed the DB to you.

strive4peace said:
Hi Gus,

email your database to me (with some sample data!) -- compact/repair and
then zip -- I will take a look... please specify what to look at :)

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


Hi Crystal,

Well, what can I say. I just won't give up. The code runs and then I get a
message box (Lotus Notes MailMan Error : You must provide a file path) Click
OK. Then one more error message box pops up with (Lotus Notes MailMan Error:
Exception occurred.) Click OK. Then Lotus Notes opens with the Reminders
Report with a properly formatted email. In the TO: of the email only the
first users email address appears. At the same time the Microsoft Access
Debugger pops up and reads (Microsoft Access has encountered a problem and
needs to close. We are sorry for the inconvenienece) and Access Crashes.
Thanks. Have a good evening.

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" _

In my Module(Module1) sometimes points to the ---> Docmd.SendObject _ as
the one of the errors.
LoopAgmtsSendEmail strSQL
End Sub


Option Compare Database

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 Report" _
, acFormatHTML _
, r!email _
, _
, _
, "Your Reminder for " & Format(Date, "ddd m-d-yy"), "Good
Morning, your report is attached" _
, True _


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 said:
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

:

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
 
S

strive4peace

Chase Error, Option Explicit, Test Constructed SQL, Rethink Structure
-------

Hi Gus,

since you didn't tell me what to look at, I closed ytour Switchboard and
found the routine using SendObject -- then I found the code that called
it, behind form --> EmailQuery

To see what was going on, I added the STOP and DEBUG lines to the code
for your 'Send Email' button. These statements are lined up on the Left
because they are temporary and will be removed (easier to spot that
way). Everything else is as you had it -- except for 'Option Explicit'

'~~~~~~~~~~~~~~
Option Explicit

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" _

Debug.Print strSQL
Stop

LoopAgmtsSendEmail strSQL
End Sub
'~~~~~

Notice how the 'Option Explicit' statement has been added to the top of
the module sheet...

--- Option Explicit ---

It is always a good idea to DIMension your variables before you use them

Dim mStr As String

to force variable delaration (which I HIGHLY recommend), put this at the
top of your module:

Option Explicit

you can do Tools, Options... and check "require variable declaration" so
that any new modules created will have the Option Explicit statement
automatically at the top. Modules already existing will need to have
the line added manually

Another handy thing in declaring variables is that, if Access recognizes
the variable name you have typed as a variable that has been
dimensioned, it will change the case for you after you move off the line.

mstr --> mStr

When you compile your code, if any variable names are used that are
different than what was dimensioned, or is the wrong data type, Access
will complain -- one less problem to track down at Run-Time...

***
~~~~~~~~~~~~~~~~~~~~~~~`
anyway, the reason Debug.Print was done was to test the SQL of what is
being generated.

when the code came to the STOP statement, it stops and that statement is
highlighted in yellow

Because we don't want to leave code hanging open, Reset the code so you
are no longer in execution mode

--> from the menu --> Run, Reset

1. press CTRL-G to show the debuG window, if it is not displayed.

2. highlight the SQL that was generated and CUT (Ctrl-X)

3. make a New Query

4. go to the SQL view and paste the SQL statement that was generated.

Now, click on the Design View button of the Query.

Best fit all the columns on the grid -- or make each wide enough to show
the contents.

Notice how Access has added 'Expr1:' to one of your columns -- that is
because you have specified a fieldname -- RACA_Agr_Num -- that does not
exist...

here is what the column says:

Expr1: tblAgreements.RACA_Agr_Num

you DO, however, have a field called 'Agr_Num' and this is what I assume
you mean

this line of code:
& ", tblAgreements.RACA_Agr_Num " _
is changed to this -->
& ", tblAgreements.Agr_Num " _


also, you have not given the SQL criteria for the record that is
displayed ... don't you want JUST the person shown on the form to get
email? Or the Agreement shown? Or other criteria (such as
'Date_Action_Reminder >= (Date()-1)' ... and how do you plan to make the
records that are resolved?


To make the SQL easier to read, it is good use ALIASES for your
tablenames...
FROM tblUSers AS U ...tblAgreements AS A

'~~~~~~~~~~~~~~
Private Sub cmdSendEmail_Click()
Dim strSQL As String

'save record if changes have been made
If Me.Dirty Then Me.Dirty = False

strSQL = "SELECT U.User_FName AS UserName" _
& ", A.Agr_Num" _
& ", A.Action_Item" _
& ", A.Date_Action_Reminder" _
& ", A.Date_Action_Required" _
& ", U.Team" _
& ", U.Email" _
& " FROM tblUSers AS U " _
& " INNER JOIN tblAgreements AS A " _
& " ON U.User_ID = A.UserID "

Debug.Print strSQL
Stop

'---- add criteria if a current record is displayed
' If Not Me.NewRecord Then
'... but wait, we can't -- we need to examine the
'SQL that is constructed and see which field is also on the form


'need to do some checking on structures before we can finish

' LoopAgmtsSendEmail strSQL
End Sub

'~~~~~~~~~~~~~~

Background:
tblUsers has UserID autonumber primary key
tblAgreements has AgrID autonumber primary key
tblAgreements has UserID as a foreign key to the parent table

Gus, is this form going to used for data entry or changes? I see that
the recordSource for your form is a query that gets its information from
the parent and child tables...

If you will add and change data here, you need to set this up as a
main/subform -- or just a main form that DISPLAYS data from Users and
allows modifications to Agreements.

At any rate, UserID needs to be added to the RecordSet to best identify
the user that the output will be filtered for.

If you are just going to send Agreements (even though some users may get
2 of them), then AgrID should be added to the form RecordSet (since it
is the Primary Key) so you can capture it.

It is a good idea to make sure the ID fields for each table you are
getting data from are also in the RecordSet (Query) -- User_Id and AgrID
in this case.

'~~~~~~~~~~~~~~~~~

as for limiting by date...

Would it not be more wise to create a table for items to follow-up on
Agreements something like this:

*Followup*
FupID, autonumber
AgrID, long integer, DefaultValue --> null -- FK to Agreements
FolTypID, long integer, DefaultValue --> null -- FK to FollowTypes
DatePlan, date
DateDone, date

and then if there are more field such as who it is assigned to, etc ...
depends how much you want to track

you now have a way to see if the item can drop off reports -- If
DateDone is filled. You also have a way to set up differnt types of
actions ... lunch, meeting, call, ...

*FollowTypes*
FolTypID, autonumber
FollowType, text --> Reminder, Required, etc
maybe also some kind of category for this followup type

~~~
It seems you have 2 date fields -- each for a different type of followup
in your Agreements table. You need to re-think this ... are you going
to add a field everytime you need to set up another type of contact?

Wouldn't it be nice to see a report by date regardless of what needs to
be done?

Until the data structure is solid, there is no point to discuss how to
make the forms work

~~~~

now that you have absorbed more information about structuring data, do this:

1. Print your relationship diagram (instructions in the 30-page doc for
good layout)

2. make a pot of hot tea

3. get a clipboard, paper, pencils, pen, marker, highlighter -- whatever

4. now get comfy in a nice chair -- sit for a few hours, think, and make
notes.

The time you invest now into making the foundation of your database
strong will more than pay for itself in the future.



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

Gus

Notice how Access has added 'Expr1:' to one of your columns -- that is
because you have specified a fieldname -- RACA_Agr_Num -- that does not
exist... -
---> Got it, I am not sure why I could not figure out my own field/key
names…I changed this.

also, you have not given the SQL criteria for the record that is
displayed ... don't you want JUST the person shown on the form to get
email? --->Yes this is correct. I want the information pertaining to the
user to be sent to him or her. I want the Code to pull the information based
on a date criteria…

Or the Agreement shown? Or other criteria (such as
'Date_Action_Reminder >= (Date()-1)' ... and how do you plan to make the
records that are resolved? --->This, I am not sure I understand.


Gus, is this form going to be used for data entry or changes? I see that
the recordSource for your form is a query that gets its information from the
parent and child tables...---->No this form will not be used for data-entry,
I just created it when I was trying to pull the information to the form and
then emailing that information. I only wanted a form were I could run my code
execution button.

If you are just going to send Agreements (even though some users may get 2
of them), then AgrID should be added to the form RecordSet (since it is the
Primary Key) so you can capture it. ---->Okay, cool..Users could get multiple
reminders for the same agreement and multiple agreement reminders.

It is a good idea to make sure the ID fields for each table you are
getting data from are also in the RecordSet (Query) -- User_Id and AgrID in
this case. ---->Now that you explain this it makes a lot of sense.

Would it not be more wise to create a table for items to follow-up on
Agreements something like this:

*Followup*
FupID, autonumber
AgrID, long integer, DefaultValue --> null -- FK to Agreements
FolTypID, long integer, DefaultValue --> null -- FK to FollowTypes
DatePlan, date
DateDone, date

and then if there are more field such as who it is assigned to, etc ...
depends how much you want to track
Would it not be more wise to create a table for items to follow-up on
Agreements something like this:

*Followup*
FupID, autonumber
AgrID, long integer, DefaultValue --> null -- FK to Agreements
FolTypID, long integer, DefaultValue --> null -- FK to FollowTypes
DatePlan, date
DateDone, date

and then if there are more field such as who it is assigned to, etc ...
depends how much you want to track

you now have a way to see if the item can drop off reports -- If
DateDone is filled. You also have a way to set up differnt types of
actions ... lunch, meeting, call, ...

*FollowTypes*
FolTypID, autonumber
FollowType, text --> Reminder, Required, etc
maybe also some kind of category for this followup type

--->This is something I was thinking about, but had not really thought
through how I was going to do it. You are right on….

It seems you have 2 date fields -- each for a different type of followup
in your Agreements table. You need to re-think this ... are you going
to add a field everytime you need to set up another type of contact?

Wouldn't it be nice to see a report by date regardless of what needs to
be done? ---->Yes, as sometimes reminders are not specific to an
agreement/contract. You may have seen I put a reminder for updating security
passwd. This is done sometimes by department managers to remind themselves of
tasks they must have their entire department complete. When I thought about
the Criteria for the data, I thought of something that would always be
populated (Date), (this is required in the user form). Unlike AgrNum, which
is not required.
now that you have absorbed more information about structuring data, do this:

1. Print your relationship diagram (instructions in the 30-page doc for
good layout)

2. make a pot of hot tea

3. get a clipboard, paper, pencils, pen, marker, highlighter -- whatever

4. now get comfy in a nice chair -- sit for a few hours, think, and make
notes.

The time you invest now into making the foundation of your database
strong will more than pay for itself in the future.

Looks like it will be a few hours before I post. Let me think and get back
to you….Thank you

Oh, it would still be great if I could get the query to work based on the
structure of my current DB. I am thinking it is going to be a while before I
can release the updated DB to my users. The DB currently used has been split
and is working well. Have a great day!Thanks for sharing your knowledge!
 
S

strive4peace

Hi Gus,

"I want the Code to pull the information based on a date criteria…"

what is your criteria?

more comments in-line...


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Or the Agreement shown? Or other criteria (such as
'Date_Action_Reminder >= (Date()-1)' ... and how do you plan to make the
records that are resolved? --->This, I am not sure I understand.


Wouldn't it be nice to see a report by date regardless of what needs to
be done? ---->Yes, as sometimes reminders are not specific to an
agreement/contract. You may have seen I put a reminder for updating security
passwd. This is done sometimes by department managers to remind themselves of
tasks they must have their entire department complete. When I thought about
the Criteria for the data, I thought of something that would always be
populated (Date), (this is required in the user form). Unlike AgrNum, which
is not required.

.... kinda thought that, which is why I suggested the DefaultValue of
AgrID to be null in the suggested Followup table ... in that case, you
may also want to add User_ID to the Followup structure.

I don't know your business well enough to know exactly how followups
should be set up -- only that it is wise to make a different structure
for them...
Looks like it will be a few hours before I post. Let me think and get back
to you….Thank you

take your time, make it right. Data structuring is an iterative process...
Oh, it would still be great if I could get the query to work based on the
structure of my current DB.

duct-tape is cheap; your time isn't -- look at the amount of time you
will spend in the life of your database -- a bit more now saves you lots
more later...

I am thinking it is going to be a while before I
can release the updated DB to my users. The DB currently used has been split
and is working well. Have a great day!Thanks for sharing your knowledge!
you're welcome, Gus!
 
G

Gus

Hi Crystal,

My criteria would be Date_Action_Reminder, I think you mentioned it below,
but would 'Date_Action_Reminder >= (Date()-1) pull the data for the day that
I run the code?

To be more specific the code would run daily and it would pull all reminders
that have a Date_Action_Reminder for that day.

Thanks. Hope that I do not confuse you too much with my way of explaining
that.

Have a nice day!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top