send mail from access

Van Eenoo Stefaan · Sep 20, 2003

Hey, in the code below, access uses one e-mailadress at the time. What is
the code to send all the adresses in one time to the Emailadres ? (now, if i
have 100 adresses, so I need to create the text 100 times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

Ken Snell · Sep 20, 2003

Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2] & ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Van Eenoo Stefaan · Sep 20, 2003

great !!!!!!
thank you.

Another problem:

When I use a criteria in the querie, it works.

But, when I use as a criteria the following [forms]![form1]![cboCriteria]

CboCriteria being a combobox on my form (from where I send the mail) i get
an error message (too few arguments).
Do you have any idea ?
The querie works independant of the mail.

Ken Snell said:
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2] & ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

Van Eenoo Stefaan said:

Hey, in the code below, access uses one e-mailadress at the time. What is
the code to send all the adresses in one time to the Emailadres ? (now,

Click to expand...

if

i
have 100 adresses, so I need to create the text 100 times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

Click to expand...

Ken Snell · Sep 20, 2003

Post the SQL statement of the query that contains the Forms reference.

--
Ken Snell
<MS ACCESS MVP>

Van Eenoo Stefaan said:
great !!!!!!
thank you.

Another problem:

When I use a criteria in the querie, it works.

But, when I use as a criteria the following [forms]![form1]![cboCriteria]

CboCriteria being a combobox on my form (from where I send the mail) i get
an error message (too few arguments).
Do you have any idea ?
The querie works independant of the mail.

Ken Snell said:

Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2] & ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

Van Eenoo Stefaan said:

Hey, in the code below, access uses one e-mailadress at the time. What is
the code to send all the adresses in one time to the Emailadres ?

Click to expand...

Click to expand...

(now,

if

i
have 100 adresses, so I need to create the text 100 times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

Click to expand...

Click to expand...

Van Eenoo Stefaan · Sep 21, 2003

I found the solution.

I used the eval function. eval("[forms]![form1]![cboCategorie]")

Thank you Ken for the time and effort.

Ken Snell said:
Post the SQL statement of the query that contains the Forms reference.

--
Ken Snell
<MS ACCESS MVP>

Van Eenoo Stefaan said:

great !!!!!!
thank you.

Another problem:

When I use a criteria in the querie, it works.

But, when I use as a criteria the following [forms]![form1]![cboCriteria]

CboCriteria being a combobox on my form (from where I send the mail) i get
an error message (too few arguments).
Do you have any idea ?
The querie works independant of the mail.

Ken Snell said:

Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2] & ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

Hey, in the code below, access uses one e-mailadress at the time.

Click to expand...

What
is

the code to send all the adresses in one time to the Emailadres ?

Click to expand...

(now,

if
i
have 100 adresses, so I need to create the text 100 times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

Click to expand...

Click to expand...

Hafeez Esmail · Sep 26, 2003

Hi Ken,

I want to do something similar to this and have thought of
two ways to carry it (neither of which I know how to do).

Case one (ideal solution):
I have a query based on a paramter (i.e. Last_Name). The
following tasks is what I want it to do automatically:
1 Run query
2 Enter employee's last name based on a table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need to be executed and
emailed to each employee respectively. Is there an easy
way to do this?

Hafeez Esmail

Van Eenoo Stefaan said:
-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2] & ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

Van Eenoo Stefaan said:

Hey, in the code below, access uses one e-mailadress at the time. What is
the code to send all the adresses in one time to the

Click to expand...

Emailadres ? (now, if
i

have 100 adresses, so I need to create the text 100 times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

Click to expand...

.

Ken Snell · Sep 28, 2003

RE: case one, you cannot use a macro. You will need to use VBA code to open
a recordset based on the table and then to loop through the recordset. I'm
not sure what you mean by the query is using a parameter based on the
person's last name? If you have a basic query (no parameter), you don't need
to run it in order to email it; just email the query (SendObject) and it
will run and export its results. Something like this:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate arguments to this line
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

If what you desire is to use each person's last name as the parameter for
the query and to email each "filtered" query to just that one person, I
don't have a ready answer for you at the moment. I would anticipate that
you'd need to use the QueryDefs collection of the Database object in order
to get the query and evaluate the parameter using each last name from the
table, and then likely you'd need to have that query export its results into
a temporary table, and then email that table; then clear the table and go to
the next record in the table until all done. I've not worked with this
before, so I'm not sure of all the nuances and will need a bit of time to
try a few things before I can post a possible solution for you.

I'm tied up on some personal business at the moment, so it will be a few
days before I can post a possible idea.

--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:
Hi Ken,

I want to do something similar to this and have thought of
two ways to carry it (neither of which I know how to do).

Case one (ideal solution):
I have a query based on a paramter (i.e. Last_Name). The
following tasks is what I want it to do automatically:
1 Run query
2 Enter employee's last name based on a table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need to be executed and
emailed to each employee respectively. Is there an easy
way to do this?

Hafeez Esmail

-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2] & ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

Van Eenoo Stefaan said:

Hey, in the code below, access uses one e-mailadress at the time. What is
the code to send all the adresses in one time to the

Click to expand...

Emailadres ? (now, if
i

have 100 adresses, so I need to create the text 100 times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

Click to expand...

.

Click to expand...

Ken Snell · Sep 28, 2003

Error in my first example for case one...sorry:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate arguments to this line
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
--
Ken Snell
<MS ACCESS MVP>

Ken Snell said:
RE: case one, you cannot use a macro. You will need to use VBA code to open
a recordset based on the table and then to loop through the recordset. I'm
not sure what you mean by the query is using a parameter based on the
person's last name? If you have a basic query (no parameter), you don't need
to run it in order to email it; just email the query (SendObject) and it
will run and export its results. Something like this:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate arguments to this line
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

If what you desire is to use each person's last name as the parameter for
the query and to email each "filtered" query to just that one person, I
don't have a ready answer for you at the moment. I would anticipate that
you'd need to use the QueryDefs collection of the Database object in order
to get the query and evaluate the parameter using each last name from the
table, and then likely you'd need to have that query export its results into
a temporary table, and then email that table; then clear the table and go to
the next record in the table until all done. I've not worked with this
before, so I'm not sure of all the nuances and will need a bit of time to
try a few things before I can post a possible solution for you.

I'm tied up on some personal business at the moment, so it will be a few
days before I can post a possible idea.

--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:

Hi Ken,

I want to do something similar to this and have thought of
two ways to carry it (neither of which I know how to do).

Case one (ideal solution):
I have a query based on a paramter (i.e. Last_Name). The
following tasks is what I want it to do automatically:
1 Run query
2 Enter employee's last name based on a table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need to be executed and
emailed to each employee respectively. Is there an easy
way to do this?

Hafeez Esmail

-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2] & ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

