Automatically insert Email address into TO field

[dwalsh77]

I have a table with a number of companies, each of which have a EmailAddress
field. I have a report that when previewed, automatically sends the report as
an attachment to a email recipient. At this moment, I have a macro set to
execute the SendObject action which send the report in HTML format (for
example), and automatically inserts a standard Subject, CC, BCC, etc. At this
moment, the user who is sending the report has to fill in the email address
in the TO Box, BUT, I would prefer to have Access find the email address from
the table and automatically insert it into the TO field for me.
Under the SendObject action of the macro, in the TO option, I have been
leaving it blank, and then have tried things like =[Companies.EmailAddress]
and so on, but nothing works.
Any suggestions?

 

[Barry Gilbert]

How are you calling the macro? From a form? Somehow you need to get at the
email address value from the report's underlying data. If you are calling it
from a form (the preferred method), you could reference the form's email
address field.

We need more information about how you're calling the report and where the
data comes from.

 

[dwalsh77]

I have a main form from which I am selecting a company (company table). Based
on the company selected from the table, when I preview the form, it opens the
report and fills in the appropriate information from that particular company
from the company table (i.e. Name, address, town, etc).
So I guess that in the macro, which is being called from the Main Form, that
this is the starting point for selecting the company information.

Possible?

Sorry about the confusion.

How are you calling the macro? From a form? Somehow you need to get at the
email address value from the report's underlying data. If you are calling it
from a form (the preferred method), you could reference the form's email
address field.

We need more information about how you're calling the report and where the
data comes from.

I have a table with a number of companies, each of which have a EmailAddress
field. I have a report that when previewed, automatically sends the report as
an attachment to a email recipient. At this moment, I have a macro set to
execute the SendObject action which send the report in HTML format (for
example), and automatically inserts a standard Subject, CC, BCC, etc. At this
moment, the user who is sending the report has to fill in the email address
in the TO Box, BUT, I would prefer to have Access find the email address from
the table and automatically insert it into the TO field for me.
Under the SendObject action of the macro, in the TO option, I have been
leaving it blank, and then have tried things like =[Companies.EmailAddress]
and so on, but nothing works.
Any suggestions?

 

[Barry Gilbert]

If you have the email address as a field in the form, which it sounds like
you should, you can refer to this in your macro. Simply enter:
=Forms!MyFormName!EmailAddress
in the To field of the SendObject command in the macro. (Obviously you need
to substitute your own form and field names.). Don't forget the equal sign!

Barry

I have a main form from which I am selecting a company (company table). Based
on the company selected from the table, when I preview the form, it opens the
report and fills in the appropriate information from that particular company
from the company table (i.e. Name, address, town, etc).
So I guess that in the macro, which is being called from the Main Form, that
this is the starting point for selecting the company information.

Possible?

Sorry about the confusion.

How are you calling the macro? From a form? Somehow you need to get at the
email address value from the report's underlying data. If you are calling it
from a form (the preferred method), you could reference the form's email
address field.

We need more information about how you're calling the report and where the
data comes from.

I have a table with a number of companies, each of which have a EmailAddress
field. I have a report that when previewed, automatically sends the report as
an attachment to a email recipient. At this moment, I have a macro set to
execute the SendObject action which send the report in HTML format (for
example), and automatically inserts a standard Subject, CC, BCC, etc. At this
moment, the user who is sending the report has to fill in the email address
in the TO Box, BUT, I would prefer to have Access find the email address from
the table and automatically insert it into the TO field for me.
Under the SendObject action of the macro, in the TO option, I have been
leaving it blank, and then have tried things like =[Companies.EmailAddress]
and so on, but nothing works.
Any suggestions?

 

[dwalsh77]

A quick correction to the previous post.
From my main form, I am only selecting the company name from a list, then
previewing the report which fills in the information on the report such as
Fname, LName, Address, etc. So in actual fact the EmailAddress field is not
called on or displayed on that main form.
I tried your reply, and even though I know what you are saying is indeed
correct, it's still not working properly. Is it possible to call the Company
Table directly or something like that so that the selected comapny's email
appears in the TO box?

Thanks again,

If you have the email address as a field in the form, which it sounds like
you should, you can refer to this in your macro. Simply enter:
=Forms!MyFormName!EmailAddress
in the To field of the SendObject command in the macro. (Obviously you need
to substitute your own form and field names.). Don't forget the equal sign!

Barry

I have a main form from which I am selecting a company (company table). Based
on the company selected from the table, when I preview the form, it opens the
report and fills in the appropriate information from that particular company
from the company table (i.e. Name, address, town, etc).
So I guess that in the macro, which is being called from the Main Form, that
this is the starting point for selecting the company information.

Possible?

Sorry about the confusion.

How are you calling the macro? From a form? Somehow you need to get at the
email address value from the report's underlying data. If you are calling it
from a form (the preferred method), you could reference the form's email
address field.

