Susanne:
The expression you've cited looks up the value in an E-Mail Address
column in the row of a contacts table where the value of a column ID
equals that of Assigned To, which I assume to be a control on a form.
In your case it sounds like you have the email address itself in a
control on your form, so you should be able to reference that control
directly, e.g.
=Forms![YourForm]![EmailAddress]
This assumes that the value of the EmailAddress control on your form
is the actual address. Its possible, however, that the actual value
might be a numeric value which references a numeric primary key in the
table from which the address is looked up. If you've used the dreaded
lookup field wizard when creating the column in the referencing table
this won't be readily apparent, even in raw datasheet view of the
table (one of the many reasons why the lookup field wizard should be
avoided like the plague). If this is the case the RowSource of the
EmailAddress combo box on your form will return two column, and will
look something like this:
SELECT ContactID, EmailAddress FROM Contacts ORDER BY EmailAddress;
in which case you'd need to reference the second column like so:
=Forms![YourForm]![EmailAddress].Column(1)
The Column property is zero-based, so Column(1) is the second column.
To be honest I'm not totally sure that a reference of this nature
would work in a macro as macros are not generally used by most
developers; we would normally write a VBA function to be used as the
button's On Click event property, or would put the code directly into
its Click event procedure. A common solution when its necessary to
reference an unbound column of a combo box like this in a query etc is
to add a hidden text box, txtEmailAddressHidden say, to the form with
a ControlSource property of:
=[EmailAddress].Column(1)
In your macro you should then be able to reference the hidden text box
in your macro with:
=Forms![YourForm]![txtEmailAddressHidden]
Ken Sheridan
Stafford, England
--
Susanne
Susanne said:
I created an email cmd on one of my forms that produces a single email with
no information in the body of the email since I wanted to key in the info. I
would like for the email to display automatically an email address from a
look up field in my form. I went into properties, then to macros design and
tried to enter VBA code for the look up combo box in the "To" column but I am
getting "parse errors". I am not experienced with VBA.
I would appreciate any help explained as simply as possible.
I guess I should explain how I created the email object in the first place.
To Create A Blank Outlook Email Object, Simply create A new macros. Name the
macros “macroSendEmailToâ€, Place the identifiers in the “Conditions†Column.
EXAMPLE: [Forms]![Workorders by Customer]![Email Alert To]. Then enter
“SendObject†in the Action column. The macros will write the code for the
blank outlook email object.
Next: go to your form where you want the email to appear and after clicking
design mode. Create a new command button using the wizard. The wizard will
allow you to choose your new macros as the action for your new command
button. Do not forget to name your new command button so that you can
identify it later in case you want to make changes.
The only thing left to do is to attach the look up email address field combo
box so that when I select an email address in the LookUp then click the new
email cmd, the selected email address will appear in the email object "To"
field.
I am looking at the Issues Data Base that I found on line and I see that in
the macros it self (in design mode) a small amount of code was placed in the
“TO†field of the macros.
=Nz(DLookUp("[E-Mail Address]","Contacts","ID=" & Nz([Assigned To],0))).
Possibly this can be adjusted to work with the different forms and tables in
my data base.
Thanks anyway.