Hey, in the code below, access uses one e-mailadress at the time. What is
the code to send all the adresses in one time to the Emailadres ? (now, if
i
have 100 adresses, so I need to create the text 100 times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML, strEmailadress, , , strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

.

Click to expand...

Click to expand...

Hafeez Esmail · Sep 30, 2003

I've set the references to DA 3.6 (I wasn't in Design
Mode) but the error I'm having now is at my Where condition

(Where rstrec!Last_Name = strLastName)

Public Function MakeRpts()

Dim rstrec As DAO.Recordset
Dim rstname As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

Set dbs = CurrentDb()
Set rstname = dbs.OpenRecordset("TblFHStaffList")
Set rstrec = dbs.OpenRecordset("QryPredef120")
If rstname.BOF = False And rstname.EOF = False Then
rstname.MoveFirst
Do While rstname.EOF = False
Set strLastName = rstname!Last_Name
If rstrec.BOF = False And rstrec.EOF = False Then
rstrec.MoveFirst
Do While rstrec.EOF = False
Where rstrec!Last_Name = strLastName
rstrec.MoveNext
Loop
DoCmd.Save acReport, "RptPredef" & strLastName
End If
rst.MoveNext
Loop
rstname.Close
Set rstname = Nothing
rstrec.Close
Set rstrec = Nothing
dbs.Close
Set dbs = Nothing
End Function

-----Original Message-----
Error in my first example for case one...sorry:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset ("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate arguments to this line
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
--
Ken Snell
<MS ACCESS MVP>

use VBA code to
open

Click to expand...

through
the recordset. I'm

parameter), you don't
need arguments to this
line

Click to expand...

as
the parameter for

export its results
into clear the table and go
to

Click to expand...

so
it will be a few Last_Name).

The

following tasks is what I want it to do automatically:
1 Run query
2 Enter employee's last name based on a table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need to be executed and
emailed to each employee respectively. Is there an easy
way to do this?

Hafeez Esmail

-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2] & ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML, strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

Hey, in the code below, access uses one e- mailadress at
the time. What is
the code to send all the adresses in one time to the
Emailadres ? (now, if
i
have 100 adresses, so I need to create the text 100
times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML, strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

.

Click to expand...

.

Click to expand...

.

Ken Snell · Oct 2, 2003

Yes, you can open more than one recordset in a code snippet.

You don't need the Where word in the line of code. Change it to this:
rstrec!Last_Name = strLastName

You correctly identified that you needed to set a reference to DAO library.
--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:
I've set the references to DA 3.6 (I wasn't in Design
Mode) but the error I'm having now is at my Where condition

(Where rstrec!Last_Name = strLastName)

Public Function MakeRpts()

Dim rstrec As DAO.Recordset
Dim rstname As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

Set dbs = CurrentDb()
Set rstname = dbs.OpenRecordset("TblFHStaffList")
Set rstrec = dbs.OpenRecordset("QryPredef120")
If rstname.BOF = False And rstname.EOF = False Then
rstname.MoveFirst
Do While rstname.EOF = False
Set strLastName = rstname!Last_Name
If rstrec.BOF = False And rstrec.EOF = False Then
rstrec.MoveFirst
Do While rstrec.EOF = False
Where rstrec!Last_Name = strLastName
rstrec.MoveNext
Loop
DoCmd.Save acReport, "RptPredef" & strLastName
End If
rst.MoveNext
Loop
rstname.Close
Set rstname = Nothing
rstrec.Close
Set rstrec = Nothing
dbs.Close
Set dbs = Nothing
End Function

-----Original Message-----
Error in my first example for case one...sorry:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset ("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate arguments to this line
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
--
Ken Snell
<MS ACCESS MVP>

RE: case one, you cannot use a macro. You will need to use VBA code to
open
a recordset based on the table and then to loop

Click to expand...

through
the recordset. I'm

not sure what you mean by the query is using a parameter based on the
person's last name? If you have a basic query (no parameter), you don't
need
to run it in order to email it; just email the query (SendObject) and it
will run and export its results. Something like this:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset ("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate arguments to this
line
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

If what you desire is to use each person's last name

Click to expand...

as
the parameter for

the query and to email each "filtered" query to just that one person, I
don't have a ready answer for you at the moment. I would anticipate that
you'd need to use the QueryDefs collection of the Database object in order
to get the query and evaluate the parameter using each last name from the
table, and then likely you'd need to have that query export its results
into
a temporary table, and then email that table; then clear the table and go
to
the next record in the table until all done. I've not worked with this
before, so I'm not sure of all the nuances and will need a bit of time to
try a few things before I can post a possible solution for you.

I'm tied up on some personal business at the moment,

Click to expand...

so
it will be a few

days before I can post a possible idea.

--
Ken Snell
<MS ACCESS MVP>
Hi Ken,

I want to do something similar to this and have thought of
two ways to carry it (neither of which I know how to do).

Case one (ideal solution):
I have a query based on a paramter (i.e.

Click to expand...

Last_Name).

The
following tasks is what I want it to do automatically:
1 Run query
2 Enter employee's last name based on a table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need to be executed and
emailed to each employee respectively. Is there an easy
way to do this?

Hafeez Esmail

-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2] & ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML, strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

Hey, in the code below, access uses one e- mailadress at
the time. What is
the code to send all the adresses in one time to the
Emailadres ? (now, if
i
have 100 adresses, so I need to create the text 100
times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML, strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

.

.

Click to expand...

.

Click to expand...

Hafeez Esmail · Oct 2, 2003

Hi Ken,

Thanks for the confirmation :-)

I seem to be having a problem with this code now
DoCmd.Save acReport, "RptPredef" & strLastName

I want to save the resulting search (i don't care which
format it's in) but I can't seem to be able to do it.

Thanks in advance
Hafeez Esmail

Hafeez Esmail said:
-----Original Message-----
Yes, you can open more than one recordset in a code snippet.

You don't need the Where word in the line of code. Change it to this:
rstrec!Last_Name = strLastName

You correctly identified that you needed to set a reference to DAO library.
--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:

I've set the references to DA 3.6 (I wasn't in Design
Mode) but the error I'm having now is at my Where condition

(Where rstrec!Last_Name = strLastName)

Public Function MakeRpts()

Dim rstrec As DAO.Recordset
Dim rstname As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

Set dbs = CurrentDb()
Set rstname = dbs.OpenRecordset("TblFHStaffList")
Set rstrec = dbs.OpenRecordset("QryPredef120")
If rstname.BOF = False And rstname.EOF = False Then
rstname.MoveFirst
Do While rstname.EOF = False
Set strLastName = rstname!Last_Name
If rstrec.BOF = False And rstrec.EOF = False Then
rstrec.MoveFirst
Do While rstrec.EOF = False
Where rstrec!Last_Name = strLastName
rstrec.MoveNext
Loop
DoCmd.Save acReport, "RptPredef" & strLastName
End If
rst.MoveNext
Loop
rstname.Close
Set rstname = Nothing
rstrec.Close
Set rstrec = Nothing
dbs.Close
Set dbs = Nothing
End Function

-----Original Message-----
Error in my first example for case one...sorry:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate
arguments to this line
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
--
Ken Snell
<MS ACCESS MVP>

message
RE: case one, you cannot use a macro. You will need to
use VBA code to
open
a recordset based on the table and then to loop through
the recordset. I'm
not sure what you mean by the query is using a
parameter based on the
person's last name? If you have a basic query (no
parameter), you don't
need
to run it in order to email it; just email the query
(SendObject) and it
will run and export its results. Something like this:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate
arguments to this
line
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