We need more information about how you're calling the report and where the
data comes from.

:

I have a table with a number of companies, each of which have a EmailAddress
field. I have a report that when previewed, automatically sends the report as
an attachment to a email recipient. At this moment, I have a macro set to
execute the SendObject action which send the report in HTML format (for
example), and automatically inserts a standard Subject, CC, BCC, etc. At this
moment, the user who is sending the report has to fill in the email address
in the TO Box, BUT, I would prefer to have Access find the email address from
the table and automatically insert it into the TO field for me.
Under the SendObject action of the macro, in the TO option, I have been
leaving it blank, and then have tried things like =[Companies.EmailAddress]
and so on, but nothing works.
Any suggestions?

 

[Barry Gilbert]

You have a list of comapny names in a Listbox or ComboBox? That control's
rowsource is either a query, a table, or a SQL string. In either case, you
can add the email address as a column in the control. If the emailaddress is
not contained in the company table, you'll need to do a join. In the control,
you can hide the email column by using the control's Column Widths property.
Then in your macro, refer to the value in the column with something like:

=Forms!MeFormName!cboCompanies.Column(1)

Column 1 is the second column; column numbers are zero-based.

Barry

A quick correction to the previous post.
From my main form, I am only selecting the company name from a list, then
previewing the report which fills in the information on the report such as
Fname, LName, Address, etc. So in actual fact the EmailAddress field is not
called on or displayed on that main form.
I tried your reply, and even though I know what you are saying is indeed
correct, it's still not working properly. Is it possible to call the Company
Table directly or something like that so that the selected comapny's email
appears in the TO box?

Thanks again,

If you have the email address as a field in the form, which it sounds like
you should, you can refer to this in your macro. Simply enter:
=Forms!MyFormName!EmailAddress
in the To field of the SendObject command in the macro. (Obviously you need
to substitute your own form and field names.). Don't forget the equal sign!

Barry

I have a main form from which I am selecting a company (company table). Based
on the company selected from the table, when I preview the form, it opens the
report and fills in the appropriate information from that particular company
from the company table (i.e. Name, address, town, etc).
So I guess that in the macro, which is being called from the Main Form, that
this is the starting point for selecting the company information.

Possible?

Sorry about the confusion.

:

How are you calling the macro? From a form? Somehow you need to get at the
email address value from the report's underlying data. If you are calling it
from a form (the preferred method), you could reference the form's email
address field.

We need more information about how you're calling the report and where the
data comes from.

:

I have a table with a number of companies, each of which have a EmailAddress
field. I have a report that when previewed, automatically sends the report as
an attachment to a email recipient. At this moment, I have a macro set to
execute the SendObject action which send the report in HTML format (for
example), and automatically inserts a standard Subject, CC, BCC, etc. At this
moment, the user who is sending the report has to fill in the email address
in the TO Box, BUT, I would prefer to have Access find the email address from
the table and automatically insert it into the TO field for me.
Under the SendObject action of the macro, in the TO option, I have been
leaving it blank, and then have tried things like =[Companies.EmailAddress]
and so on, but nothing works.
Any suggestions?

 

[dwalsh77]

On the main form, I have a control (combo box) called Comapny which is used
to select a company name. The source of the control (combo box) is
SELECT [Company Lov].CustomerID, [Company Lov].CompanyName FROM [Company
Lov];
-- Company LOV is a query which is retrieving info from the Company Table.
The Column Count is 2 with the Bound column 1.
I tried modifying the Company LOV query to retrieve the EmailAddress which
worked fine in the query itself.
I then changed the source for the Company combo box to:
SELECT [Company Lov].CustomerID, [Company Lov].CompanyName, [Company
Lov].EmailAddress FROM [Company Lov];
Then in the macro itself, I tried
=[Forms]![Tradshow Information]![Company].[EmailAddress] but it still would
not work. I am not sure about the column issue.

Sorry about this.
Any more suggestions?

You have a list of comapny names in a Listbox or ComboBox? That control's
rowsource is either a query, a table, or a SQL string. In either case, you
can add the email address as a column in the control. If the emailaddress is
not contained in the company table, you'll need to do a join. In the control,
you can hide the email column by using the control's Column Widths property.
Then in your macro, refer to the value in the column with something like:

=Forms!MeFormName!cboCompanies.Column(1)

Column 1 is the second column; column numbers are zero-based.

Barry

A quick correction to the previous post.
From my main form, I am only selecting the company name from a list, then
previewing the report which fills in the information on the report such as
Fname, LName, Address, etc. So in actual fact the EmailAddress field is not
called on or displayed on that main form.
I tried your reply, and even though I know what you are saying is indeed
correct, it's still not working properly. Is it possible to call the Company
Table directly or something like that so that the selected comapny's email
appears in the TO box?

