send email in access

  • Thread starter Thread starter kms
  • Start date Start date
K

kms

Let's hope I can explain this clearly.

I have a form with a submit button on it. I would like for this button to
send an email to the employee's supervisor notifying them that there is a
document that needs to be approved. I have a table with all employee names,
email addresses and supervisor's name.

I can't seem to figure out the best way to program the button to send the
email to the supervisor without the user having to type the email address in
automatically. The user's name is in the table that is the record source for
the form.

I'm not familiar with coding so the command button was created with the
macro builder. Any help would be greatly appreciated.
 
Sounds like the key thing is to get to the e-mail address of the manager of
the employee. Perhaps this will help:

- Create a query that resolves the e-mail address of the manager of each
employee. This query would have at least two columns, EmployeeID and
ManagerEMailAddress.
- In your macro, where you are using the SendObject action, the value of the
"To" property would be something like

=Nz(DFirst("ManagerEMailAddress","qryEmployeeManagerEMailAdresses",
"EmployeeID = """ & [EmployeeID] & """"))

All of this depends on the actual names of things in your application.
 
I think I understand what you are telling me but my query isn't pulling the
email address.
the table is broken down like this
id, user id, employee first name, employee last name, supervisor, email
address

I need the query to look at the supervior name and find the supervisor's id
or user id and match it to the correct email address I tried several times
but its not working
Kipp Woodard said:
Sounds like the key thing is to get to the e-mail address of the manager of
the employee. Perhaps this will help:

- Create a query that resolves the e-mail address of the manager of each
employee. This query would have at least two columns, EmployeeID and
ManagerEMailAddress.
- In your macro, where you are using the SendObject action, the value of the
"To" property would be something like

=Nz(DFirst("ManagerEMailAddress","qryEmployeeManagerEMailAdresses",
"EmployeeID = """ & [EmployeeID] & """"))

All of this depends on the actual names of things in your application.

kms said:
Let's hope I can explain this clearly.

I have a form with a submit button on it. I would like for this button to
send an email to the employee's supervisor notifying them that there is a
document that needs to be approved. I have a table with all employee names,
email addresses and supervisor's name.

I can't seem to figure out the best way to program the button to send the
email to the supervisor without the user having to type the email address in
automatically. The user's name is in the table that is the record source for
the form.

I'm not familiar with coding so the command button was created with the
macro builder. Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
So far so good. However, now you need a new query that lists UserID and
ManagerEMailAddress for each user. To do this, you need a table that has at
least these columns:

- UserID
- UserEMailAddress
- ManagerUserID

From such a table, let's say it is named tblUsers, you would then build a
new query to list each user's manager's e-mail address. That query would
look like this:

SELECT tblUsers.UserID, tblUsers_Managers.UserEMailAddress AS
ManagerEMailAddress
FROM tblUsers LEFT JOIN tblUsers AS tblUsers_Managers ON
tblUsers.ManagerUserID = tblUsers_Managers.UserID;

Once you have this new query, then this formula will work:

=Nz(DFirst("ManagerEMailAddress","qryEmployeeManagerEMailAdresses", "UserID
= """ & [EmployeeID] & """"))


kms said:
I think I understand what you are telling me but my query isn't pulling the
email address.
the table is broken down like this
id, user id, employee first name, employee last name, supervisor, email
address

I need the query to look at the supervior name and find the supervisor's id
or user id and match it to the correct email address I tried several times
but its not working
Kipp Woodard said:
Sounds like the key thing is to get to the e-mail address of the manager of
the employee. Perhaps this will help:

- Create a query that resolves the e-mail address of the manager of each
employee. This query would have at least two columns, EmployeeID and
ManagerEMailAddress.
- In your macro, where you are using the SendObject action, the value of the
"To" property would be something like

=Nz(DFirst("ManagerEMailAddress","qryEmployeeManagerEMailAdresses",
"EmployeeID = """ & [EmployeeID] & """"))

All of this depends on the actual names of things in your application.

kms said:
Let's hope I can explain this clearly.

I have a form with a submit button on it. I would like for this button to
send an email to the employee's supervisor notifying them that there is a
document that needs to be approved. I have a table with all employee names,
email addresses and supervisor's name.

I can't seem to figure out the best way to program the button to send the
email to the supervisor without the user having to type the email address in
automatically. The user's name is in the table that is the record source for
the form.

I'm not familiar with coding so the command button was created with the
macro builder. Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top