If what you desire is to use each person's last name as
the parameter for
the query and to email each "filtered" query to just
that one person, I
don't have a ready answer for you at the moment. I
would anticipate that
you'd need to use the QueryDefs collection of the
Database object in order
to get the query and evaluate the parameter using each
last name from the
table, and then likely you'd need to have that query
export its results
into
a temporary table, and then email that table; then
clear the table and go
to
the next record in the table until all done. I've not
worked with this
before, so I'm not sure of all the nuances and will
need a bit of time to
try a few things before I can post a possible solution
for you.

I'm tied up on some personal business at the moment, so
it will be a few
days before I can post a possible idea.

--
Ken Snell
<MS ACCESS MVP>
Hi Ken,

I want to do something similar to this and have
thought of
two ways to carry it (neither of which I know how to
do).

Case one (ideal solution):
I have a query based on a paramter (i.e. Last_Name).
The
following tasks is what I want it to do automatically:
1 Run query
2 Enter employee's last name based on a table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need to be executed
and
emailed to each employee respectively. Is there an
easy
way to do this?

Hafeez Esmail

-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2] & ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

Hey, in the code below, access uses one e-
mailadress at
the time. What is
the code to send all the adresses in one time to
the
Emailadres ? (now, if
i
have 100 adresses, so I need to create the text 100
times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

.

.

.

Click to expand...

Click to expand...

.

Ken Snell · Oct 4, 2003

You want to save the results of the query that is being displayed in the
report? If yes, the DoCmd.Save command will not do this for you. That
command is used to save changes made to the design view of an object. It
won't save the displayed data.

You need to use a MakeTable query that is based on the report's query to put
the data into a table so that you can save it. Alternatively, you can "save"
the report by using the OutputTo command to save the report in an ".rtf"
format. You wouldn't need to open the report first; just be sure that the
report's query is set up and then use this command:
DoCmd.OutputTo acReport, "ReportName", acFormatRTF,
"C:\MyFolder\ReportName.rtf"

--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:
Hi Ken,

Thanks for the confirmation
I seem to be having a problem with this code now
DoCmd.Save acReport, "RptPredef" & strLastName

I want to save the resulting search (i don't care which
format it's in) but I can't seem to be able to do it.

Thanks in advance
Hafeez Esmail

-----Original Message-----
Yes, you can open more than one recordset in a code snippet.

You don't need the Where word in the line of code. Change it to this:
rstrec!Last_Name = strLastName

You correctly identified that you needed to set a reference to DAO library.
--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:

I've set the references to DA 3.6 (I wasn't in Design
Mode) but the error I'm having now is at my Where condition

(Where rstrec!Last_Name = strLastName)

Public Function MakeRpts()

Dim rstrec As DAO.Recordset
Dim rstname As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

Set dbs = CurrentDb()
Set rstname = dbs.OpenRecordset("TblFHStaffList")
Set rstrec = dbs.OpenRecordset("QryPredef120")
If rstname.BOF = False And rstname.EOF = False Then
rstname.MoveFirst
Do While rstname.EOF = False
Set strLastName = rstname!Last_Name
If rstrec.BOF = False And rstrec.EOF = False Then
rstrec.MoveFirst
Do While rstrec.EOF = False
Where rstrec!Last_Name = strLastName
rstrec.MoveNext
Loop
DoCmd.Save acReport, "RptPredef" & strLastName
End If
rst.MoveNext
Loop
rstname.Close
Set rstname = Nothing
rstrec.Close
Set rstrec = Nothing
dbs.Close
Set dbs = Nothing
End Function

-----Original Message-----
Error in my first example for case one...sorry:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate
arguments to this line
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
--
Ken Snell
<MS ACCESS MVP>

message
RE: case one, you cannot use a macro. You will need to
use VBA code to
open
a recordset based on the table and then to loop
through
the recordset. I'm
not sure what you mean by the query is using a
parameter based on the
person's last name? If you have a basic query (no
parameter), you don't
need
to run it in order to email it; just email the query
(SendObject) and it
will run and export its results. Something like this:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate
arguments to this
line
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

If what you desire is to use each person's last name
as
the parameter for
the query and to email each "filtered" query to just
that one person, I
don't have a ready answer for you at the moment. I
would anticipate that
you'd need to use the QueryDefs collection of the
Database object in order
to get the query and evaluate the parameter using each
last name from the
table, and then likely you'd need to have that query
export its results
into
a temporary table, and then email that table; then
clear the table and go
to
the next record in the table until all done. I've not
worked with this
before, so I'm not sure of all the nuances and will
need a bit of time to
try a few things before I can post a possible solution
for you.

I'm tied up on some personal business at the moment,
so
it will be a few
days before I can post a possible idea.

--
Ken Snell
<MS ACCESS MVP>
Hi Ken,

I want to do something similar to this and have
thought of
two ways to carry it (neither of which I know how to
do).

Case one (ideal solution):
I have a query based on a paramter (i.e.
Last_Name).
The
following tasks is what I want it to do
automatically:
1 Run query
2 Enter employee's last name based on a table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need to be executed
and
emailed to each employee respectively. Is there an
easy
way to do this?

