Email Report - Using a Field on Report as TO

[Lisa]

Normally i would do this in a mail merge in word, but I have 2 tables that
are linked by a request number and i can't find a way to do a mail merge that
would bring all the criteria from one request number from the linked table.

The question i have is, can I send a report using the requestor name field
on the report as the TO field in the SendObject macro option.

If there is another way to do this or if this is an option. please let me
know

thanks.

Lisa

 

[Carl Rapson]

I would use VBA instead of a macro. Assuming the report is based on a query,
you can use DLookUp to fetch the requestor name from the same query that the
report is based on, and then use that name in a call to DoCmd.SendObject:

strTo = DLookUp("requestor","name of query the report is based on","other
criteria")
DoCmd.SendObject acSendReport, "name of report", acFormatText, strTo...

Carl Rapson

 

[Lisa]

Carl,

Thanks for your response and your help. I have one additional question.
This worked perfectly except that it is prompting me to send every email.. so
if I have 25 it will prompt me 25 times. Is there a way to get it to
automatically send without prompting.

Thanks again for you help.

 

[Lisa]

One other thing I would like to ask.. currently the Submitter field
(requestor) is in a last name, first name (department, location) format -
which is our company email format. When it takes the submitter field it
changes the first comma to a semi colon and then causes a problem sending
because our email system doesn't recognize last name; first name (department,
location). Is there a way to convert that submitter field so that it would
take the whole thing without converting it with the semi-colon.

Thanks again.

 

[Carl Rapson]

Depends on what you mean by prompting. Is it telling you that a program is
attempting to send email? If so, that's a security feature of
Outlook/Exchange, and there's no way to avoid it without involving your mail
system administrators. What I do is check the box to allow for "x" minutes
so that there are no more prompts.

Carl Rapson

 

[Carl Rapson]

I don't have an answer for this one. I haven't seen that, but then I don't
use a "lastname, firstname" format. Maybe someone else here has more
experience with this?

Carl Rapson