Re: Help with Microsoft help on linking Access to Outlook

Tech-Archive recommends: Fix windows errors by optimizing your registry

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![EMail]

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




"David Cox" wrote:

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


"JohnB" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9F8E0ECB-F939-4609-86EE-CD7CBAA6F764@xxxxxxxxxxxxxxxx
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.

.

Quantcast