Hafeez Esmail

-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2] & ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

Hey, in the code below, access uses one e-
mailadress at
the time. What is
the code to send all the adresses in one time to
the
Emailadres ? (now, if
i
have 100 adresses, so I need to create the text
100
times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

.

.

.

Click to expand...

.

Click to expand...

Ken Snell · Oct 7, 2003

How are you planning to compare the two queries? The data contents? The SQL
statement?

--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:
Hi Ken,

I have made qryA. I'd like to compare qryTmp in my code
to qryA and if there is a discrepancy, replace qryA (i.e.
save qryTemp as qryA)
Is this possible?

Once again Thanks for all your help! There are so many
keywords I don't even know where to start looking!

Hafeez Esmail

-----Original Message-----
You want to save the results of the query that is being displayed in the
report? If yes, the DoCmd.Save command will not do this for you. That
command is used to save changes made to the design view of an object. It
won't save the displayed data.

You need to use a MakeTable query that is based on the report's query to put
the data into a table so that you can save it. Alternatively, you can "save"
the report by using the OutputTo command to save the report in an ".rtf"
format. You wouldn't need to open the report first; just be sure that the
report's query is set up and then use this command:
DoCmd.OutputTo acReport, "ReportName", acFormatRTF,
"C:\MyFolder\ReportName.rtf"

--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:

Hi Ken,

Thanks for the confirmation
I seem to be having a problem with this code now
DoCmd.Save acReport, "RptPredef" & strLastName

I want to save the resulting search (i don't care which
format it's in) but I can't seem to be able to do it.

Thanks in advance
Hafeez Esmail

-----Original Message-----
Yes, you can open more than one recordset in a code
snippet.

You don't need the Where word in the line of code. Change
it to this:
rstrec!Last_Name = strLastName

You correctly identified that you needed to set a
reference to DAO library.
--
Ken Snell
<MS ACCESS MVP>

I've set the references to DA 3.6 (I wasn't in Design
Mode) but the error I'm having now is at my Where
condition

(Where rstrec!Last_Name = strLastName)

Public Function MakeRpts()

Dim rstrec As DAO.Recordset
Dim rstname As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

Set dbs = CurrentDb()
Set rstname = dbs.OpenRecordset("TblFHStaffList")
Set rstrec = dbs.OpenRecordset("QryPredef120")
If rstname.BOF = False And rstname.EOF = False Then
rstname.MoveFirst
Do While rstname.EOF = False
Set strLastName = rstname!Last_Name
If rstrec.BOF = False And rstrec.EOF = False
Then
rstrec.MoveFirst
Do While rstrec.EOF = False
Where rstrec!Last_Name = strLastName
rstrec.MoveNext
Loop
DoCmd.Save acReport, "RptPredef" &
strLastName
End If
rst.MoveNext
Loop
rstname.Close
Set rstname = Nothing
rstrec.Close
Set rstrec = Nothing
dbs.Close
Set dbs = Nothing
End Function

-----Original Message-----
Error in my first example for case one...sorry:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last
name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate
arguments to this line
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
--
Ken Snell
<MS ACCESS MVP>

in
message
RE: case one, you cannot use a macro. You will need
to
use VBA code to
open
a recordset based on the table and then to loop
through
the recordset. I'm
not sure what you mean by the query is using a
parameter based on the
person's last name? If you have a basic query (no
parameter), you don't
need
to run it in order to email it; just email the query
(SendObject) and it
will run and export its results. Something like
this:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last
name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the appropriate
arguments to this
line
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

If what you desire is to use each person's last name
as
the parameter for
the query and to email each "filtered" query to just
that one person, I
don't have a ready answer for you at the moment. I
would anticipate that
you'd need to use the QueryDefs collection of the
Database object in order
to get the query and evaluate the parameter using
each
last name from the
table, and then likely you'd need to have that query
export its results
into
a temporary table, and then email that table; then
clear the table and go
to
the next record in the table until all done. I've
not
worked with this
before, so I'm not sure of all the nuances and will
need a bit of time to
try a few things before I can post a possible
solution
for you.

I'm tied up on some personal business at the moment,
so
it will be a few
days before I can post a possible idea.

--
Ken Snell
<MS ACCESS MVP>
Hi Ken,

I want to do something similar to this and have
thought of
two ways to carry it (neither of which I know how
to
do).

Case one (ideal solution):
I have a query based on a paramter (i.e.
Last_Name).
The
following tasks is what I want it to do
automatically:
1 Run query
2 Enter employee's last name based on a table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need to be
executed
and
emailed to each employee respectively. Is there
an
easy
way to do this?

Hafeez Esmail

-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst![email2]
& ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