Thanks again,

If you have the email address as a field in the form, which it sounds like
you should, you can refer to this in your macro. Simply enter:
=Forms!MyFormName!EmailAddress
in the To field of the SendObject command in the macro. (Obviously you need
to substitute your own form and field names.). Don't forget the equal sign!

Barry

:

I have a main form from which I am selecting a company (company table). Based
on the company selected from the table, when I preview the form, it opens the
report and fills in the appropriate information from that particular company
from the company table (i.e. Name, address, town, etc).
So I guess that in the macro, which is being called from the Main Form, that
this is the starting point for selecting the company information.

Possible?

Sorry about the confusion.

:

How are you calling the macro? From a form? Somehow you need to get at the
email address value from the report's underlying data. If you are calling it
from a form (the preferred method), you could reference the form's email
address field.

We need more information about how you're calling the report and where the
data comes from.

:

I have a table with a number of companies, each of which have a EmailAddress
field. I have a report that when previewed, automatically sends the report as
an attachment to a email recipient. At this moment, I have a macro set to
execute the SendObject action which send the report in HTML format (for
example), and automatically inserts a standard Subject, CC, BCC, etc. At this
moment, the user who is sending the report has to fill in the email address
in the TO Box, BUT, I would prefer to have Access find the email address from
the table and automatically insert it into the TO field for me.
Under the SendObject action of the macro, in the TO option, I have been
leaving it blank, and then have tried things like =[Companies.EmailAddress]
and so on, but nothing works.
Any suggestions?

 

[Barry Gilbert]

Company.EmailAdress won't work. You changed the combo's rowSource correctly.
Now the email address is the fourth column in the combo. The macro should
refer to it like this:
=[Forms]![Tradshow Information]![Company].Column(3)

Barry

You have a list of comapny names in a Listbox or ComboBox? That control's
rowsource is either a query, a table, or a SQL string. In either case, you
can add the email address as a column in the control. If the emailaddress is
not contained in the company table, you'll need to do a join. In the control,
you can hide the email column by using the control's Column Widths property.
Then in your macro, refer to the value in the column with something like:

=Forms!MeFormName!cboCompanies.Column(1)

Column 1 is the second column; column numbers are zero-based.

Barry

A quick correction to the previous post.
From my main form, I am only selecting the company name from a list, then
previewing the report which fills in the information on the report such as
Fname, LName, Address, etc. So in actual fact the EmailAddress field is not
called on or displayed on that main form.
I tried your reply, and even though I know what you are saying is indeed
correct, it's still not working properly. Is it possible to call the Company
Table directly or something like that so that the selected comapny's email
appears in the TO box?

Thanks again,

If you have the email address as a field in the form, which it sounds like
you should, you can refer to this in your macro. Simply enter:
=Forms!MyFormName!EmailAddress
in the To field of the SendObject command in the macro. (Obviously you need
to substitute your own form and field names.). Don't forget the equal sign!

Barry

:

I have a main form from which I am selecting a company (company table). Based
on the company selected from the table, when I preview the form, it opens the
report and fills in the appropriate information from that particular company
from the company table (i.e. Name, address, town, etc).
So I guess that in the macro, which is being called from the Main Form, that
this is the starting point for selecting the company information.

Possible?

Sorry about the confusion.

:

How are you calling the macro? From a form? Somehow you need to get at the
email address value from the report's underlying data. If you are calling it
from a form (the preferred method), you could reference the form's email
address field.

We need more information about how you're calling the report and where the
data comes from.

:

I have a table with a number of companies, each of which have a EmailAddress
field. I have a report that when previewed, automatically sends the report as
an attachment to a email recipient. At this moment, I have a macro set to
execute the SendObject action which send the report in HTML format (for
example), and automatically inserts a standard Subject, CC, BCC, etc. At this
moment, the user who is sending the report has to fill in the email address
in the TO Box, BUT, I would prefer to have Access find the email address from
the table and automatically insert it into the TO field for me.
Under the SendObject action of the macro, in the TO option, I have been
leaving it blank, and then have tried things like =[Companies.EmailAddress]
and so on, but nothing works.
Any suggestions?

 

[dwalsh77]

Excellent, finally got it to work. Made it point to Column(2) instead.

Thanks a lot for your help. You certainly know your stuff.

Company.EmailAdress won't work. You changed the combo's rowSource correctly.
Now the email address is the fourth column in the combo. The macro should
refer to it like this:
=[Forms]![Tradshow Information]![Company].Column(3)

Barry

You have a list of comapny names in a Listbox or ComboBox? That control's
rowsource is either a query, a table, or a SQL string. In either case, you
can add the email address as a column in the control. If the emailaddress is
not contained in the company table, you'll need to do a join. In the control,
you can hide the email column by using the control's Column Widths property.
Then in your macro, refer to the value in the column with something like:

