Re: Help with Microsoft help on linking Access to Outlook

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

"Douglas J. Steele" wrote:

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)


"JohnB" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0CAC6161-1A7C-4FFB-8C6F-D0BB9D8F4426@xxxxxxxxxxxxxxxx
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.




"Douglas J. Steele" wrote:

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)


"JohnB" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1096BBA9-B5FC-4DE2-B5C9-973A27B9C4FD@xxxxxxxxxxxxxxxx
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.



"Douglas J. Steele" wrote:

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
.