SendObject > auto fill the "To" box

[roydmerer]

I built a db that generates a report from a query, then
attaches the report, via a macro>SendObject, to an Outlook
e-mail message. Within the db is a table of names (email
addressees) which the user selects to initiate the report
via a listbox within a form. The selected name also
appears within the report itself.

I wish to now auto-populate the "To" box in Outlook with
the chosen name and have the message sent in background?

BTW > This is in Access 95, v7...my company is By Gawd
cheap! Any help is most appreciated.

 

[Steve Schapel]

Roydmerer,

Put this in the To argument of your SendObject macro action...
=[Forms]![NameOfYourForm]![NameOfListbox].[Column](1)

This assumes your listbox's rowsource contains 2 columns, being name
and email address. Adjust as necessary if otherwise.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Many thanks, but your solution didn't work. Did I mention
this in A 95 v7? OK, so we're about a decade behind here.
There are indeed 3 columns within a names table and the e-
mail name is in column 2. I set the form/listbox neme per
your code and corrected to column 2. I still get the email
with the report attached but the "To" box is blank...the
blinking cursor is there but no name. Thanks again for the
try and any other help is a blessing.

RDM

-----Original Message-----
Roydmerer,

Put this in the To argument of your SendObject macro action...
=[Forms]![NameOfYourForm]![NameOfListbox].[Column](1)

This assumes your listbox's rowsource contains 2 columns, being name
and email address. Adjust as necessary if otherwise.

- Steve Schapel, Microsoft Access MVP

I built a db that generates a report from a query, then
attaches the report, via a macro>SendObject, to an Outlook
e-mail message. Within the db is a table of names (email
addressees) which the user selects to initiate the report
via a listbox within a form. The selected name also
appears within the report itself.

I wish to now auto-populate the "To" box in Outlook with
the chosen name and have the message sent in background?

BTW > This is in Access 95, v7...my company is By Gawd
cheap! Any help is most appreciated.

.

 

[Steve Schapel]

RDM,

Columns are counted starting from 0 so the 2nd column is Column(1).
This has to refer to the Row Source of the listbox. You mentioned a
table... is this the row source of the listbox? If so, is the email
address column included in the listbox? Among other things, this means
that the listbox's Column Count property has to be set to 2. The
answer I gave is correct, and if it doesn't work it's because it isn't
set up correctly.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Thanks! I've rechecked your instructions about 12 times
and all rowsourse information and column count numbers are
set correctly (correct report name, listboxname, column 1)
but the email still does not contain the individual in
the "To" box. As it's blank, it seems to me that the macro
is losing the unique name from the form after the report
is generated? If so how would I prevent that activity? BTW

The db structure would have to be OK else either the

report or macro would implode which they do not. I've also
had a couple of MSAccess folks look at the thing and all
agree the db structure is fine/correct - they just don't e-
mail objects. So...Any other thoughts?

 

[Steve Schapel]

RDM,

Please post back with full details of the macro you are using. Is
this macro activated via an event on the same form that the listbox is
located on? There must be a simple explanation for this peculiar
behaviour.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Got it! Or at least most of the issue: It turns out the
rowsource was "bound" to the listbox and the table with
the names themselves was a "link" to an xls sheet. By
including the xls sheet as a table v link and simply using
the rowsource as the table, the person's name now shows up
in the "To" email box. The only remaining issue is Access
for some reason now wants to use a simi-colon to separate
1st and last names instead of a comma as is in the name
table. Any ideas on this latest???

 

[Ken Snell]

Steve is taking a well earned vacation....I'll see if I can complete his
worthy assistance!

As I understand, you're now getting the email address in the To box, but the
first and last names are separated by semicolons, not commas? Why are there
first and last names in the email address?

Can you post the actual RowSource info that you have for the listbox, and a
sample record from the Row Source table/query?

 

[Guest]

The RowSource type = Query/Table and Sample Record =
Smith, Jane. Within the internal Outlook e-mail this would
be the normal address. To add to the mystery, the macro
sends the first person on the list perfectly - last name -
comma - first name; everyone after that = last name -
simicolon - first name. Many thanks for the offer of help
as I guess I wore the other person out!

 

[Guest]

Stop the presses: I've detrmined that anyone with more
than two names in the "name field" from the list box
rowsource is the issue. i.e.> "Jones, Mary" sends
perfectly; "Jones, Mary T" does not. Apparently Acess is
reading the letter "T" as a word and placing a semicolon
after the "Mary" intstead of leaving the space. Your
solution is very welcome!

 

[Guest]

The problem is in the list separator default for Access -
it's a semicolon. Can/how is this changed within Access or
within a table/macro/query/form/format within the db?

 

[Ken Snell]

The other guy didn't get worn out...he's on vacation.

I don't know of a way to change the list separator within ACCESS. ACCESS
uses either a semicolon or the "list separator" character that is defined in
the Regional Settings in Windows.

You might try concatenating the first name and the middle initial with an _
character before you put it in the SendObject TO list? Other than that, I
don't have a suggestion; sorry!

 

[Guest]

You've got to be kidding me! After all this work you're
telling me that Access simply cannot not read a basic name
from a table defined as text (last, first mid ini) without
putting a semicolon after the first name? Huh? I don't
understand why it will not put the defined name into the e-
mail because it is a record. There has to be a work-
around....how about some VB code or another idea?

 

[Ken Snell]

That is not the question that you asked, nor the one that I answered.

Yes, ACCESS can read a text string and insert it into the SendObject's To
property. As I note from your earlier posts, you're using a listbox as the
source of the email name. So, try using this expression in your To box:

=CStr$([Forms]![NameOfYourForm]![NameOfListbox].[Column](1)) & ";"

I am not using A95, so let us know if this works for you.