=Forms!MeFormName!cboCompanies.Column(1)

Column 1 is the second column; column numbers are zero-based.

Barry

A quick correction to the previous post.
From my main form, I am only selecting the company name from a list, then
previewing the report which fills in the information on the report such as
Fname, LName, Address, etc. So in actual fact the EmailAddress field is not
called on or displayed on that main form.
I tried your reply, and even though I know what you are saying is indeed
correct, it's still not working properly. Is it possible to call the Company
Table directly or something like that so that the selected comapny's email
appears in the TO box?

Thanks again,

:

If you have the email address as a field in the form, which it sounds like
you should, you can refer to this in your macro. Simply enter:
=Forms!MyFormName!EmailAddress
in the To field of the SendObject command in the macro. (Obviously you need
to substitute your own form and field names.). Don't forget the equal sign!

Barry

:

I have a main form from which I am selecting a company (company table). Based
on the company selected from the table, when I preview the form, it opens the
report and fills in the appropriate information from that particular company
from the company table (i.e. Name, address, town, etc).
So I guess that in the macro, which is being called from the Main Form, that
this is the starting point for selecting the company information.

Possible?

Sorry about the confusion.

:

How are you calling the macro? From a form? Somehow you need to get at the
email address value from the report's underlying data. If you are calling it
from a form (the preferred method), you could reference the form's email
address field.

We need more information about how you're calling the report and where the
data comes from.

:

I have a table with a number of companies, each of which have a EmailAddress
field. I have a report that when previewed, automatically sends the report as
an attachment to a email recipient. At this moment, I have a macro set to
execute the SendObject action which send the report in HTML format (for
example), and automatically inserts a standard Subject, CC, BCC, etc. At this
moment, the user who is sending the report has to fill in the email address
in the TO Box, BUT, I would prefer to have Access find the email address from
the table and automatically insert it into the TO field for me.
Under the SendObject action of the macro, in the TO option, I have been
leaving it blank, and then have tried things like =[Companies.EmailAddress]
and so on, but nothing works.
Any suggestions?

 

[Barry Gilbert]

Glad I could help. Good luck.

Excellent, finally got it to work. Made it point to Column(2) instead.

Thanks a lot for your help. You certainly know your stuff.

Company.EmailAdress won't work. You changed the combo's rowSource correctly.
Now the email address is the fourth column in the combo. The macro should
refer to it like this:
=[Forms]![Tradshow Information]![Company].Column(3)

Barry

You have a list of comapny names in a Listbox or ComboBox? That control's
rowsource is either a query, a table, or a SQL string. In either case, you
can add the email address as a column in the control. If the emailaddress is
not contained in the company table, you'll need to do a join. In the control,
you can hide the email column by using the control's Column Widths property.
Then in your macro, refer to the value in the column with something like:

=Forms!MeFormName!cboCompanies.Column(1)

Column 1 is the second column; column numbers are zero-based.

Barry

:

A quick correction to the previous post.
From my main form, I am only selecting the company name from a list, then
previewing the report which fills in the information on the report such as
Fname, LName, Address, etc. So in actual fact the EmailAddress field is not
called on or displayed on that main form.
I tried your reply, and even though I know what you are saying is indeed
correct, it's still not working properly. Is it possible to call the Company
Table directly or something like that so that the selected comapny's email
appears in the TO box?

Thanks again,

:

If you have the email address as a field in the form, which it sounds like
you should, you can refer to this in your macro. Simply enter:
=Forms!MyFormName!EmailAddress
in the To field of the SendObject command in the macro. (Obviously you need
to substitute your own form and field names.). Don't forget the equal sign!

Barry

:

I have a main form from which I am selecting a company (company table). Based
on the company selected from the table, when I preview the form, it opens the
report and fills in the appropriate information from that particular company
from the company table (i.e. Name, address, town, etc).
So I guess that in the macro, which is being called from the Main Form, that
this is the starting point for selecting the company information.

Possible?

Sorry about the confusion.

:

How are you calling the macro? From a form? Somehow you need to get at the
email address value from the report's underlying data. If you are calling it
from a form (the preferred method), you could reference the form's email
address field.

We need more information about how you're calling the report and where the
data comes from.

:

I have a table with a number of companies, each of which have a EmailAddress
field. I have a report that when previewed, automatically sends the report as
an attachment to a email recipient. At this moment, I have a macro set to
execute the SendObject action which send the report in HTML format (for
example), and automatically inserts a standard Subject, CC, BCC, etc. At this
moment, the user who is sending the report has to fill in the email address
in the TO Box, BUT, I would prefer to have Access find the email address from
the table and automatically insert it into the TO field for me.
Under the SendObject action of the macro, in the TO option, I have been
leaving it blank, and then have tried things like =[Companies.EmailAddress]
and so on, but nothing works.
Any suggestions?