Trigger Form Email Help

[rthom9]

Hello,



I apologize if this has been answered elsewhere. I saw similar questions, but none for what I need. I am working on moving a paper form used by employees into a digital form. I am trying to set it up so that when an employee submits the form, it automatically triggers an email to be sent to their manager. I have two tables, one with the form information and one that houses information on each employee, including who their manager is. I then created a query that concatenates the managers name into a standard email format.

I know that you can use EmailDatabaseObject as an onclick event, but this requires that you type in the email address that the form should be sent to. My company is quite large and many employees would not know the managers email. What I would like to do is have the form automatically send to the manager's email that is associated with the employee (housed in the query mentioned above). I've searched all over for an answer to this and I'm comin up blank. I appreciate any help.

Thank you.

[June7]

EmailDatabaseObject is a macro action?

Use VBA and SendObject or Outlook automation. Common topic, many threads.

[rthom9]

Sorry, I'm a little new to VBA. Maybe I was just searching with the wrong keywords. It still looks like I would have to specify the recipient in the code? Or should I just put the [mgremail] column in the "To" area of the code?

[Micron]

and one that houses information on each employee, including who their manager is

That's where the email addresses for both should be.

I seem to recall that Outlook address books could contain such associations, but I wouldn't want to go down the road of mining Outlook address books from Access. Besides, I suspect your company hasn't employed that feature.

[rthom9]

We do have that feature, but I wanted to avoid it. So the email address for managers should be housed in the table? I'm still not clear on how to associate this in the VBA code. From what I'm reading up on, it looks like you have to type them all into the code... Sorry if this is a noob question.

[Micron]

No, you'd enter these into the table and associate them with the respective employees. You of course need to know who is processing that form and when the code assigns that person's email to the To box, the code also grabs the manager email by looking up the manager's email that relates to that employee. A DLookup is one way to grab the manager email, or you can build a query for your form that includes the manager email field on the form (doesn't have to be visible). You them reference the form control(s) for the address(es).

[rthom9]

Okay, so let's say that the manager's email is in the same row as the employee's information. Would that be enough to associate them, or would it require a DLookup? For instance, if I wanted it sent to John Smith's manager, and his manager's information is in the same row as his other information, would the code then pull that email?

[rthom9]

Nevermind. I'm sorry; my brain is mush right now.

[Micron]

one additional consideration: if a person can have 2 managers (at least as far as this procedure is concerned) then you need a separate table for managers. Otherwise, you'd nave to repeat the empl details; once for each manager. Duplication like this is to be avoided. tblManagers would repeat only the PK from tblEmployees (as a foreign key in tblManagers) so that emplID 25 would show twice in tblMangers (assuming 2 managers). Maybe I didn't need to tell you that, but just covering the bases, so to speak. If you think this situation might one day arise, then think about setting it up that way now - especially if you want to keep historical data. If you look 5 years back and want to know which manager was in the loop, editing that manager data will erase it without this secondary table.

[rthom9]

There are different levels of managers, but the employee only has one (Then the manager has a manager). I was thinking that I would have the direct manager on the employee info table and then a separate table that lists the manager's manager. I do plan to save each forms on the table that houses the form data so that you can always go back and view, but there shouldn't be a situation where I will need to go back in an historical context since the manager will have a part of the form to complete as well (so the old manager would show up on the old form).

[Minty]

If an employee can only have one manager, then you only need a ManagerID field in the employee record, which you store the EmpId of the manager in. Self refrencing the Employee table.
No need for the second table.

[rthom9]

Even if it would sometimes need to go to the manger's manager? We require second level manager review if the form is rejected.

[Micron]

Apply the same decision to manager-manager as you would to employee manager - either one table for general manager (or whatever you call a manager's manager) or just GM name/email in tblManagers. Your additional requirement now does require the additional table as I first suggested. Without this requirement, then no you didn't really need it. That's why I suggested to consider it. Doing so would not harm anything if it's never needed. Change plans later and you'll probably wish you did as you'll find yourself redesigning forms/queries/reports to a greater degree.

Ever hear the expression that a stitch in time saves nine?

[Minty]

Lets say your employee table is like this ;

tblEmployees
EmpID	FirstName	LastName	JobTitle	ManagerID
1	Commodore	Stone	Commodore	0
2	James T	Kirk	Captain	1
3	Mr	Spock	First Officier	2
4	Montgomery	Scott	Chief Engineer	2
5	Leonard	McCoy	Doctor (Bones)	2
6	Nyota	Uhuru	Comms Officer	3
7	Christine	Chapel	Medic Nurse	5
8
9

You can still find a managers manager, by simply querying the Manager EmpID Of the manager.
So Nurse Chaples manager is Bones
Bones Manager is Capt Kirk
Capt Kirks Manager is Commodore Stone.
Commodore Stone is God, and has no manager.

This keeps working no matter how many layers you have, unless somehow someone is deemed to have 2 managers , which as Micron suggests would need the second table.

[rthom9]

Okay, so here's what I have so far. I have one table "tblTMInfo" that houses employee name/id and job, as well as the manager's ID. I then have table "tblMgrInfo" that houses Manager Id, Manager Name, Manager email, Level 2 Manager ID, Level 2 Manager Name, Level 2 Manager email. I then made a query that links Mgr ID from both tables, as well as all info on employees and subsequent management info. I've made a form that contains all the info from this query.

If I have understood correctly, I then need to set a control with VBA docmd.sendobject in which "MgrEmail" is the "mailto:" option. Is this correct?