Hey, in the code below, access uses one e-
mailadress at
the time. What is
the code to send all the adresses in one time
to
the
Emailadres ? (now, if
i
have 100 adresses, so I need to create the text
100
times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

.

.

.

.

Click to expand...

.

Click to expand...

Ken Snell · Oct 8, 2003

That is a tall order! And one that may not be a guaranteed 100% success,
depending upon how much must match.

All of the data.... each data value in each field in each record? How will
you sort the records so that you can compare them easily?

If we can identify some unique fields and values, we might create a
comparison query that could tell you if they are the same. However, if any
record's field has a Null value in it, this type of query will not find a
"match" even if both queries have the same Null value in the same field in
the same record.

Any particular reason you need to store the data in a table? Will the query
not be able to reproduce the data when you call it again? How about storing
the results each time instead of just the most recent?

--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:
The data. If the records are different, then replace all
of the qryA data with all of the qryTemp data.

Thanks

-----Original Message-----
How are you planning to compare the two queries? The data contents? The SQL
statement?

--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:

Hi Ken,

I have made qryA. I'd like to compare qryTmp in my code
to qryA and if there is a discrepancy, replace qryA (i.e.
save qryTemp as qryA)
Is this possible?

Once again Thanks for all your help! There are so many
keywords I don't even know where to start looking!

Hafeez Esmail

-----Original Message-----
You want to save the results of the query that is being
displayed in the
report? If yes, the DoCmd.Save command will not do this
for you. That
command is used to save changes made to the design view
of an object. It
won't save the displayed data.

You need to use a MakeTable query that is based on the
report's query to put
the data into a table so that you can save it.
Alternatively, you can "save"
the report by using the OutputTo command to save the
report in an ".rtf"
format. You wouldn't need to open the report first; just
be sure that the
report's query is set up and then use this command:
DoCmd.OutputTo acReport, "ReportName",
acFormatRTF,
"C:\MyFolder\ReportName.rtf"

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for the confirmation
I seem to be having a problem with this code now
DoCmd.Save acReport, "RptPredef" & strLastName

I want to save the resulting search (i don't care which
format it's in) but I can't seem to be able to do it.

Thanks in advance
Hafeez Esmail

-----Original Message-----
Yes, you can open more than one recordset in a code
snippet.

You don't need the Where word in the line of code.
Change
it to this:
rstrec!Last_Name = strLastName

You correctly identified that you needed to set a
reference to DAO library.
--
Ken Snell
<MS ACCESS MVP>

I've set the references to DA 3.6 (I wasn't in Design
Mode) but the error I'm having now is at my Where
condition

(Where rstrec!Last_Name = strLastName)

Public Function MakeRpts()

Dim rstrec As DAO.Recordset
Dim rstname As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

Set dbs = CurrentDb()
Set rstname = dbs.OpenRecordset("TblFHStaffList")
Set rstrec = dbs.OpenRecordset("QryPredef120")
If rstname.BOF = False And rstname.EOF = False Then
rstname.MoveFirst
Do While rstname.EOF = False
Set strLastName = rstname!Last_Name
If rstrec.BOF = False And rstrec.EOF = False
Then
rstrec.MoveFirst
Do While rstrec.EOF = False
Where rstrec!Last_Name = strLastName
rstrec.MoveNext
Loop
DoCmd.Save acReport, "RptPredef" &
strLastName
End If
rst.MoveNext
Loop
rstname.Close
Set rstname = Nothing
rstrec.Close
Set rstrec = Nothing
dbs.Close
Set dbs = Nothing
End Function

-----Original Message-----
Error in my first example for case one...sorry:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last
name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName Then
DoCmd.SendObject ' add the
appropriate
arguments to this line
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
--
Ken Snell
<MS ACCESS MVP>

"Ken Snell" <[email protected]>
wrote
in
message
RE: case one, you cannot use a macro. You will
need
to
use VBA code to
open
a recordset based on the table and then to loop
through
the recordset. I'm
not sure what you mean by the query is using a
parameter based on the
person's last name? If you have a basic query (no
parameter), you don't
need
to run it in order to email it; just email the
query
(SendObject) and it
will run and export its results. Something like
this:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last
name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName
Then
DoCmd.SendObject ' add the
appropriate
arguments to this
line
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

If what you desire is to use each person's last
name
as
the parameter for
the query and to email each "filtered" query to
just
that one person, I
don't have a ready answer for you at the moment.
I
would anticipate that
you'd need to use the QueryDefs collection of the
Database object in order
to get the query and evaluate the parameter using
each
last name from the
table, and then likely you'd need to have that
query
export its results
into
a temporary table, and then email that table;
then
clear the table and go
to
the next record in the table until all done. I've
not
worked with this
before, so I'm not sure of all the nuances and
will
need a bit of time to
try a few things before I can post a possible
solution
for you.

I'm tied up on some personal business at the
moment,
so
it will be a few
days before I can post a possible idea.

--
Ken Snell
<MS ACCESS MVP>
[email protected]...
Hi Ken,

I want to do something similar to this and have
thought of
two ways to carry it (neither of which I know
how
to
do).

Case one (ideal solution):
I have a query based on a paramter (i.e.
Last_Name).
The
following tasks is what I want it to do
automatically:
1 Run query
2 Enter employee's last name based on a table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need to be
executed
and
emailed to each employee respectively. Is
there
an
easy
way to do this?

Hafeez Esmail

-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst! [email2]
& ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

