Help with Microsoft help on linking Access to Outlook

[JohnB]

Hi.

Is anyone willing to start a dialogue with me on the following Microsoft
help page? It details a method of automatically populating the Outlook 'To'
window with email addresses contained in an Access table.

http://support.microsoft.com/?id=318881#appliesto

To start, I have a three questions and would be grateful if someone would
help me along.

The article provides code for a Module but I don't know how this is used.
i.e. how do I call up this code - from the 'on click' event of a command
button on the form described? How exactly - what is the Module name? As you
can tell, I'm not too hot on the use of modules/macros.

Next, I don't understand what is meant by step 11 in the instructions. I
don't want to send attachments so I would enter just 'SendMessages' in the
debug window, but why? - what happens when I press enter? Is this meant to
test the procedure before building it into a command buttons code? If so,
we're back to my first question.

Once I get the code to work, I then need to know how to apply it to my own
particular circumstances. I will be providing my users with a form bound to
table tblSchools. This table lists School details, including Email address.
They will use the form to filter down to a set of records, e.g all schools in
a certain Town and I want this code (say, on a command button on the form) to
transfer only those email addresses in the filtered set to Outlook, rather
than all records in tblSchools. How could this be done?

Thanks in advance for any help.

 

[Douglas J. Steele]

The article provides code for a Module but I don't know how this is used.
i.e. how do I call up this code - from the 'on click' event of a command
button on the form described? How exactly - what is the Module name? As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated with a form
or report) and copy all of the code from step 7 into that new module. What
you name the module when you save it is irrelevant (as long as you don't
name it SendMessages, since modules cannot have the same name as functions
or subs contained within them)

In your "on click" event (or however you want to invoke it), you'd put a
line of code:

Call SendMessages

or

Call SendMessages("C:\TestAttachment.txt")

I disagree stylistically with what they're saying in step 11: I think it's
better to use the Call keyword, but the two lines above are equivalent to
just using

SendMessages

or

SendMessages "C:\TestAttachment.txt"

