How do I use table data as a variable in a macro query

[Matt Moate]

I'm trying to create a macro to generate several reports and e-mail a
different report out to each e-mail address in a separate table.

I can get the report to run and an e-mail to send to a specified address but
I can't work out how to send the e-mail to an address which is selected from
a table.

Can anyone help me please?

 

[Steve Schapel]

Matt,

You could perhaps use the DLookup function to return the required email
address. Another way that may be relevant is to reference the field in
the current record on a form that is open at the time.

 

[Pluggie]

Hi Steve (and others), I'm having the same question as Matt and do not see a
solution in your answer... yet. Can you elaborate a bit please? Here's my
story:

I have been trying to make a macro that creates and sends an email with a
report as attachement.

I want the macro to automatically select the correct emailadress from either
a value on the report or from a table or query. That is why I somehow need to
put code into the "To"-line of the SendObject-command in the macro. But I
don't see any option for that. Is this possible or do I have to find a work
around?

 

[Steve Schapel]

Pluggie,

I am sorry if my earlier reply to Matt was a bit vague. Unfortunately,
with a vague question, it is difficult to give a specific answer.

Your suggestion of using "a value on the report" is not really possible.
So I go back to repeating the advice I gave to Matt. You see, you
can't just say "a value from a table or query". You have to indicate
which field in the table or query, and you also have to indicate which
record in the table or query (even if the table or query only has one
record, there is the potential for more than one, so Access doesn't know
what you're playing at unless you say so). So, sorry, vague again, but
hey I can't see your database so... one way to return the value of a
particular field in a particular record in a particular table or query
is to use a DLookup function. You can have a look in the Help or your
Access book if you don't know how to use DLookup. It is one of the
Domain Aggregate Functions.

Alternatively, if you happen to have a form open at the time of running
the SendObject macro, and if the email address is shown on the form, and
if the form's current record is the one with the email address that you
want, well.... as I said to Matt, you can refer to the Control on the
form in the To argument of the macro. The syntax of this will depend on
a few things, for example whether it's a main form or a subform, and
whether it's the same form that the macro is called from or another form.

 

[Pluggie]

Thanks Steve,

I must look into that DLookup thing some other time.
Right now i've solved it with the form, from which i wanted to take my value
in the first place.

Thanks for the advice.