message
Hey, in the code below, access uses one e-
mailadress at
the time. What is
the code to send all the adresses in one
time
to
the
Emailadres ? (now, if
i
have 100 adresses, so I need to create the
text
100
times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

.

.

.

.

.

Click to expand...

.

Click to expand...

Hafeez Esmail · Oct 8, 2003

Hey now that you mention it, there's isn't a unique field
but there are two fields (ID1 and ID2) that when you
combine them make each record unique.
ID1 is the number of the general system and ID2 is the
individual part (e.g ID1=02315 ID2=05 is given to a
specific valve on a unique system)
Every record will contain the two "ID" fields, however, in
any record there may be up to seven fields can be NULL.
So would the comparison query approach work(i.e. 100%
success)?
The idea for comparing is cause it's different, it'll
email the newest collection to the appropriate person and
then the nextday it'll use the newest data as a basis for
comparison (that way it doesn't send the same email
everyday)

Thanks a lot Ken!
Hafeez Esmail

Hafeez Esmail said:
-----Original Message-----
That is a tall order! And one that may not be a guaranteed 100% success,
depending upon how much must match.

All of the data.... each data value in each field in each record? How will
you sort the records so that you can compare them easily?

If we can identify some unique fields and values, we might create a
comparison query that could tell you if they are the same. However, if any
record's field has a Null value in it, this type of query will not find a
"match" even if both queries have the same Null value in the same field in
the same record.

Any particular reason you need to store the data in a table? Will the query
not be able to reproduce the data when you call it again? How about storing
the results each time instead of just the most recent?

--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:

The data. If the records are different, then replace all
of the qryA data with all of the qryTemp data.

Thanks

-----Original Message-----
How are you planning to compare the two queries? The

Click to expand...

data
contents? The SQL

statement?

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

I have made qryA. I'd like to compare qryTmp in my code
to qryA and if there is a discrepancy, replace qryA (i.e.
save qryTemp as qryA)
Is this possible?

Once again Thanks for all your help! There are so many
keywords I don't even know where to start looking!

Hafeez Esmail

-----Original Message-----
You want to save the results of the query that is being
displayed in the
report? If yes, the DoCmd.Save command will not do this
for you. That
command is used to save changes made to the design view
of an object. It
won't save the displayed data.

You need to use a MakeTable query that is based on the
report's query to put
the data into a table so that you can save it.
Alternatively, you can "save"
the report by using the OutputTo command to save the
report in an ".rtf"
format. You wouldn't need to open the report first; just
be sure that the
report's query is set up and then use this command:
DoCmd.OutputTo acReport, "ReportName",
acFormatRTF,
"C:\MyFolder\ReportName.rtf"

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for the confirmation
I seem to be having a problem with this code now
DoCmd.Save acReport, "RptPredef" & strLastName

I want to save the resulting search (i don't care which
format it's in) but I can't seem to be able to do it.

Thanks in advance
Hafeez Esmail

-----Original Message-----
Yes, you can open more than one recordset in a code
snippet.

You don't need the Where word in the line of code.
Change
it to this:
rstrec!Last_Name = strLastName

You correctly identified that you needed to set a
reference to DAO library.
--
Ken Snell
<MS ACCESS MVP>

I've set the references to DA 3.6 (I wasn't in Design
Mode) but the error I'm having now is at my Where
condition

(Where rstrec!Last_Name = strLastName)

Public Function MakeRpts()

Dim rstrec As DAO.Recordset
Dim rstname As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

Set dbs = CurrentDb()
Set rstname = dbs.OpenRecordset ("TblFHStaffList")
Set rstrec = dbs.OpenRecordset("QryPredef120")
If rstname.BOF = False And rstname.EOF = False Then
rstname.MoveFirst
Do While rstname.EOF = False
Set strLastName = rstname!Last_Name
If rstrec.BOF = False And rstrec.EOF = False
Then
rstrec.MoveFirst
Do While rstrec.EOF = False
Where rstrec!Last_Name = strLastName
rstrec.MoveNext
Loop
DoCmd.Save acReport, "RptPredef" &
strLastName
End If
rst.MoveNext
Loop
rstname.Close
Set rstname = Nothing
rstrec.Close
Set rstrec = Nothing
dbs.Close
Set dbs = Nothing
End Function

-----Original Message-----
Error in my first example for case one...sorry:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last
name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName =

Click to expand...

strLastName
Then

DoCmd.SendObject ' add the
appropriate
arguments to this line
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
--
Ken Snell
<MS ACCESS MVP>

"Ken Snell"

Click to expand...

wrote
in
message
RE: case one, you cannot use a macro. You will
need
to
use VBA code to
open
a recordset based on the table and then to loop
through
the recordset. I'm
not sure what you mean by the query is using a
parameter based on the
person's last name? If you have a basic

Click to expand...

query
(no

parameter), you don't
need
to run it in order to email it; just email the
query
(SendObject) and it
will run and export its results. Something like
this:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last
name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName
Then
DoCmd.SendObject ' add the
appropriate
arguments to this
line
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

If what you desire is to use each person's last
name
as
the parameter for
the query and to email each "filtered"

Click to expand...

query
to

just
that one person, I
don't have a ready answer for you at the moment.
I
would anticipate that
you'd need to use the QueryDefs collection

Click to expand...

of
the

Database object in order
to get the query and evaluate the parameter using
each
last name from the
table, and then likely you'd need to have that
query
export its results
into
a temporary table, and then email that table;
then
clear the table and go
to
the next record in the table until all

Click to expand...

done.
I've

not
worked with this
before, so I'm not sure of all the nuances and
will
need a bit of time to
try a few things before I can post a possible
solution
for you.

I'm tied up on some personal business at the
moment,
so
it will be a few
days before I can post a possible idea.

--
Ken Snell
<MS ACCESS MVP>
[email protected]...
Hi Ken,

I want to do something similar to this

Click to expand...

and
have

thought of
two ways to carry it (neither of which I know
how
to
do).

Case one (ideal solution):
I have a query based on a paramter (i.e.
Last_Name).
The
following tasks is what I want it to do
automatically:
1 Run query
2 Enter employee's last name based on a table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need to be
executed
and
emailed to each employee respectively. Is
there
an
easy
way to do this?

Hafeez Esmail

-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst! [email2]
& ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

message

Hey, in the code below, access uses

Click to expand...

one
e-

mailadress at
the time. What is
the code to send all the adresses in one
time
to
the
Emailadres ? (now, if
i
have 100 adresses, so I need to create the
text
100
times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1! txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

.

.

.

.

.

.

Click to expand...

Click to expand...

.

Ken Snell · Oct 9, 2003

OK - here is a type of query that you can use to see if the new query's
records aren't equal to the old query's records (the ones stored in a table,
right?).

Essentially, you create a query that is based on the new query and the
table. Join every field from the table to its counterpart field in the
query. Make each join a LEFT JOIN from the table to the query. In the SQL
statement for this query, wrap the Nz function around each joining clause:
tablename LEFT JOIN queryname ON Nz(tablename.field1,"") =
Nz(queryname.field1, "") AND Nz(tablename.field2, "") = Nz(queryname.field2,
"") AND ....

This will cancel out Null values that would otherwise cause nonmatching of
records.

Then the two unique fields from the query onto the design grid; set their
"Criteria:" expression to Is Null. Then put the two unique fields from the
table on the query grid.

When this query returns one or more records, the two objects' data do not
match. If the query returns zero records, they match.
--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:
Hey now that you mention it, there's isn't a unique field
but there are two fields (ID1 and ID2) that when you
combine them make each record unique.
ID1 is the number of the general system and ID2 is the
individual part (e.g ID1=02315 ID2=05 is given to a
specific valve on a unique system)
Every record will contain the two "ID" fields, however, in
any record there may be up to seven fields can be NULL.
So would the comparison query approach work(i.e. 100%
success)?
The idea for comparing is cause it's different, it'll
email the newest collection to the appropriate person and
then the nextday it'll use the newest data as a basis for
comparison (that way it doesn't send the same email
everyday)

Thanks a lot Ken!
Hafeez Esmail

-----Original Message-----
That is a tall order! And one that may not be a guaranteed 100% success,
depending upon how much must match.

All of the data.... each data value in each field in each record? How will
you sort the records so that you can compare them easily?

If we can identify some unique fields and values, we might create a
comparison query that could tell you if they are the same. However, if any
record's field has a Null value in it, this type of query will not find a
"match" even if both queries have the same Null value in the same field in
the same record.

Any particular reason you need to store the data in a table? Will the query
not be able to reproduce the data when you call it again? How about storing
the results each time instead of just the most recent?

--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:

The data. If the records are different, then replace all
of the qryA data with all of the qryTemp data.

Thanks
-----Original Message-----
How are you planning to compare the two queries? The data
contents? The SQL
statement?

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

I have made qryA. I'd like to compare qryTmp in my code
to qryA and if there is a discrepancy, replace qryA
(i.e.
save qryTemp as qryA)
Is this possible?

Once again Thanks for all your help! There are so many
keywords I don't even know where to start looking!

Hafeez Esmail

-----Original Message-----
You want to save the results of the query that is being
displayed in the
report? If yes, the DoCmd.Save command will not do this
for you. That
command is used to save changes made to the design view
of an object. It
won't save the displayed data.

You need to use a MakeTable query that is based on the
report's query to put
the data into a table so that you can save it.
Alternatively, you can "save"
the report by using the OutputTo command to save the
report in an ".rtf"
format. You wouldn't need to open the report first;
just
be sure that the
report's query is set up and then use this command:
DoCmd.OutputTo acReport, "ReportName",
acFormatRTF,
"C:\MyFolder\ReportName.rtf"

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for the confirmation
I seem to be having a problem with this code now
DoCmd.Save acReport, "RptPredef" & strLastName

I want to save the resulting search (i don't care
which
format it's in) but I can't seem to be able to do it.

Thanks in advance
Hafeez Esmail

-----Original Message-----
Yes, you can open more than one recordset in a code
snippet.

You don't need the Where word in the line of code.
Change
it to this:
rstrec!Last_Name = strLastName

You correctly identified that you needed to set a
reference to DAO library.
--
Ken Snell
<MS ACCESS MVP>

I've set the references to DA 3.6 (I wasn't in
Design
Mode) but the error I'm having now is at my Where
condition

(Where rstrec!Last_Name = strLastName)

Public Function MakeRpts()

Dim rstrec As DAO.Recordset
Dim rstname As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

Set dbs = CurrentDb()
Set rstname = dbs.OpenRecordset ("TblFHStaffList")
Set rstrec = dbs.OpenRecordset("QryPredef120")
If rstname.BOF = False And rstname.EOF = False
Then
rstname.MoveFirst
Do While rstname.EOF = False
Set strLastName = rstname!Last_Name
If rstrec.BOF = False And rstrec.EOF =
False
Then
rstrec.MoveFirst
Do While rstrec.EOF = False
Where rstrec!Last_Name =
strLastName
rstrec.MoveNext
Loop
DoCmd.Save acReport, "RptPredef" &
strLastName
End If
rst.MoveNext
Loop
rstname.Close
Set rstname = Nothing
rstrec.Close
Set rstrec = Nothing
dbs.Close
Set dbs = Nothing
End Function

-----Original Message-----
Error in my first example for case one...sorry:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last
name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName
Then
DoCmd.SendObject ' add the
appropriate
arguments to this line
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
--
Ken Snell
<MS ACCESS MVP>

"Ken Snell"

Click to expand...

wrote
in
message
RE: case one, you cannot use a macro. You will
need
to
use VBA code to
open
a recordset based on the table and then to
loop
through
the recordset. I'm
not sure what you mean by the query is using a
parameter based on the
person's last name? If you have a basic query
(no
parameter), you don't
need
to run it in order to email it; just email the
query
(SendObject) and it
will run and export its results. Something
like
this:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's
last
name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName
Then
DoCmd.SendObject ' add the
appropriate
arguments to this
line
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

If what you desire is to use each person's
last
name
as
the parameter for
the query and to email each "filtered" query
to
just
that one person, I
don't have a ready answer for you at the
moment.
I
would anticipate that
you'd need to use the QueryDefs collection of
the
Database object in order
to get the query and evaluate the parameter
using
each
last name from the
table, and then likely you'd need to have that
query
export its results
into
a temporary table, and then email that table;
then
clear the table and go
to
the next record in the table until all done.
I've
not
worked with this
before, so I'm not sure of all the nuances and
will
need a bit of time to
try a few things before I can post a possible
solution
for you.

I'm tied up on some personal business at the
moment,
so
it will be a few
days before I can post a possible idea.

--
Ken Snell
<MS ACCESS MVP>
[email protected]...
Hi Ken,

I want to do something similar to this and
have
thought of
two ways to carry it (neither of which I
know
how
to
do).

Case one (ideal solution):
I have a query based on a paramter (i.e.
Last_Name).
The
following tasks is what I want it to do
automatically:
1 Run query
2 Enter employee's last name based on a
table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need to be
executed
and
emailed to each employee respectively. Is
there
an
easy
way to do this?

Hafeez Esmail

-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1!txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst!
[email2]
& ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

message

Hey, in the code below, access uses one
e-
mailadress at
the time. What is
the code to send all the adresses in one
time
to
the
Emailadres ? (now, if
i
have 100 adresses, so I need to create
the
text
100
times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1! txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

.

.

.

.

.

.

Click to expand...

.

Click to expand...

Hafeez Esmail · Oct 14, 2003

This is it!
You've done it! I would never have known do any of this!
In the future, how would I know what functions are
available, what they do and how to use them?
Thanks a lot Ken!!

Hafeez Esmail

Hafeez Esmail said:
-----Original Message-----
OK - here is a type of query that you can use to see if the new query's
records aren't equal to the old query's records (the ones stored in a table,
right?).

Essentially, you create a query that is based on the new query and the
table. Join every field from the table to its counterpart field in the
query. Make each join a LEFT JOIN from the table to the query. In the SQL
statement for this query, wrap the Nz function around each joining clause:
tablename LEFT JOIN queryname ON Nz (tablename.field1,"") =
Nz(queryname.field1, "") AND Nz(tablename.field2, "") = Nz (queryname.field2,
"") AND ....

This will cancel out Null values that would otherwise cause nonmatching of
records.

Then the two unique fields from the query onto the design grid; set their
"Criteria:" expression to Is Null. Then put the two unique fields from the
table on the query grid.

When this query returns one or more records, the two objects' data do not
match. If the query returns zero records, they match.
--
Ken Snell
<MS ACCESS MVP>

Hafeez Esmail said:

Hey now that you mention it, there's isn't a unique field
but there are two fields (ID1 and ID2) that when you
combine them make each record unique.
ID1 is the number of the general system and ID2 is the
individual part (e.g ID1=02315 ID2=05 is given to a
specific valve on a unique system)
Every record will contain the two "ID" fields, however, in
any record there may be up to seven fields can be NULL.
So would the comparison query approach work(i.e. 100%
success)?
The idea for comparing is cause it's different, it'll
email the newest collection to the appropriate person and
then the nextday it'll use the newest data as a basis for
comparison (that way it doesn't send the same email
everyday)

Thanks a lot Ken!
Hafeez Esmail

-----Original Message-----
That is a tall order! And one that may not be a guaranteed 100% success,
depending upon how much must match.

All of the data.... each data value in each field in

Click to expand...

each
record? How will

you sort the records so that you can compare them easily?

If we can identify some unique fields and values, we might create a
comparison query that could tell you if they are the same. However, if any
record's field has a Null value in it, this type of

Click to expand...

query
will not find a

"match" even if both queries have the same Null value

Click to expand...

in
the same field in

the same record.

Any particular reason you need to store the data in a table? Will the query
not be able to reproduce the data when you call it

Click to expand...

again?
How about storing

the results each time instead of just the most recent?

--
Ken Snell
<MS ACCESS MVP>

The data. If the records are different, then replace all
of the qryA data with all of the qryTemp data.

Thanks
-----Original Message-----
How are you planning to compare the two queries? The data
contents? The SQL
statement?

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

I have made qryA. I'd like to compare qryTmp in

Click to expand...

my
code

to qryA and if there is a discrepancy, replace qryA
(i.e.
save qryTemp as qryA)
Is this possible?

Once again Thanks for all your help! There are so many
keywords I don't even know where to start looking!

Hafeez Esmail

-----Original Message-----
You want to save the results of the query that is being
displayed in the
report? If yes, the DoCmd.Save command will not

Click to expand...

do
this

for you. That
command is used to save changes made to the

Click to expand...

design
view

of an object. It
won't save the displayed data.

You need to use a MakeTable query that is based

Click to expand...

on
the

report's query to put
the data into a table so that you can save it.
Alternatively, you can "save"
the report by using the OutputTo command to save the
report in an ".rtf"
format. You wouldn't need to open the report first;
just
be sure that the
report's query is set up and then use this command:
DoCmd.OutputTo acReport, "ReportName",
acFormatRTF,
"C:\MyFolder\ReportName.rtf"

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for the confirmation
I seem to be having a problem with this code now
DoCmd.Save acReport, "RptPredef" & strLastName

I want to save the resulting search (i don't care
which
format it's in) but I can't seem to be able to

Click to expand...

do
it.

Thanks in advance
Hafeez Esmail

-----Original Message-----
Yes, you can open more than one recordset in a code
snippet.

You don't need the Where word in the line of code.
Change
it to this:
rstrec!Last_Name = strLastName

You correctly identified that you needed to set a
reference to DAO library.
--
Ken Snell
<MS ACCESS MVP>

I've set the references to DA 3.6 (I wasn't in
Design
Mode) but the error I'm having now is at my Where
condition

(Where rstrec!Last_Name = strLastName)

Public Function MakeRpts()

Dim rstrec As DAO.Recordset
Dim rstname As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

Set dbs = CurrentDb()
Set rstname = dbs.OpenRecordset ("TblFHStaffList")
Set rstrec = dbs.OpenRecordset ("QryPredef120")
If rstname.BOF = False And rstname.EOF = False
Then
rstname.MoveFirst
Do While rstname.EOF = False
Set strLastName = rstname!Last_Name
If rstrec.BOF = False And rstrec.EOF =
False
Then
rstrec.MoveFirst
Do While rstrec.EOF = False
Where rstrec!Last_Name =
strLastName
rstrec.MoveNext
Loop
DoCmd.Save acReport, "RptPredef" &
strLastName
End If
rst.MoveNext
Loop
rstname.Close
Set rstname = Nothing
rstrec.Close
Set rstrec = Nothing
dbs.Close
Set dbs = Nothing
End Function

-----Original Message-----
Error in my first example for case one...sorry:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's last
name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName
Then
DoCmd.SendObject ' add the
appropriate
arguments to this line
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
--
Ken Snell
<MS ACCESS MVP>

"Ken Snell"
RE: case one, you cannot use a macro.

Click to expand...

You
will

need
to
use VBA code to
open
a recordset based on the table and then to
loop
through
the recordset. I'm
not sure what you mean by the query is using a
parameter based on the
person's last name? If you have a basic query
(no
parameter), you don't
need
to run it in order to email it; just

Click to expand...

email
the

query
(SendObject) and it
will run and export its results. Something
like
this:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strLastName As String

strLastName = InputBox("Enter the person's
last
name:")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
("TableNameWithPerson'sLastName")
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
If rst!LastNameFieldName = strLastName
Then
DoCmd.SendObject ' add the
appropriate
arguments to this
line
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

If what you desire is to use each person's
last
name
as
the parameter for
the query and to email each "filtered" query
to
just
that one person, I
don't have a ready answer for you at the
moment.
I
would anticipate that
you'd need to use the QueryDefs

Click to expand...

collection
of

the
Database object in order
to get the query and evaluate the parameter
using
each
last name from the
table, and then likely you'd need to

Click to expand...

have
that

query
export its results
into
a temporary table, and then email that table;
then
clear the table and go
to
the next record in the table until all done.
I've
not
worked with this
before, so I'm not sure of all the

Click to expand...

nuances
and

will
need a bit of time to
try a few things before I can post a possible
solution
for you.

I'm tied up on some personal business at the
moment,
so
it will be a few
days before I can post a possible idea.

--
Ken Snell
<MS ACCESS MVP>
[email protected]...
Hi Ken,

I want to do something similar to this and
have
thought of
two ways to carry it (neither of which I
know
how
to
do).

Case one (ideal solution):
I have a query based on a paramter (i.e.
Last_Name).
The
following tasks is what I want it to do
automatically:
1 Run query
2 Enter employee's last name based on a
table
3 Email query to employee
*loop through all names in table*

Case two (easier??):
I have 50 different queries that need

Click to expand...

to
be

executed
and
emailed to each employee respectively. Is
there
an
easy
way to do this?

Hafeez Esmail

-----Original Message-----
Try this:

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strEmailadress = ""
strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1! txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = strEmailadress & rst!
[email2]
& ";"
rst.MoveNext
Loop
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Ken Snell
<MS ACCESS MVP>

"Van Eenoo Stefaan" <[email protected]>

Click to expand...

wrote
in

message

Hey, in the code below, access uses one
e-
mailadress at
the time. What is
the code to send all the adresses

Click to expand...

in
one

time
to
the
Emailadres ? (now, if
i
have 100 adresses, so I need to create
the
text
100
times and press the
"send button 100 time)
Any help is welcome.

this is my code
Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String

strSubject = Forms!form1!txtSubject
strEmailmessage = Forms!form1! txtMessage
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("mail")
rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![email2]
DoCmd.SendObject , , acFormatHTML,
strEmailadress, , ,
strSubject,
strEmailmessage, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

.

.

.

.

.

.

.

Click to expand...

Click to expand...

.

Ken Snell · Oct 15, 2003

Glad to hear of your success!

There's not a single, one-stop place for seeing all the functions, but the
best place to find them is to open the Visual Basic Editor, open the Object
Browser (View | Object Browser) and select VBA as the library. You'll find
many functions (under the general headings such as Strings, Math, etc.).

Once you find a function that looks interesting, then look it up in Help.