Re: Help with Microsoft help on linking Access to Outlook

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)


"JohnB" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B3A8C562-DAC6-479E-8348-5321637DEA36@xxxxxxxxxxxxxxxx
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.



"Douglas J. Steele" wrote:

"JohnB" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B7AF82EF-BE21-4361-A43C-5E91878BB389@xxxxxxxxxxxxxxxx

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)

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






.