(Note that you must enclose the parameter in parentheses when you use the
Call keyword, and you don't when not using Call)

Next, I don't understand what is meant by step 11 in the instructions. I
don't want to send attachments so I would enter just 'SendMessages' in the
debug window, but why? - what happens when I press enter?

Pressing Enter in the debug window means that what's been typed preceding
the Enter gets executed.

Is this meant to test the procedure before building it into a command
buttons code?
Yes.

Once I get the code to work, I then need to know how to apply it to my own
particular circumstances. I will be providing my users with a form bound
to
table tblSchools. This table lists School details, including Email
address.
They will use the form to filter down to a set of records, e.g all schools
in
a certain Town and I want this code (say, on a command button on the form)
to
transfer only those email addresses in the filtered set to Outlook, rather
than all records in tblSchools. How could this be done?

First, let me tell you about a fairly serious omission in the sample if
you're using Access 2000 or 2002. The code is using DAO as the method of
getting the data from the table, but by default Access 2000 and 2002 do not
contain a reference to the DAO library. In section 9, where it's talking
about setting references, you need to ensure that you also have a reference
set to the Microsoft DAO 3.6 Object Library. As well, you need to change the
line of code

Dim MyRS As Recordset

to

Dim MyRS As DAO.Recordset

(You could also change Dim MyDB As Database to Dim MyDB As DAO.Database if
you want, but that's not absolutely necessary)

To customize the code to your specific situation, you need to change

Set MyRS = MyDB.OpenRecordset("tblMailingList")

to

Set MyRS = MyDB.OpenRecordset("tblSchool")

If the e-mail address in tblSchool isn't named EmailAddress, you also need
to change EmailAddress to the actual field name in this line of code:

TheAddress = MyRS![EmailAddress]

Now, this code is going to send email to every entry in the table. To limit
to whom the e-mail gets send, you'll need to create a query that returns
only those schools to whom you wish to e-mail, use that query name rather
than tblSchool in the OpenRecordset command. Alternatively, you could create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

 

[JohnB]

Thank you, Doug, for providing such a comprehensive reply and for the
additional crutial info.

I just want to focus on your last point, where you said:

"Now, this code is going to send email to every entry in the table. To limit
to whom the e-mail gets send, you'll need to create a query that returns
only those schools to whom you wish to e-mail, use that query name rather
than tblSchool in the OpenRecordset command. Alternatively, you could create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)"

The thing is, I want to give my users the ability to choose the group of
Schools to whom an Email is sent, dynamically. By that I mean they open a
form which initially shows all School records, then they filter the recordset
to, say, only those Schools in a particular town, then they would click on a
command button that calls up the routine to run using the filtered set of
records. The next time, they might choose to filter down to those in a
different town, or using a different selection criteria all together. Your
suggestions, if I understand them correctly, would assume that the set of
Schools was fixed and predetermined.

To look at it another way, where a particular line of code in the routine
reads:

Set MyRS = MyDB.OpenRecordset("tblMailingList")

it would instead say something like:

Set MyRS = MyDB.OpenRecordset("Something here that points to the forms
currently filtered recordset")

I hope you get the idea.

Again, thanks for taking the time to produce such useful answers.

The article provides code for a Module but I don't know how this is used.
i.e. how do I call up this code - from the 'on click' event of a command
button on the form described? How exactly - what is the Module name? As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated with a form
or report) and copy all of the code from step 7 into that new module. What
you name the module when you save it is irrelevant (as long as you don't
name it SendMessages, since modules cannot have the same name as functions
or subs contained within them)

In your "on click" event (or however you want to invoke it), you'd put a
line of code:

Call SendMessages

or

Call SendMessages("C:\TestAttachment.txt")

I disagree stylistically with what they're saying in step 11: I think it's
better to use the Call keyword, but the two lines above are equivalent to
just using

SendMessages

or

SendMessages "C:\TestAttachment.txt"

(Note that you must enclose the parameter in parentheses when you use the
Call keyword, and you don't when not using Call)

Next, I don't understand what is meant by step 11 in the instructions. I
don't want to send attachments so I would enter just 'SendMessages' in the
debug window, but why? - what happens when I press enter?

Pressing Enter in the debug window means that what's been typed preceding
the Enter gets executed.

Is this meant to test the procedure before building it into a command
buttons code?
Yes.

Once I get the code to work, I then need to know how to apply it to my own
particular circumstances. I will be providing my users with a form bound
to
table tblSchools. This table lists School details, including Email
address.
They will use the form to filter down to a set of records, e.g all schools
in
a certain Town and I want this code (say, on a command button on the form)
to
transfer only those email addresses in the filtered set to Outlook, rather
than all records in tblSchools. How could this be done?

First, let me tell you about a fairly serious omission in the sample if
you're using Access 2000 or 2002. The code is using DAO as the method of
getting the data from the table, but by default Access 2000 and 2002 do not
contain a reference to the DAO library. In section 9, where it's talking
about setting references, you need to ensure that you also have a reference
set to the Microsoft DAO 3.6 Object Library. As well, you need to change the
line of code

Dim MyRS As Recordset

to

Dim MyRS As DAO.Recordset

(You could also change Dim MyDB As Database to Dim MyDB As DAO.Database if
you want, but that's not absolutely necessary)

To customize the code to your specific situation, you need to change

Set MyRS = MyDB.OpenRecordset("tblMailingList")

to

Set MyRS = MyDB.OpenRecordset("tblSchool")

If the e-mail address in tblSchool isn't named EmailAddress, you also need
to change EmailAddress to the actual field name in this line of code:

TheAddress = MyRS![EmailAddress]

Now, this code is going to send email to every entry in the table. To limit
to whom the e-mail gets send, you'll need to create a query that returns
only those schools to whom you wish to e-mail, use that query name rather
than tblSchool in the OpenRecordset command. Alternatively, you could create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

 

[JohnB]

Hi again Doug.

Sorry to bombard you with replies but since writing my other one I've done
some Googling and found some info on an object called RecordSetClone. I can't
understand how I would build this into the routine but it looks like it would
do the job of ensuring that the routine runs on the filtered set of records.
Is this any help?

Thanks again.

The article provides code for a Module but I don't know how this is used.
i.e. how do I call up this code - from the 'on click' event of a command
button on the form described? How exactly - what is the Module name? As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated with a form
or report) and copy all of the code from step 7 into that new module. What
you name the module when you save it is irrelevant (as long as you don't
name it SendMessages, since modules cannot have the same name as functions
or subs contained within them)

In your "on click" event (or however you want to invoke it), you'd put a
line of code:

Call SendMessages

or

Call SendMessages("C:\TestAttachment.txt")

I disagree stylistically with what they're saying in step 11: I think it's
better to use the Call keyword, but the two lines above are equivalent to
just using

SendMessages

or

SendMessages "C:\TestAttachment.txt"

(Note that you must enclose the parameter in parentheses when you use the
Call keyword, and you don't when not using Call)

Next, I don't understand what is meant by step 11 in the instructions. I
don't want to send attachments so I would enter just 'SendMessages' in the
debug window, but why? - what happens when I press enter?

Pressing Enter in the debug window means that what's been typed preceding
the Enter gets executed.

Is this meant to test the procedure before building it into a command
buttons code?
Yes.

Once I get the code to work, I then need to know how to apply it to my own
particular circumstances. I will be providing my users with a form bound
to
table tblSchools. This table lists School details, including Email
address.
They will use the form to filter down to a set of records, e.g all schools
in
a certain Town and I want this code (say, on a command button on the form)
to
transfer only those email addresses in the filtered set to Outlook, rather
than all records in tblSchools. How could this be done?

First, let me tell you about a fairly serious omission in the sample if
you're using Access 2000 or 2002. The code is using DAO as the method of
getting the data from the table, but by default Access 2000 and 2002 do not
contain a reference to the DAO library. In section 9, where it's talking
about setting references, you need to ensure that you also have a reference
set to the Microsoft DAO 3.6 Object Library. As well, you need to change the
line of code

Dim MyRS As Recordset

to

Dim MyRS As DAO.Recordset

(You could also change Dim MyDB As Database to Dim MyDB As DAO.Database if
you want, but that's not absolutely necessary)

To customize the code to your specific situation, you need to change

Set MyRS = MyDB.OpenRecordset("tblMailingList")

to

Set MyRS = MyDB.OpenRecordset("tblSchool")

If the e-mail address in tblSchool isn't named EmailAddress, you also need
to change EmailAddress to the actual field name in this line of code:

TheAddress = MyRS![EmailAddress]

Now, this code is going to send email to every entry in the table. To limit
to whom the e-mail gets send, you'll need to create a query that returns
only those schools to whom you wish to e-mail, use that query name rather
than tblSchool in the OpenRecordset command. Alternatively, you could create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

 

[Douglas J. Steele]

You don't need RecordSetClone for what you're doing.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi again Doug.

Sorry to bombard you with replies but since writing my other one I've done
some Googling and found some info on an object called RecordSetClone. I
can't
understand how I would build this into the routine but it looks like it
would
do the job of ensuring that the routine runs on the filtered set of
records.
Is this any help?

Thanks again.

The article provides code for a Module but I don't know how this is
used.
i.e. how do I call up this code - from the 'on click' event of a
command
button on the form described? How exactly - what is the Module name? As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated with a
form
or report) and copy all of the code from step 7 into that new module.
What
you name the module when you save it is irrelevant (as long as you don't
name it SendMessages, since modules cannot have the same name as
functions
or subs contained within them)

In your "on click" event (or however you want to invoke it), you'd put a
line of code:

Call SendMessages

or

Call SendMessages("C:\TestAttachment.txt")

I disagree stylistically with what they're saying in step 11: I think
it's
better to use the Call keyword, but the two lines above are equivalent to
just using

SendMessages

or

SendMessages "C:\TestAttachment.txt"

(Note that you must enclose the parameter in parentheses when you use the
Call keyword, and you don't when not using Call)

Next, I don't understand what is meant by step 11 in the instructions.
I
don't want to send attachments so I would enter just 'SendMessages' in
the
debug window, but why? - what happens when I press enter?

Pressing Enter in the debug window means that what's been typed preceding
the Enter gets executed.

Is this meant to test the procedure before building it into a command
buttons code?
Yes.

Once I get the code to work, I then need to know how to apply it to my
own
particular circumstances. I will be providing my users with a form
bound
to
table tblSchools. This table lists School details, including Email
address.
They will use the form to filter down to a set of records, e.g all
schools
in
a certain Town and I want this code (say, on a command button on the
form)
to
transfer only those email addresses in the filtered set to Outlook,
rather
than all records in tblSchools. How could this be done?

First, let me tell you about a fairly serious omission in the sample if
you're using Access 2000 or 2002. The code is using DAO as the method of
getting the data from the table, but by default Access 2000 and 2002 do
not
contain a reference to the DAO library. In section 9, where it's talking
about setting references, you need to ensure that you also have a
reference
set to the Microsoft DAO 3.6 Object Library. As well, you need to change
the
line of code

Dim MyRS As Recordset

to

Dim MyRS As DAO.Recordset

(You could also change Dim MyDB As Database to Dim MyDB As DAO.Database
if
you want, but that's not absolutely necessary)

To customize the code to your specific situation, you need to change

Set MyRS = MyDB.OpenRecordset("tblMailingList")

to

Set MyRS = MyDB.OpenRecordset("tblSchool")

If the e-mail address in tblSchool isn't named EmailAddress, you also
need
to change EmailAddress to the actual field name in this line of code:

TheAddress = MyRS![EmailAddress]

Now, this code is going to send email to every entry in the table. To
limit
to whom the e-mail gets send, you'll need to create a query that returns
only those schools to whom you wish to e-mail, use that query name rather
than tblSchool in the OpenRecordset command. Alternatively, you could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

 

[Douglas J. Steele]

Sounds as though you may need to do a bit of homework into how to create
queries that accept parameters from the user.

If you had a parameter-driven query, that's what you'd use when opening your
recordset. The SQL string I gave was a sample of how you can dynamically
create SQL statements. The assumption was that you'd pass some additional
parameters to the function, indicating how to build the WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you, Doug, for providing such a comprehensive reply and for the
additional crutial info.

I just want to focus on your last point, where you said:

"Now, this code is going to send email to every entry in the table. To
limit
to whom the e-mail gets send, you'll need to create a query that returns
only those schools to whom you wish to e-mail, use that query name rather
than tblSchool in the OpenRecordset command. Alternatively, you could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)"

The thing is, I want to give my users the ability to choose the group of
Schools to whom an Email is sent, dynamically. By that I mean they open a
form which initially shows all School records, then they filter the
recordset
to, say, only those Schools in a particular town, then they would click on
a
command button that calls up the routine to run using the filtered set of
records. The next time, they might choose to filter down to those in a
different town, or using a different selection criteria all together. Your
suggestions, if I understand them correctly, would assume that the set of
Schools was fixed and predetermined.

To look at it another way, where a particular line of code in the routine
reads:

Set MyRS = MyDB.OpenRecordset("tblMailingList")

it would instead say something like:

Set MyRS = MyDB.OpenRecordset("Something here that points to the forms
currently filtered recordset")

I hope you get the idea.

Again, thanks for taking the time to produce such useful answers.

The article provides code for a Module but I don't know how this is
used.
i.e. how do I call up this code - from the 'on click' event of a
command
button on the form described? How exactly - what is the Module name? As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated with a
form
or report) and copy all of the code from step 7 into that new module.
What
you name the module when you save it is irrelevant (as long as you don't
name it SendMessages, since modules cannot have the same name as
functions
or subs contained within them)

In your "on click" event (or however you want to invoke it), you'd put a
line of code:

Call SendMessages

or

Call SendMessages("C:\TestAttachment.txt")

I disagree stylistically with what they're saying in step 11: I think
it's
better to use the Call keyword, but the two lines above are equivalent to
just using

SendMessages

or

SendMessages "C:\TestAttachment.txt"

(Note that you must enclose the parameter in parentheses when you use the
Call keyword, and you don't when not using Call)

Next, I don't understand what is meant by step 11 in the instructions.
I
don't want to send attachments so I would enter just 'SendMessages' in
the
debug window, but why? - what happens when I press enter?

Pressing Enter in the debug window means that what's been typed preceding
the Enter gets executed.

Is this meant to test the procedure before building it into a command
buttons code?
Yes.

Once I get the code to work, I then need to know how to apply it to my
own
particular circumstances. I will be providing my users with a form
bound
to
table tblSchools. This table lists School details, including Email
address.
They will use the form to filter down to a set of records, e.g all
schools
in
a certain Town and I want this code (say, on a command button on the
form)
to
transfer only those email addresses in the filtered set to Outlook,
rather
than all records in tblSchools. How could this be done?

First, let me tell you about a fairly serious omission in the sample if
you're using Access 2000 or 2002. The code is using DAO as the method of
getting the data from the table, but by default Access 2000 and 2002 do
not
contain a reference to the DAO library. In section 9, where it's talking
about setting references, you need to ensure that you also have a
reference
set to the Microsoft DAO 3.6 Object Library. As well, you need to change
the
line of code

Dim MyRS As Recordset

to

Dim MyRS As DAO.Recordset

(You could also change Dim MyDB As Database to Dim MyDB As DAO.Database
if
you want, but that's not absolutely necessary)

To customize the code to your specific situation, you need to change

Set MyRS = MyDB.OpenRecordset("tblMailingList")

to

Set MyRS = MyDB.OpenRecordset("tblSchool")

If the e-mail address in tblSchool isn't named EmailAddress, you also
need
to change EmailAddress to the actual field name in this line of code:

TheAddress = MyRS![EmailAddress]

Now, this code is going to send email to every entry in the table. To
limit
to whom the e-mail gets send, you'll need to create a query that returns
only those schools to whom you wish to e-mail, use that query name rather
than tblSchool in the OpenRecordset command. Alternatively, you could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

 

[JohnB]

Hi Doug.

I read your reply earlier but gave myself some time to digest all you'd said
before I got back.

You said: "If you had a parameter-driven query, that's what you'd use when
opening your recordset. The SQL string I gave was a sample of how you can
dynamically create SQL statements. The assumption was that you'd pass some
additional parameters to the function, indicating how to build the WHERE
clause."

meaning your eariler code:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

I didn't realise that this was meant to be dynamic. I thought you meant I
should build the SQL, using a query, then paste it into your code. I thought
that it would not suit my needs because it would be fixed and would select
the same filtered recordset every time. Sorry - I should have realised you
didn't mean that. So, how would the stSQL be generated dynamically by the
user filtering down a recordset?

BTW, I'm fairly familiar with parameters in queries but I've only ever used
them in a simplistic way when producing reports. I've found them fairy crude,
as I can only present the user with the ability to specify one criteria at a
time and they have to remember to enter the exact field contents every time.
I guess there is a way to present the user with a combo box to select entries
from but I don't know how to do that. Even then, the user wouldn't get to see
the filtered recordset before the routine would run, hence my idea of using a
normal form to allow the normal filtering process and viewing of the results
before clicking on a command button to pass the filtered set to the routine.

So at the moment, I have two options:

1. build a simple query with a parameter for the field Town, then use that
query as the record source for the routine. That would present a fixed
question ( e.g. "What Town Name?" ) to the user every time. Actually, that
would suit my needs at the present, although they wouldn't be able to view
the resultant filtered set before Outlook opens. Still, it would do.

2. try to fully understand what you mean by dynamically producing the SQL to
act as the record source, because presumably that would allow the users to
choose themselves what fields to filter on. Much more useful. And maybe this
option would allow them to view the filtered recordset and perhaps
correct/redo it before it is passed to the routine?

So, to repeat the question - how would the stSQL be generated dynamically by
the user filtering down a recordset?

Hopefully I'm understanding you correctly now. Many thanks, again, for the
help.

Sounds as though you may need to do a bit of homework into how to create
queries that accept parameters from the user.

If you had a parameter-driven query, that's what you'd use when opening your
recordset. The SQL string I gave was a sample of how you can dynamically
create SQL statements. The assumption was that you'd pass some additional
parameters to the function, indicating how to build the WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you, Doug, for providing such a comprehensive reply and for the
additional crutial info.

I just want to focus on your last point, where you said:

"Now, this code is going to send email to every entry in the table. To
limit
to whom the e-mail gets send, you'll need to create a query that returns
only those schools to whom you wish to e-mail, use that query name rather
than tblSchool in the OpenRecordset command. Alternatively, you could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)"

The thing is, I want to give my users the ability to choose the group of
Schools to whom an Email is sent, dynamically. By that I mean they open a
form which initially shows all School records, then they filter the
recordset
to, say, only those Schools in a particular town, then they would click on
a
command button that calls up the routine to run using the filtered set of
records. The next time, they might choose to filter down to those in a
different town, or using a different selection criteria all together. Your
suggestions, if I understand them correctly, would assume that the set of
Schools was fixed and predetermined.

To look at it another way, where a particular line of code in the routine
reads:

Set MyRS = MyDB.OpenRecordset("tblMailingList")

it would instead say something like:

Set MyRS = MyDB.OpenRecordset("Something here that points to the forms
currently filtered recordset")

I hope you get the idea.

Again, thanks for taking the time to produce such useful answers.

The article provides code for a Module but I don't know how this is
used.
i.e. how do I call up this code - from the 'on click' event of a
command
button on the form described? How exactly - what is the Module name? As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated with a
form
or report) and copy all of the code from step 7 into that new module.
What
you name the module when you save it is irrelevant (as long as you don't
name it SendMessages, since modules cannot have the same name as
functions
or subs contained within them)

In your "on click" event (or however you want to invoke it), you'd put a
line of code:

Call SendMessages

or

Call SendMessages("C:\TestAttachment.txt")

I disagree stylistically with what they're saying in step 11: I think
it's
better to use the Call keyword, but the two lines above are equivalent to
just using

SendMessages

or

SendMessages "C:\TestAttachment.txt"

(Note that you must enclose the parameter in parentheses when you use the
Call keyword, and you don't when not using Call)

Next, I don't understand what is meant by step 11 in the instructions.
I
don't want to send attachments so I would enter just 'SendMessages' in
the
debug window, but why? - what happens when I press enter?

Pressing Enter in the debug window means that what's been typed preceding
the Enter gets executed.

Is this meant to test the procedure before building it into a command
buttons code?

Yes.

Once I get the code to work, I then need to know how to apply it to my
own
particular circumstances. I will be providing my users with a form
bound
to
table tblSchools. This table lists School details, including Email
address.
They will use the form to filter down to a set of records, e.g all
schools
in
a certain Town and I want this code (say, on a command button on the
form)
to
transfer only those email addresses in the filtered set to Outlook,
rather
than all records in tblSchools. How could this be done?

First, let me tell you about a fairly serious omission in the sample if
you're using Access 2000 or 2002. The code is using DAO as the method of
getting the data from the table, but by default Access 2000 and 2002 do
not
contain a reference to the DAO library. In section 9, where it's talking
about setting references, you need to ensure that you also have a
reference
set to the Microsoft DAO 3.6 Object Library. As well, you need to change
the
line of code

Dim MyRS As Recordset

to

Dim MyRS As DAO.Recordset

(You could also change Dim MyDB As Database to Dim MyDB As DAO.Database
if
you want, but that's not absolutely necessary)

To customize the code to your specific situation, you need to change

Set MyRS = MyDB.OpenRecordset("tblMailingList")

to

Set MyRS = MyDB.OpenRecordset("tblSchool")

If the e-mail address in tblSchool isn't named EmailAddress, you also
need
to change EmailAddress to the actual field name in this line of code:

TheAddress = MyRS![EmailAddress]

Now, this code is going to send email to every entry in the table. To
limit
to whom the e-mail gets send, you'll need to create a query that returns
only those schools to whom you wish to e-mail, use that query name rather
than tblSchool in the OpenRecordset command. Alternatively, you could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

 

[Douglas J. Steele]

A simplistic approach is to create a form that has a combo box listing all
of the towns, and have the query point to the combo box to get its value. If
you want to get a little more sophisticated, you could use a list box, so
that they can multiselect towns. However, that requires that you build the
WHERE clause in code (see http://www.mvps.org/access/forms/frm0007.htm at
"The Access Web" for an example of how you do that) To see something even
more sophisticated, take a look at the Query By Form example Duane Hookom
has at http://www.access.hookom.net/Samples.htm

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug.

I read your reply earlier but gave myself some time to digest all you'd
said
before I got back.

You said: "If you had a parameter-driven query, that's what you'd use when
opening your recordset. The SQL string I gave was a sample of how you can
dynamically create SQL statements. The assumption was that you'd pass some
additional parameters to the function, indicating how to build the WHERE
clause."

meaning your eariler code:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

I didn't realise that this was meant to be dynamic. I thought you meant I
should build the SQL, using a query, then paste it into your code. I
thought
that it would not suit my needs because it would be fixed and would select
the same filtered recordset every time. Sorry - I should have realised you
didn't mean that. So, how would the stSQL be generated dynamically by the
user filtering down a recordset?

BTW, I'm fairly familiar with parameters in queries but I've only ever
used
them in a simplistic way when producing reports. I've found them fairy
crude,
as I can only present the user with the ability to specify one criteria at
a
time and they have to remember to enter the exact field contents every
time.
I guess there is a way to present the user with a combo box to select
entries
from but I don't know how to do that. Even then, the user wouldn't get to
see
the filtered recordset before the routine would run, hence my idea of
using a
normal form to allow the normal filtering process and viewing of the
results
before clicking on a command button to pass the filtered set to the
routine.

So at the moment, I have two options:

1. build a simple query with a parameter for the field Town, then use that
query as the record source for the routine. That would present a fixed
question ( e.g. "What Town Name?" ) to the user every time. Actually, that
would suit my needs at the present, although they wouldn't be able to view
the resultant filtered set before Outlook opens. Still, it would do.

2. try to fully understand what you mean by dynamically producing the SQL
to
act as the record source, because presumably that would allow the users to
choose themselves what fields to filter on. Much more useful. And maybe
this
option would allow them to view the filtered recordset and perhaps
correct/redo it before it is passed to the routine?

So, to repeat the question - how would the stSQL be generated dynamically
by
the user filtering down a recordset?

Hopefully I'm understanding you correctly now. Many thanks, again, for the
help.

Sounds as though you may need to do a bit of homework into how to create
queries that accept parameters from the user.

If you had a parameter-driven query, that's what you'd use when opening
your
recordset. The SQL string I gave was a sample of how you can dynamically
create SQL statements. The assumption was that you'd pass some additional
parameters to the function, indicating how to build the WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you, Doug, for providing such a comprehensive reply and for the
additional crutial info.

I just want to focus on your last point, where you said:

"Now, this code is going to send email to every entry in the table. To
limit
to whom the e-mail gets send, you'll need to create a query that
returns
only those schools to whom you wish to e-mail, use that query name
rather
than tblSchool in the OpenRecordset command. Alternatively, you could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)"

The thing is, I want to give my users the ability to choose the group
of
Schools to whom an Email is sent, dynamically. By that I mean they open
a
form which initially shows all School records, then they filter the
recordset
to, say, only those Schools in a particular town, then they would click
on
a
command button that calls up the routine to run using the filtered set
of
records. The next time, they might choose to filter down to those in a
different town, or using a different selection criteria all together.
Your
suggestions, if I understand them correctly, would assume that the set
of
Schools was fixed and predetermined.

To look at it another way, where a particular line of code in the
routine
reads:

Set MyRS = MyDB.OpenRecordset("tblMailingList")

it would instead say something like:

Set MyRS = MyDB.OpenRecordset("Something here that points to the forms
currently filtered recordset")

I hope you get the idea.

Again, thanks for taking the time to produce such useful answers.



:


The article provides code for a Module but I don't know how this is
used.
i.e. how do I call up this code - from the 'on click' event of a
command
button on the form described? How exactly - what is the Module name?
As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated with a
form
or report) and copy all of the code from step 7 into that new module.
What
you name the module when you save it is irrelevant (as long as you
don't
name it SendMessages, since modules cannot have the same name as
functions
or subs contained within them)

In your "on click" event (or however you want to invoke it), you'd put
a
line of code:

Call SendMessages

or

Call SendMessages("C:\TestAttachment.txt")

I disagree stylistically with what they're saying in step 11: I think
it's
better to use the Call keyword, but the two lines above are equivalent
to
just using

SendMessages

or

SendMessages "C:\TestAttachment.txt"

(Note that you must enclose the parameter in parentheses when you use
the
Call keyword, and you don't when not using Call)

Next, I don't understand what is meant by step 11 in the
instructions.
I
don't want to send attachments so I would enter just 'SendMessages'
in
the
debug window, but why? - what happens when I press enter?

Pressing Enter in the debug window means that what's been typed
preceding
the Enter gets executed.

Is this meant to test the procedure before building it into a
command
buttons code?

Yes.

Once I get the code to work, I then need to know how to apply it to
my
own
particular circumstances. I will be providing my users with a form
bound
to
table tblSchools. This table lists School details, including Email
address.
They will use the form to filter down to a set of records, e.g all
schools
in
a certain Town and I want this code (say, on a command button on the
form)
to
transfer only those email addresses in the filtered set to Outlook,
rather
than all records in tblSchools. How could this be done?

First, let me tell you about a fairly serious omission in the sample
if
you're using Access 2000 or 2002. The code is using DAO as the method
of
getting the data from the table, but by default Access 2000 and 2002
do
not
contain a reference to the DAO library. In section 9, where it's
talking
about setting references, you need to ensure that you also have a
reference
set to the Microsoft DAO 3.6 Object Library. As well, you need to
change
the
line of code

Dim MyRS As Recordset

to

Dim MyRS As DAO.Recordset

(You could also change Dim MyDB As Database to Dim MyDB As
DAO.Database
if
you want, but that's not absolutely necessary)

To customize the code to your specific situation, you need to change

Set MyRS = MyDB.OpenRecordset("tblMailingList")

to

Set MyRS = MyDB.OpenRecordset("tblSchool")

If the e-mail address in tblSchool isn't named EmailAddress, you also
need
to change EmailAddress to the actual field name in this line of code:

TheAddress = MyRS![EmailAddress]

Now, this code is going to send email to every entry in the table. To
limit
to whom the e-mail gets send, you'll need to create a query that
returns
only those schools to whom you wish to e-mail, use that query name
rather
than tblSchool in the OpenRecordset command. Alternatively, you could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

 

[JohnB]

Hi Doug. Thanks for getting back.

You said

"A simplistic approach is to create a form that has a combo box listing all
of the towns, and have the query point to the combo box to get its value."

Then you went to to give links covering more complicated posibilites.

Hold on there - I want the simplistic approach!

So, say I have a form with three combos, School Name, Town and Email and a
command button cmdOpenOutlook. Also I have a query qryCreateEmailSet which I
use as the source in the routine. Are you saying I can use a reference to
each of the forms combo values as criteria parameters in the query? That way
the user selects values in one or more of the combos and when the command
button calls up the routine, the query runs and filters down using the values
in the combos? If that's correct, I think I've got it! All is left is to find
out the syntax for referencing the combos.

Please tell me this is correct. If it is, I can actually do it!

Thanks again for all the help.

A simplistic approach is to create a form that has a combo box listing all
of the towns, and have the query point to the combo box to get its value. If
you want to get a little more sophisticated, you could use a list box, so
that they can multiselect towns. However, that requires that you build the
WHERE clause in code (see http://www.mvps.org/access/forms/frm0007.htm at
"The Access Web" for an example of how you do that) To see something even
more sophisticated, take a look at the Query By Form example Duane Hookom
has at http://www.access.hookom.net/Samples.htm

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug.

I read your reply earlier but gave myself some time to digest all you'd
said
before I got back.

You said: "If you had a parameter-driven query, that's what you'd use when
opening your recordset. The SQL string I gave was a sample of how you can
dynamically create SQL statements. The assumption was that you'd pass some
additional parameters to the function, indicating how to build the WHERE
clause."

meaning your eariler code:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

I didn't realise that this was meant to be dynamic. I thought you meant I
should build the SQL, using a query, then paste it into your code. I
thought
that it would not suit my needs because it would be fixed and would select
the same filtered recordset every time. Sorry - I should have realised you
didn't mean that. So, how would the stSQL be generated dynamically by the
user filtering down a recordset?

BTW, I'm fairly familiar with parameters in queries but I've only ever
used
them in a simplistic way when producing reports. I've found them fairy
crude,
as I can only present the user with the ability to specify one criteria at
a
time and they have to remember to enter the exact field contents every
time.
I guess there is a way to present the user with a combo box to select
entries
from but I don't know how to do that. Even then, the user wouldn't get to
see
the filtered recordset before the routine would run, hence my idea of
using a
normal form to allow the normal filtering process and viewing of the
results
before clicking on a command button to pass the filtered set to the
routine.

So at the moment, I have two options:

1. build a simple query with a parameter for the field Town, then use that
query as the record source for the routine. That would present a fixed
question ( e.g. "What Town Name?" ) to the user every time. Actually, that
would suit my needs at the present, although they wouldn't be able to view
the resultant filtered set before Outlook opens. Still, it would do.

2. try to fully understand what you mean by dynamically producing the SQL
to
act as the record source, because presumably that would allow the users to
choose themselves what fields to filter on. Much more useful. And maybe
this
option would allow them to view the filtered recordset and perhaps
correct/redo it before it is passed to the routine?

So, to repeat the question - how would the stSQL be generated dynamically
by
the user filtering down a recordset?

Hopefully I'm understanding you correctly now. Many thanks, again, for the
help.

Sounds as though you may need to do a bit of homework into how to create
queries that accept parameters from the user.

If you had a parameter-driven query, that's what you'd use when opening
your
recordset. The SQL string I gave was a sample of how you can dynamically
create SQL statements. The assumption was that you'd pass some additional
parameters to the function, indicating how to build the WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you, Doug, for providing such a comprehensive reply and for the
additional crutial info.

I just want to focus on your last point, where you said:

"Now, this code is going to send email to every entry in the table. To
limit
to whom the e-mail gets send, you'll need to create a query that
returns
only those schools to whom you wish to e-mail, use that query name
rather
than tblSchool in the OpenRecordset command. Alternatively, you could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)"

The thing is, I want to give my users the ability to choose the group
of
Schools to whom an Email is sent, dynamically. By that I mean they open
a
form which initially shows all School records, then they filter the
recordset
to, say, only those Schools in a particular town, then they would click
on
a
command button that calls up the routine to run using the filtered set
of
records. The next time, they might choose to filter down to those in a
different town, or using a different selection criteria all together.
Your
suggestions, if I understand them correctly, would assume that the set
of
Schools was fixed and predetermined.

To look at it another way, where a particular line of code in the
routine
reads:

Set MyRS = MyDB.OpenRecordset("tblMailingList")

it would instead say something like:

Set MyRS = MyDB.OpenRecordset("Something here that points to the forms
currently filtered recordset")

I hope you get the idea.

Again, thanks for taking the time to produce such useful answers.



:


The article provides code for a Module but I don't know how this is
used.
i.e. how do I call up this code - from the 'on click' event of a
command
button on the form described? How exactly - what is the Module name?
As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated with a
form
or report) and copy all of the code from step 7 into that new module.
What
you name the module when you save it is irrelevant (as long as you
don't
name it SendMessages, since modules cannot have the same name as
functions
or subs contained within them)

In your "on click" event (or however you want to invoke it), you'd put
a
line of code:

Call SendMessages

or

Call SendMessages("C:\TestAttachment.txt")

I disagree stylistically with what they're saying in step 11: I think
it's
better to use the Call keyword, but the two lines above are equivalent
to
just using

SendMessages

or

SendMessages "C:\TestAttachment.txt"

(Note that you must enclose the parameter in parentheses when you use
the
Call keyword, and you don't when not using Call)

Next, I don't understand what is meant by step 11 in the
instructions.
I
don't want to send attachments so I would enter just 'SendMessages'
in
the
debug window, but why? - what happens when I press enter?

Pressing Enter in the debug window means that what's been typed
preceding
the Enter gets executed.

Is this meant to test the procedure before building it into a
command
buttons code?

Yes.

Once I get the code to work, I then need to know how to apply it to
my
own
particular circumstances. I will be providing my users with a form
bound
to
table tblSchools. This table lists School details, including Email
address.
They will use the form to filter down to a set of records, e.g all
schools
in
a certain Town and I want this code (say, on a command button on the
form)
to
transfer only those email addresses in the filtered set to Outlook,
rather
than all records in tblSchools. How could this be done?

First, let me tell you about a fairly serious omission in the sample
if
you're using Access 2000 or 2002. The code is using DAO as the method
of
getting the data from the table, but by default Access 2000 and 2002
do
not
contain a reference to the DAO library. In section 9, where it's
talking
about setting references, you need to ensure that you also have a
reference
set to the Microsoft DAO 3.6 Object Library. As well, you need to
change
the
line of code

Dim MyRS As Recordset

to

Dim MyRS As DAO.Recordset

(You could also change Dim MyDB As Database to Dim MyDB As
DAO.Database
if
you want, but that's not absolutely necessary)

To customize the code to your specific situation, you need to change

Set MyRS = MyDB.OpenRecordset("tblMailingList")

to

Set MyRS = MyDB.OpenRecordset("tblSchool")

If the e-mail address in tblSchool isn't named EmailAddress, you also
need
to change EmailAddress to the actual field name in this line of code:

TheAddress = MyRS![EmailAddress]

Now, this code is going to send email to every entry in the table. To
limit
to whom the e-mail gets send, you'll need to create a query that
returns
only those schools to whom you wish to e-mail, use that query name
rather
than tblSchool in the OpenRecordset command. Alternatively, you could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

 

[Douglas J. Steele]

You've got it.

The way to reference is to put

Forms!NameOfForm!NameOfControl

as the parameter, rather than something like [What Town Name?]

Note that if there are spaces in the name of the form or control (and
realistically, there shouldn't be), you need square brackets around the
names:

Forms![Name of form with blanks]![Name of control with blanks]

If you want to give the ability to only fill in one or two of the three
combo boxes, and have it select everything for the missing box(es), put

Forms!NameOfForm!NameOfControl Or (Forms!NameOfForm!NameOfControl IS
NULL)

in the criteria cell in the graphical query builder.

The SQL associated with that will look something like:

... WHERE MyField = Forms!NameOfForm!NameOfControl OR
(Forms!NameOfForm!NameOfControl IS NULL)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug. Thanks for getting back.

You said

"A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its value."

Then you went to to give links covering more complicated posibilites.

Hold on there - I want the simplistic approach!

So, say I have a form with three combos, School Name, Town and Email and a
command button cmdOpenOutlook. Also I have a query qryCreateEmailSet which
I
use as the source in the routine. Are you saying I can use a reference to
each of the forms combo values as criteria parameters in the query? That
way
the user selects values in one or more of the combos and when the command
button calls up the routine, the query runs and filters down using the
values
in the combos? If that's correct, I think I've got it! All is left is to
find
out the syntax for referencing the combos.

Please tell me this is correct. If it is, I can actually do it!

Thanks again for all the help.

A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its value.
If
you want to get a little more sophisticated, you could use a list box, so
that they can multiselect towns. However, that requires that you build
the
WHERE clause in code (see http://www.mvps.org/access/forms/frm0007.htm at
"The Access Web" for an example of how you do that) To see something even
more sophisticated, take a look at the Query By Form example Duane Hookom
has at http://www.access.hookom.net/Samples.htm

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug.

I read your reply earlier but gave myself some time to digest all you'd
said
before I got back.

You said: "If you had a parameter-driven query, that's what you'd use
when
opening your recordset. The SQL string I gave was a sample of how you
can
dynamically create SQL statements. The assumption was that you'd pass
some
additional parameters to the function, indicating how to build the
WHERE
clause."

meaning your eariler code:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

I didn't realise that this was meant to be dynamic. I thought you meant
I
should build the SQL, using a query, then paste it into your code. I
thought
that it would not suit my needs because it would be fixed and would
select
the same filtered recordset every time. Sorry - I should have realised
you
didn't mean that. So, how would the stSQL be generated dynamically by
the
user filtering down a recordset?

BTW, I'm fairly familiar with parameters in queries but I've only ever
used
them in a simplistic way when producing reports. I've found them fairy
crude,
as I can only present the user with the ability to specify one criteria
at
a
time and they have to remember to enter the exact field contents every
time.
I guess there is a way to present the user with a combo box to select
entries
from but I don't know how to do that. Even then, the user wouldn't get
to
see
the filtered recordset before the routine would run, hence my idea of
using a
normal form to allow the normal filtering process and viewing of the
results
before clicking on a command button to pass the filtered set to the
routine.

So at the moment, I have two options:

1. build a simple query with a parameter for the field Town, then use
that
query as the record source for the routine. That would present a fixed
question ( e.g. "What Town Name?" ) to the user every time. Actually,
that
would suit my needs at the present, although they wouldn't be able to
view
the resultant filtered set before Outlook opens. Still, it would do.

2. try to fully understand what you mean by dynamically producing the
SQL
to
act as the record source, because presumably that would allow the users
to
choose themselves what fields to filter on. Much more useful. And maybe
this
option would allow them to view the filtered recordset and perhaps
correct/redo it before it is passed to the routine?

So, to repeat the question - how would the stSQL be generated
dynamically
by
the user filtering down a recordset?

Hopefully I'm understanding you correctly now. Many thanks, again, for
the
help.



:

Sounds as though you may need to do a bit of homework into how to
create
queries that accept parameters from the user.

If you had a parameter-driven query, that's what you'd use when
opening
your
recordset. The SQL string I gave was a sample of how you can
dynamically
create SQL statements. The assumption was that you'd pass some
additional
parameters to the function, indicating how to build the WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you, Doug, for providing such a comprehensive reply and for
the
additional crutial info.

I just want to focus on your last point, where you said:

"Now, this code is going to send email to every entry in the table.
To
limit
to whom the e-mail gets send, you'll need to create a query that
returns
only those schools to whom you wish to e-mail, use that query name
rather
than tblSchool in the OpenRecordset command. Alternatively, you
could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)"

The thing is, I want to give my users the ability to choose the
group
of
Schools to whom an Email is sent, dynamically. By that I mean they
open
a
form which initially shows all School records, then they filter the
recordset
to, say, only those Schools in a particular town, then they would
click
on
a
command button that calls up the routine to run using the filtered
set
of
records. The next time, they might choose to filter down to those in
a
different town, or using a different selection criteria all
together.
Your
suggestions, if I understand them correctly, would assume that the
set
of
Schools was fixed and predetermined.

To look at it another way, where a particular line of code in the
routine
reads:

Set MyRS = MyDB.OpenRecordset("tblMailingList")

it would instead say something like:

Set MyRS = MyDB.OpenRecordset("Something here that points to the
forms
currently filtered recordset")

I hope you get the idea.

Again, thanks for taking the time to produce such useful answers.



:


The article provides code for a Module but I don't know how this
is
used.
i.e. how do I call up this code - from the 'on click' event of a
command
button on the form described? How exactly - what is the Module
name?
As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated
with a
form
or report) and copy all of the code from step 7 into that new
module.
What
you name the module when you save it is irrelevant (as long as you
don't
name it SendMessages, since modules cannot have the same name as
functions
or subs contained within them)

In your "on click" event (or however you want to invoke it), you'd
put
a
line of code:

Call SendMessages

or

Call SendMessages("C:\TestAttachment.txt")

I disagree stylistically with what they're saying in step 11: I
think
it's
better to use the Call keyword, but the two lines above are
equivalent
to
just using

SendMessages

or

SendMessages "C:\TestAttachment.txt"

(Note that you must enclose the parameter in parentheses when you
use
the
Call keyword, and you don't when not using Call)

Next, I don't understand what is meant by step 11 in the
instructions.
I
don't want to send attachments so I would enter just
'SendMessages'
in
the
debug window, but why? - what happens when I press enter?

Pressing Enter in the debug window means that what's been typed
preceding
the Enter gets executed.

Is this meant to test the procedure before building it into a
command
buttons code?

Yes.

Once I get the code to work, I then need to know how to apply it
to
my
own
particular circumstances. I will be providing my users with a
form
bound
to
table tblSchools. This table lists School details, including
Email
address.
They will use the form to filter down to a set of records, e.g
all
schools
in
a certain Town and I want this code (say, on a command button on
the
form)
to
transfer only those email addresses in the filtered set to
Outlook,
rather
than all records in tblSchools. How could this be done?

First, let me tell you about a fairly serious omission in the
sample
if
you're using Access 2000 or 2002. The code is using DAO as the
method
of
getting the data from the table, but by default Access 2000 and
2002
do
not
contain a reference to the DAO library. In section 9, where it's
talking
about setting references, you need to ensure that you also have a
reference
set to the Microsoft DAO 3.6 Object Library. As well, you need to
change
the
line of code

Dim MyRS As Recordset

to

Dim MyRS As DAO.Recordset

(You could also change Dim MyDB As Database to Dim MyDB As
DAO.Database
if
you want, but that's not absolutely necessary)

To customize the code to your specific situation, you need to
change

Set MyRS = MyDB.OpenRecordset("tblMailingList")

to

Set MyRS = MyDB.OpenRecordset("tblSchool")

If the e-mail address in tblSchool isn't named EmailAddress, you
also
need
to change EmailAddress to the actual field name in this line of
code:

TheAddress = MyRS![EmailAddress]

Now, this code is going to send email to every entry in the table.
To
limit
to whom the e-mail gets send, you'll need to create a query that
returns
only those schools to whom you wish to e-mail, use that query name
rather
than tblSchool in the OpenRecordset command. Alternatively, you
could
create
an SQL Statement in the routine, and use that to open the
recordset:

Dim strSQL As String

 

[JohnB]

Excellent. Thank you very much.

Although I have yet to apply all of your advice, there is something special
when you reach the point of visualising the way something should work. Now I
can see how it should happen and it makes sense - everything else is just the
detail of getting the syntax right.

I appreciate all the help so far, but especially the extra points you've
made along the way ( I actually knew about using [ ] when a control had
spaces but you were right to assume that I did not know). I think that's what
shows the difference between help and great help; the ability to see what
extra help might be needed (your earlier points about DOA are crutial).

Thank you very much for sticking with me on this Doug - and over the weekend
too!

Cheers,

You've got it.

The way to reference is to put

Forms!NameOfForm!NameOfControl

as the parameter, rather than something like [What Town Name?]

Note that if there are spaces in the name of the form or control (and
realistically, there shouldn't be), you need square brackets around the
names:

Forms![Name of form with blanks]![Name of control with blanks]

If you want to give the ability to only fill in one or two of the three
combo boxes, and have it select everything for the missing box(es), put

Forms!NameOfForm!NameOfControl Or (Forms!NameOfForm!NameOfControl IS
NULL)

in the criteria cell in the graphical query builder.

The SQL associated with that will look something like:

... WHERE MyField = Forms!NameOfForm!NameOfControl OR
(Forms!NameOfForm!NameOfControl IS NULL)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug. Thanks for getting back.

You said

"A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its value."

Then you went to to give links covering more complicated posibilites.

Hold on there - I want the simplistic approach!

So, say I have a form with three combos, School Name, Town and Email and a
command button cmdOpenOutlook. Also I have a query qryCreateEmailSet which
I
use as the source in the routine. Are you saying I can use a reference to
each of the forms combo values as criteria parameters in the query? That
way
the user selects values in one or more of the combos and when the command
button calls up the routine, the query runs and filters down using the
values
in the combos? If that's correct, I think I've got it! All is left is to
find
out the syntax for referencing the combos.

Please tell me this is correct. If it is, I can actually do it!

Thanks again for all the help.

 

[JohnB]

Hi Doug.

I wonder if you are still monitoring this thread. If I don't hear from you,
I will start a new one.

I have set things up now but when I call up the module, I get an error:
"Compile Error. User defined type not found" Then the module opens in design
view with the line"Dim Obj Outlook As Outlook.Application" highlighted in
blue.

I've checked my references and the following three are checked:

Visual Basic for Application
Microsoft Access 10.0 Object Library
Microsoft DAO3.6 Object Library.

The top section of Module code is as follow (hopefully I have made the
changes you mentioned correctly)

Option Compare Database

Option Explicit

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qrySchoolsUsedEmail")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS!

My mdb is Access 2000, running within Access 2002 on XP.

Any ideas what's wrong?

Thanks

[QUOTE="Douglas J. Steele"]
You've got it.

The way to reference is to put

Forms!NameOfForm!NameOfControl

as the parameter, rather than something like [What Town Name?]

Note that if there are spaces in the name of the form or control (and
realistically, there shouldn't be), you need square brackets around the
names:

Forms![Name of form with blanks]![Name of control with blanks]

If you want to give the ability to only fill in one or two of the three
combo boxes, and have it select everything for the missing box(es), put

Forms!NameOfForm!NameOfControl Or (Forms!NameOfForm!NameOfControl IS
NULL)

in the criteria cell in the graphical query builder.

The SQL associated with that will look something like:

... WHERE MyField = Forms!NameOfForm!NameOfControl OR
(Forms!NameOfForm!NameOfControl IS NULL)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


[QUOTE="JohnB"]
Hi Doug. Thanks for getting back.

You said

"A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its value."

Then you went to to give links covering more complicated posibilites.

Hold on there - I want the simplistic approach!

So, say I have a form with three combos, School Name, Town and Email and a
command button cmdOpenOutlook. Also I have a query qryCreateEmailSet which
I
use as the source in the routine. Are you saying I can use a reference to
each of the forms combo values as criteria parameters in the query? That
way
the user selects values in one or more of the combos and when the command
button calls up the routine, the query runs and filters down using the
values
in the combos? If that's correct, I think I've got it! All is left is to
find
out the syntax for referencing the combos.

Please tell me this is correct. If it is, I can actually do it!

Thanks again for all the help.
[/QUOTE][/QUOTE]

 

[David Cox]

Alt F11 to get VBA window open.
Tools - references - tick Microsoft Outlook object library.

Hi Doug.

I wonder if you are still monitoring this thread. If I don't hear from
you,
I will start a new one.

I have set things up now but when I call up the module, I get an error:
"Compile Error. User defined type not found" Then the module opens in
design
view with the line"Dim Obj Outlook As Outlook.Application" highlighted in
blue.

I've checked my references and the following three are checked:

Visual Basic for Application
Microsoft Access 10.0 Object Library
Microsoft DAO3.6 Object Library.

The top section of Module code is as follow (hopefully I have made the
changes you mentioned correctly)

Option Compare Database

Option Explicit

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qrySchoolsUsedEmail")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS!

My mdb is Access 2000, running within Access 2002 on XP.

Any ideas what's wrong?

Thanks

[QUOTE="Douglas J. Steele"]
You've got it.

The way to reference is to put

Forms!NameOfForm!NameOfControl

as the parameter, rather than something like [What Town Name?]

Note that if there are spaces in the name of the form or control (and
realistically, there shouldn't be), you need square brackets around the
names:

Forms![Name of form with blanks]![Name of control with blanks]

If you want to give the ability to only fill in one or two of the three
combo boxes, and have it select everything for the missing box(es), put

Forms!NameOfForm!NameOfControl Or (Forms!NameOfForm!NameOfControl IS
NULL)

in the criteria cell in the graphical query builder.

The SQL associated with that will look something like:

... WHERE MyField = Forms!NameOfForm!NameOfControl OR
(Forms!NameOfForm!NameOfControl IS NULL)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


[QUOTE="JohnB"]
Hi Doug. Thanks for getting back.

You said

"A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its
value."

Then you went to to give links covering more complicated posibilites.

Hold on there - I want the simplistic approach!

So, say I have a form with three combos, School Name, Town and Email
and a
command button cmdOpenOutlook. Also I have a query qryCreateEmailSet
which
I
use as the source in the routine. Are you saying I can use a reference
to
each of the forms combo values as criteria parameters in the query?
That
way
the user selects values in one or more of the combos and when the
command
button calls up the routine, the query runs and filters down using the
values
in the combos? If that's correct, I think I've got it! All is left is
to
find
out the syntax for referencing the combos.

Please tell me this is correct. If it is, I can actually do it!

Thanks again for all the help.




:

A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its
value.
If
you want to get a little more sophisticated, you could use a list box,
so
that they can multiselect towns. However, that requires that you build
the
WHERE clause in code (see http://www.mvps.org/access/forms/frm0007.htm
at
"The Access Web" for an example of how you do that) To see something
even
more sophisticated, take a look at the Query By Form example Duane
Hookom
has at http://www.access.hookom.net/Samples.htm

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi Doug.

I read your reply earlier but gave myself some time to digest all
you'd
said
before I got back.

You said: "If you had a parameter-driven query, that's what you'd
use
when
opening your recordset. The SQL string I gave was a sample of how
you
can
dynamically create SQL statements. The assumption was that you'd
pass
some
additional parameters to the function, indicating how to build the
WHERE
clause."

meaning your eariler code:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

I didn't realise that this was meant to be dynamic. I thought you
meant
I
should build the SQL, using a query, then paste it into your code. I
thought
that it would not suit my needs because it would be fixed and would
select
the same filtered recordset every time. Sorry - I should have
realised
you
didn't mean that. So, how would the stSQL be generated dynamically
by
the
user filtering down a recordset?

BTW, I'm fairly familiar with parameters in queries but I've only
ever
used
them in a simplistic way when producing reports. I've found them
fairy
crude,
as I can only present the user with the ability to specify one
criteria
at
a
time and they have to remember to enter the exact field contents
every
time.
I guess there is a way to present the user with a combo box to
select
entries
from but I don't know how to do that. Even then, the user wouldn't
get
to
see
the filtered recordset before the routine would run, hence my idea
of
using a
normal form to allow the normal filtering process and viewing of the
results
before clicking on a command button to pass the filtered set to the
routine.

So at the moment, I have two options:

1. build a simple query with a parameter for the field Town, then
use
that
query as the record source for the routine. That would present a
fixed
question ( e.g. "What Town Name?" ) to the user every time.
Actually,
that
would suit my needs at the present, although they wouldn't be able
to
view
the resultant filtered set before Outlook opens. Still, it would do.

2. try to fully understand what you mean by dynamically producing
the
SQL
to
act as the record source, because presumably that would allow the
users
to
choose themselves what fields to filter on. Much more useful. And
maybe
this
option would allow them to view the filtered recordset and perhaps
correct/redo it before it is passed to the routine?

So, to repeat the question - how would the stSQL be generated
dynamically
by
the user filtering down a recordset?

Hopefully I'm understanding you correctly now. Many thanks, again,
for
the
help.



:

Sounds as though you may need to do a bit of homework into how to
create
queries that accept parameters from the user.

If you had a parameter-driven query, that's what you'd use when
opening
your
recordset. The SQL string I gave was a sample of how you can
dynamically
create SQL statements. The assumption was that you'd pass some
additional
parameters to the function, indicating how to build the WHERE
clause.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Thank you, Doug, for providing such a comprehensive reply and for
the
additional crutial info.

I just want to focus on your last point, where you said:

"Now, this code is going to send email to every entry in the
table.
To
limit
to whom the e-mail gets send, you'll need to create a query that
returns
only those schools to whom you wish to e-mail, use that query
name
rather
than tblSchool in the OpenRecordset command. Alternatively, you
could
create
an SQL Statement in the routine, and use that to open the
recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town =
'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)"

The thing is, I want to give my users the ability to choose the
group
of
Schools to whom an Email is sent, dynamically. By that I mean
they
open
a
form which initially shows all School records, then they filter
the
recordset
to, say, only those Schools in a particular town, then they would
click
on
a
command button that calls up the routine to run using the
filtered
set
of
records. The next time, they might choose to filter down to those
in
a
different town, or using a different selection criteria all
together.
Your
suggestions, if I understand them correctly, would assume that
the
set
of
Schools was fixed and predetermined.

To look at it another way, where a particular line of code in the
routine
reads:

Set MyRS = MyDB.OpenRecordset("tblMailingList")

it would instead say something like:

Set MyRS = MyDB.OpenRecordset("Something here that points to the
forms
currently filtered recordset")

I hope you get the idea.

Again, thanks for taking the time to produce such useful answers.



:


The article provides code for a Module but I don't know how
this
is
used.
i.e. how do I call up this code - from the 'on click' event of
a
command
button on the form described? How exactly - what is the Module
name?
As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated
with a
form
or report) and copy all of the code from step 7 into that new
module.
What
you name the module when you save it is irrelevant (as long as
you
don't
name it SendMessages, since modules cannot have the same name as
functions
or subs contained within them)

In your "on click" event (or however you want to invoke it),
you'd
put
a
line of code:

Call SendMessages[/QUOTE][/QUOTE][/QUOTE]

 

[JohnB]

Thanks David.

It will be tomorrow around 11am GMT before I can try this. Hopefully all
will be OK but I'll repost here if I hit another glitch.

Thanks again

Alt F11 to get VBA window open.
Tools - references - tick Microsoft Outlook object library.

Hi Doug.

I wonder if you are still monitoring this thread. If I don't hear from
you,
I will start a new one.

I have set things up now but when I call up the module, I get an error:
"Compile Error. User defined type not found" Then the module opens in
design
view with the line"Dim Obj Outlook As Outlook.Application" highlighted in
blue.

I've checked my references and the following three are checked:

Visual Basic for Application
Microsoft Access 10.0 Object Library
Microsoft DAO3.6 Object Library.

The top section of Module code is as follow (hopefully I have made the
changes you mentioned correctly)

Option Compare Database

Option Explicit

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qrySchoolsUsedEmail")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS!

My mdb is Access 2000, running within Access 2002 on XP.

Any ideas what's wrong?

Thanks

[QUOTE="Douglas J. Steele"]
You've got it.

The way to reference is to put

Forms!NameOfForm!NameOfControl

as the parameter, rather than something like [What Town Name?]

Note that if there are spaces in the name of the form or control (and
realistically, there shouldn't be), you need square brackets around the
names:

Forms![Name of form with blanks]![Name of control with blanks]

If you want to give the ability to only fill in one or two of the three
combo boxes, and have it select everything for the missing box(es), put

Forms!NameOfForm!NameOfControl Or (Forms!NameOfForm!NameOfControl IS
NULL)

in the criteria cell in the graphical query builder.

The SQL associated with that will look something like:

... WHERE MyField = Forms!NameOfForm!NameOfControl OR
(Forms!NameOfForm!NameOfControl IS NULL)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi Doug. Thanks for getting back.

You said

"A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its
value."

Then you went to to give links covering more complicated posibilites.

Hold on there - I want the simplistic approach!

So, say I have a form with three combos, School Name, Town and Email
and a
command button cmdOpenOutlook. Also I have a query qryCreateEmailSet
which
I
use as the source in the routine. Are you saying I can use a reference
to
each of the forms combo values as criteria parameters in the query?
That
way
the user selects values in one or more of the combos and when the
command
button calls up the routine, the query runs and filters down using the
values
in the combos? If that's correct, I think I've got it! All is left is
to
find
out the syntax for referencing the combos.

Please tell me this is correct. If it is, I can actually do it!

Thanks again for all the help.




:

A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its
value.
If
you want to get a little more sophisticated, you could use a list box,
so
that they can multiselect towns. However, that requires that you build
the
WHERE clause in code (see http://www.mvps.org/access/forms/frm0007.htm
at
"The Access Web" for an example of how you do that) To see something
even
more sophisticated, take a look at the Query By Form example Duane
Hookom
has at http://www.access.hookom.net/Samples.htm

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi Doug.

I read your reply earlier but gave myself some time to digest all
you'd
said
before I got back.

You said: "If you had a parameter-driven query, that's what you'd
use
when
opening your recordset. The SQL string I gave was a sample of how
you
can
dynamically create SQL statements. The assumption was that you'd
pass
some
additional parameters to the function, indicating how to build the
WHERE
clause."

meaning your eariler code:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

I didn't realise that this was meant to be dynamic. I thought you
meant
I
should build the SQL, using a query, then paste it into your code. I
thought
that it would not suit my needs because it would be fixed and would
select
the same filtered recordset every time. Sorry - I should have
realised
you
didn't mean that. So, how would the stSQL be generated dynamically
by
the
user filtering down a recordset?

BTW, I'm fairly familiar with parameters in queries but I've only
ever
used
them in a simplistic way when producing reports. I've found them
fairy
crude,
as I can only present the user with the ability to specify one
criteria
at
a
time and they have to remember to enter the exact field contents
every
time.
I guess there is a way to present the user with a combo box to
select
entries
from but I don't know how to do that. Even then, the user wouldn't
get
to
see
the filtered recordset before the routine would run, hence my idea
of
using a
normal form to allow the normal filtering process and viewing of the
results
before clicking on a command button to pass the filtered set to the
routine.

So at the moment, I have two options:

1. build a simple query with a parameter for the field Town, then
use
that
query as the record source for the routine. That would present a
fixed
question ( e.g. "What Town Name?" ) to the user every time.
Actually,
that
would suit my needs at the present, although they wouldn't be able
to
view
the resultant filtered set before Outlook opens. Still, it would do.

2. try to fully understand what you mean by dynamically producing
the
SQL
to
act as the record source, because presumably that would allow the
users
to
choose themselves what fields to filter on. Much more useful. And
maybe
this
option would allow them to view the filtered recordset and perhaps
correct/redo it before it is passed to the routine?

So, to repeat the question - how would the stSQL be generated
dynamically
by
the user filtering down a recordset?

Hopefully I'm understanding you correctly now. Many thanks, again,
for
the
help.



:

Sounds as though you may need to do a bit of homework into how to
create
queries that accept parameters from the user.

If you had a parameter-driven query, that's what you'd use when
opening
your
recordset. The SQL string I gave was a sample of how you can
dynamically
create SQL statements. The assumption was that you'd pass some
additional
parameters to the function, indicating how to build the WHERE
clause.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Thank you, Doug, for providing such a comprehensive reply and for
the
additional crutial info.
[/QUOTE][/QUOTE][/QUOTE]

 

[JohnB]

Hi again David/Doug.

I've done as you said and I have got further but have now hit another
problem. I'm getting"Error 3061:Too Few Parameters. Expected 1".

Perhaps I should summarise.

I have frmEmailSelection with cboPlacementStage and two command buttons. I
also have qrySchoolsUsedEmail which has a parameter built in so that whatever
the user selects in cboPlacementStage is used to filter down the queries
recordset. The first command button calls up a form with the query as source
and allows the user to see the filtered recordset. (This works OK showing
that the selection form and query are talking to each other correctly). Then
they close that form, open frmSchoolEmailSelection again and click the other
command button, which calls up the module to start Outlook and populate the
'To' window with the selected recordset. This does not work and produces the
above message.

Actually, I added another Parameter to the query, (although not one that the
user would specify on frmEmailSelection) just to see what would happen. I
expected the query to ask me the parameter question but I just got the same
message with “Expected 2†at the end.

Any suggestions? Here is the full Module code. Thanks again for the help.

Option Compare Database

Option Explicit

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qrySchoolsUsedEmail")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS!

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
'If (IsNull(Forms!frmMail!CCAddress)) Then
'Else
' Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
' objOutlookRecip.Type = olCC
' End If

' Set the Subject, the Body, and the Importance of the e-mail message.
'.Subject = Forms!frmMail!Subject
' .Body = Forms!frmMail!MainText
' .Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
'If Not IsMissing(AttachmentPath) Then
' Set objOutlookAttach = .Attachments.Add(AttachmentPath)
' End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub




[QUOTE="David Cox"]
Alt F11 to get VBA window open.
Tools - references - tick Microsoft Outlook object library.


[QUOTE="JohnB"]
Hi Doug.

I wonder if you are still monitoring this thread. If I don't hear from
you,
I will start a new one.

I have set things up now but when I call up the module, I get an error:
"Compile Error. User defined type not found" Then the module opens in
design
view with the line"Dim Obj Outlook As Outlook.Application" highlighted in
blue.

I've checked my references and the following three are checked:

Visual Basic for Application
Microsoft Access 10.0 Object Library
Microsoft DAO3.6 Object Library.

The top section of Module code is as follow (hopefully I have made the
changes you mentioned correctly)

Option Compare Database

Option Explicit

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qrySchoolsUsedEmail")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EMail]

My mdb is Access 2000, running within Access 2002 on XP.

Any ideas what's wrong?

Thanks

[QUOTE="Douglas J. Steele"]
You've got it.

The way to reference is to put

Forms!NameOfForm!NameOfControl

as the parameter, rather than something like [What Town Name?]

Note that if there are spaces in the name of the form or control (and
realistically, there shouldn't be), you need square brackets around the
names:

Forms![Name of form with blanks]![Name of control with blanks]

If you want to give the ability to only fill in one or two of the three
combo boxes, and have it select everything for the missing box(es), put

Forms!NameOfForm!NameOfControl Or (Forms!NameOfForm!NameOfControl IS
NULL)

in the criteria cell in the graphical query builder.

The SQL associated with that will look something like:

... WHERE MyField = Forms!NameOfForm!NameOfControl OR
(Forms!NameOfForm!NameOfControl IS NULL)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi Doug. Thanks for getting back.

You said

"A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its
value."

Then you went to to give links covering more complicated posibilites.

Hold on there - I want the simplistic approach!

So, say I have a form with three combos, School Name, Town and Email
and a
command button cmdOpenOutlook. Also I have a query qryCreateEmailSet
which
I
use as the source in the routine. Are you saying I can use a reference
to
each of the forms combo values as criteria parameters in the query?
That
way
the user selects values in one or more of the combos and when the
command
button calls up the routine, the query runs and filters down using the
values
in the combos? If that's correct, I think I've got it! All is left is
to
find
out the syntax for referencing the combos.

Please tell me this is correct. If it is, I can actually do it!

Thanks again for all the help.




:

A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its
value.
If
you want to get a little more sophisticated, you could use a list box,
so
that they can multiselect towns. However, that requires that you build
the
WHERE clause in code (see http://www.mvps.org/access/forms/frm0007.htm
at
"The Access Web" for an example of how you do that) To see something
even
more sophisticated, take a look at the Query By Form example Duane
Hookom
has at http://www.access.hookom.net/Samples.htm

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi Doug.

I read your reply earlier but gave myself some time to digest all
you'd
said
before I got back.

You said: "If you had a parameter-driven query, that's what you'd
use
when
opening your recordset. The SQL string I gave was a sample of how
you
can
dynamically create SQL statements. The assumption was that you'd
pass
some
additional parameters to the function, indicating how to build the
WHERE
clause."

meaning your eariler code:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

I didn't realise that this was meant to be dynamic. I thought you
meant
I
should build the SQL, using a query, then paste it into your code. I
thought
that it would not suit my needs because it would be fixed and would
select
the same filtered recordset every time. Sorry - I should have
realised
you
didn't mean that. So, how would the stSQL be generated dynamically
by
the
user filtering down a recordset?

BTW, I'm fairly familiar with parameters in queries but I've only
ever
used
them in a simplistic way when producing reports. I've found them
fairy
crude,
as I can only present the user with the ability to specify one
criteria
at
a
time and they have to remember to enter the exact field contents
every
time.
I guess there is a way to present the user with a combo box to
select
entries
from but I don't know how to do that. Even then, the user wouldn't
get
to
see
the filtered recordset before the routine would run, hence my idea
of
using a
normal form to allow the normal filtering process and viewing of the
results
before clicking on a command button to pass the filtered set to the
routine.

So at the moment, I have two options:

1. build a simple query with a parameter for the field Town, then
use
that
query as the record source for the routine. That would present a
fixed
question ( e.g. "What Town Name?" ) to the user every time.
Actually,
that
would suit my needs at the present, although they wouldn't be able
to
view
the resultant filtered set before Outlook opens. Still, it would do.

2. try to fully understand what you mean by dynamically producing
the
SQL
to
act as the record source, because presumably that would allow the
users
to
choose themselves what fields to filter on. Much more useful. And
maybe
this
option would allow them to view the filtered recordset and perhaps
correct/redo it before it is passed to the routine?

So, to repeat the question - how would the stSQL be generated
dynamically
by
the user filtering down a recordset?

Hopefully I'm understanding you correctly now. Many thanks, again,
for
the
help.



:

Sounds as though you may need to do a bit of homework into how to
create
queries that accept parameters from the user.

If you had a parameter-driven query, that's what you'd use when
opening
your
recordset. The SQL string I gave was a sample of how you can
dynamically
create SQL statements. The assumption was that you'd pass some
additional
parameters to the function, indicating how to build the WHERE
clause.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Thank you, Doug, for providing such a comprehensive reply and for
the
additional crutial info.
[/QUOTE][/QUOTE][/QUOTE]

 

[JohnB]

hi.

If you see this, please note that I've created a new post today (22nd Sep).
Please ignore this thread now and reply to the new one if you can. Thanks.

Alt F11 to get VBA window open.
Tools - references - tick Microsoft Outlook object library.

Hi Doug.

I wonder if you are still monitoring this thread. If I don't hear from
you,
I will start a new one.

I have set things up now but when I call up the module, I get an error:
"Compile Error. User defined type not found" Then the module opens in
design
view with the line"Dim Obj Outlook As Outlook.Application" highlighted in
blue.

I've checked my references and the following three are checked:

Visual Basic for Application
Microsoft Access 10.0 Object Library
Microsoft DAO3.6 Object Library.

The top section of Module code is as follow (hopefully I have made the
changes you mentioned correctly)

Option Compare Database

Option Explicit

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qrySchoolsUsedEmail")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS!

My mdb is Access 2000, running within Access 2002 on XP.

Any ideas what's wrong?

Thanks

[QUOTE="Douglas J. Steele"]
You've got it.

The way to reference is to put

Forms!NameOfForm!NameOfControl

as the parameter, rather than something like [What Town Name?]

Note that if there are spaces in the name of the form or control (and
realistically, there shouldn't be), you need square brackets around the
names:

Forms![Name of form with blanks]![Name of control with blanks]

If you want to give the ability to only fill in one or two of the three
combo boxes, and have it select everything for the missing box(es), put

Forms!NameOfForm!NameOfControl Or (Forms!NameOfForm!NameOfControl IS
NULL)

in the criteria cell in the graphical query builder.

The SQL associated with that will look something like:

... WHERE MyField = Forms!NameOfForm!NameOfControl OR
(Forms!NameOfForm!NameOfControl IS NULL)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi Doug. Thanks for getting back.

You said

"A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its
value."

Then you went to to give links covering more complicated posibilites.

Hold on there - I want the simplistic approach!

So, say I have a form with three combos, School Name, Town and Email
and a
command button cmdOpenOutlook. Also I have a query qryCreateEmailSet
which
I
use as the source in the routine. Are you saying I can use a reference
to
each of the forms combo values as criteria parameters in the query?
That
way
the user selects values in one or more of the combos and when the
command
button calls up the routine, the query runs and filters down using the
values
in the combos? If that's correct, I think I've got it! All is left is
to
find
out the syntax for referencing the combos.

Please tell me this is correct. If it is, I can actually do it!

Thanks again for all the help.




:

A simplistic approach is to create a form that has a combo box listing
all
of the towns, and have the query point to the combo box to get its
value.
If
you want to get a little more sophisticated, you could use a list box,
so
that they can multiselect towns. However, that requires that you build
the
WHERE clause in code (see http://www.mvps.org/access/forms/frm0007.htm
at
"The Access Web" for an example of how you do that) To see something
even
more sophisticated, take a look at the Query By Form example Duane
Hookom
has at http://www.access.hookom.net/Samples.htm

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi Doug.

I read your reply earlier but gave myself some time to digest all
you'd
said
before I got back.

You said: "If you had a parameter-driven query, that's what you'd
use
when
opening your recordset. The SQL string I gave was a sample of how
you
can
dynamically create SQL statements. The assumption was that you'd
pass
some
additional parameters to the function, indicating how to build the
WHERE
clause."

meaning your eariler code:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

I didn't realise that this was meant to be dynamic. I thought you
meant
I
should build the SQL, using a query, then paste it into your code. I
thought
that it would not suit my needs because it would be fixed and would
select
the same filtered recordset every time. Sorry - I should have
realised
you
didn't mean that. So, how would the stSQL be generated dynamically
by
the
user filtering down a recordset?

BTW, I'm fairly familiar with parameters in queries but I've only
ever
used
them in a simplistic way when producing reports. I've found them
fairy
crude,
as I can only present the user with the ability to specify one
criteria
at
a
time and they have to remember to enter the exact field contents
every
time.
I guess there is a way to present the user with a combo box to
select
entries
from but I don't know how to do that. Even then, the user wouldn't
get
to
see
the filtered recordset before the routine would run, hence my idea
of
using a
normal form to allow the normal filtering process and viewing of the
results
before clicking on a command button to pass the filtered set to the
routine.

So at the moment, I have two options:

1. build a simple query with a parameter for the field Town, then
use
that
query as the record source for the routine. That would present a
fixed
question ( e.g. "What Town Name?" ) to the user every time.
Actually,
that
would suit my needs at the present, although they wouldn't be able
to
view
the resultant filtered set before Outlook opens. Still, it would do.

2. try to fully understand what you mean by dynamically producing
the
SQL
to
act as the record source, because presumably that would allow the
users
to
choose themselves what fields to filter on. Much more useful. And
maybe
this
option would allow them to view the filtered recordset and perhaps
correct/redo it before it is passed to the routine?

So, to repeat the question - how would the stSQL be generated
dynamically
by
the user filtering down a recordset?

Hopefully I'm understanding you correctly now. Many thanks, again,
for
the
help.



:

Sounds as though you may need to do a bit of homework into how to
create
queries that accept parameters from the user.

If you had a parameter-driven query, that's what you'd use when
opening
your
recordset. The SQL string I gave was a sample of how you can
dynamically
create SQL statements. The assumption was that you'd pass some
additional
parameters to the function, indicating how to build the WHERE
clause.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Thank you, Doug, for providing such a comprehensive reply and for
the
additional crutial info.
[/QUOTE][/QUOTE][/QUOTE]

 

[John]

Is this all ment to work in Outlook, in Access or in another
application? I'm looking for the same sort of solution (but I don't
understand the macro code that quickly).
I would like to choose between groups and persons all stored in my
access application. I don't want to synchronize access-outlook or
import from access into outlook.
The idea I have is when writing a new email, in stead of using the [TO]
button, just use another one, getting the same dialog but, containing
my Access adresses.


Douglas J. Steele schreef:

You don't need RecordSetClone for what you're doing.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi again Doug.

Sorry to bombard you with replies but since writing my other one I've done
some Googling and found some info on an object called RecordSetClone. I
can't
understand how I would build this into the routine but it looks like it
would
do the job of ensuring that the routine runs on the filtered set of
records.
Is this any help?

Thanks again.

The article provides code for a Module but I don't know how this is
used.
i.e. how do I call up this code - from the 'on click' event of a
command
button on the form described? How exactly - what is the Module name? As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated with a
form
or report) and copy all of the code from step 7 into that new module.
What
you name the module when you save it is irrelevant (as long as you don't
name it SendMessages, since modules cannot have the same name as
functions
or subs contained within them)

In your "on click" event (or however you want to invoke it), you'd put a
line of code:

Call SendMessages

or

Call SendMessages("C:\TestAttachment.txt")

I disagree stylistically with what they're saying in step 11: I think
it's
better to use the Call keyword, but the two lines above are equivalent to
just using

SendMessages

or

SendMessages "C:\TestAttachment.txt"

(Note that you must enclose the parameter in parentheses when you use the
Call keyword, and you don't when not using Call)

Next, I don't understand what is meant by step 11 in the instructions.
I
don't want to send attachments so I would enter just 'SendMessages' in
the
debug window, but why? - what happens when I press enter?

Pressing Enter in the debug window means that what's been typed preceding
the Enter gets executed.

Is this meant to test the procedure before building it into a command
buttons code?

Yes.

Once I get the code to work, I then need to know how to apply it to my
own
particular circumstances. I will be providing my users with a form
bound
to
table tblSchools. This table lists School details, including Email
address.
They will use the form to filter down to a set of records, e.g all
schools
in
a certain Town and I want this code (say, on a command button on the
form)
to
transfer only those email addresses in the filtered set to Outlook,
rather
than all records in tblSchools. How could this be done?

First, let me tell you about a fairly serious omission in the sample if
you're using Access 2000 or 2002. The code is using DAO as the method of
getting the data from the table, but by default Access 2000 and 2002 do
not
contain a reference to the DAO library. In section 9, where it's talking
about setting references, you need to ensure that you also have a
reference
set to the Microsoft DAO 3.6 Object Library. As well, you need to change
the
line of code

Dim MyRS As Recordset

to

Dim MyRS As DAO.Recordset

(You could also change Dim MyDB As Database to Dim MyDB As DAO.Database
if
you want, but that's not absolutely necessary)

To customize the code to your specific situation, you need to change

Set MyRS = MyDB.OpenRecordset("tblMailingList")

to

Set MyRS = MyDB.OpenRecordset("tblSchool")

If the e-mail address in tblSchool isn't named EmailAddress, you also
need
to change EmailAddress to the actual field name in this line of code:

TheAddress = MyRS![EmailAddress]

Now, this code is going to send email to every entry in the table. To
limit
to whom the e-mail gets send, you'll need to create a query that returns
only those schools to whom you wish to e-mail, use that query name rather
than tblSchool in the OpenRecordset command. Alternatively, you could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)

 

[Douglas J. Steele]

The code we were talking about is meant to run in Access.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Is this all ment to work in Outlook, in Access or in another
application? I'm looking for the same sort of solution (but I don't
understand the macro code that quickly).
I would like to choose between groups and persons all stored in my
access application. I don't want to synchronize access-outlook or
import from access into outlook.
The idea I have is when writing a new email, in stead of using the [TO]
button, just use another one, getting the same dialog but, containing
my Access adresses.


Douglas J. Steele schreef:

You don't need RecordSetClone for what you're doing.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi again Doug.

Sorry to bombard you with replies but since writing my other one I've
done
some Googling and found some info on an object called RecordSetClone. I
can't
understand how I would build this into the routine but it looks like it
would
do the job of ensuring that the routine runs on the filtered set of
records.
Is this any help?

Thanks again.

:


The article provides code for a Module but I don't know how this is
used.
i.e. how do I call up this code - from the 'on click' event of a
command
button on the form described? How exactly - what is the Module name?
As
you
can tell, I'm not too hot on the use of modules/macros.

Create a new module (not a Class Module nor a module associated with a
form
or report) and copy all of the code from step 7 into that new module.
What
you name the module when you save it is irrelevant (as long as you
don't
name it SendMessages, since modules cannot have the same name as
functions
or subs contained within them)

In your "on click" event (or however you want to invoke it), you'd put
a
line of code:

Call SendMessages

or

Call SendMessages("C:\TestAttachment.txt")

I disagree stylistically with what they're saying in step 11: I think
it's
better to use the Call keyword, but the two lines above are equivalent
to
just using

SendMessages

or

SendMessages "C:\TestAttachment.txt"

(Note that you must enclose the parameter in parentheses when you use
the
Call keyword, and you don't when not using Call)

Next, I don't understand what is meant by step 11 in the
instructions.
I
don't want to send attachments so I would enter just 'SendMessages'
in
the
debug window, but why? - what happens when I press enter?

Pressing Enter in the debug window means that what's been typed
preceding
the Enter gets executed.

Is this meant to test the procedure before building it into a
command
buttons code?

Yes.

Once I get the code to work, I then need to know how to apply it to
my
own
particular circumstances. I will be providing my users with a form
bound
to
table tblSchools. This table lists School details, including Email
address.
They will use the form to filter down to a set of records, e.g all
schools
in
a certain Town and I want this code (say, on a command button on the
form)
to
transfer only those email addresses in the filtered set to Outlook,
rather
than all records in tblSchools. How could this be done?

First, let me tell you about a fairly serious omission in the sample
if
you're using Access 2000 or 2002. The code is using DAO as the method
of
getting the data from the table, but by default Access 2000 and 2002
do
not
contain a reference to the DAO library. In section 9, where it's
talking
about setting references, you need to ensure that you also have a
reference
set to the Microsoft DAO 3.6 Object Library. As well, you need to
change
the
line of code

Dim MyRS As Recordset

to

Dim MyRS As DAO.Recordset

(You could also change Dim MyDB As Database to Dim MyDB As
DAO.Database
if
you want, but that's not absolutely necessary)

To customize the code to your specific situation, you need to change

Set MyRS = MyDB.OpenRecordset("tblMailingList")

to

Set MyRS = MyDB.OpenRecordset("tblSchool")

If the e-mail address in tblSchool isn't named EmailAddress, you also
need
to change EmailAddress to the actual field name in this line of code:

TheAddress = MyRS![EmailAddress]

Now, this code is going to send email to every entry in the table. To
limit
to whom the e-mail gets send, you'll need to create a query that
returns
only those schools to whom you wish to e-mail, use that query name
rather
than tblSchool in the OpenRecordset command. Alternatively, you could
create
an SQL Statement in the routine, and use that to open the recordset:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM tblSchool WHERE Town = 'xxxx'"
Set MyRS = MyDB.OpenRecordset(strSQL)