Thread: Create Emails from Table

Hi,

I have an Employee table and a Manager Table in my Database. The Employee table shows who their manager is and the Manager table shows the employee's manager's email.

Basically what I want to do is generate one email for each Manager attaching an Excel spreadsheet of that Manager's employee's (direct reports). The body of the email can be static text. In the subject line I would like the the "region" (a field in the Employee Table) and some static text. (ie. South East - Manager Updates)

We are looking to do this weekly, so we can keep our Manager list up to date for employees.

I have over 1,500 managers so we are looking for an automated way to generate this email with the attachment. All managers aren't on the same network, so we can't place this database in a shared location for people to update.

Help!

Originally Posted by shane.albrandt

Help!

I am not sure HELP is a question. How about we break your task down into manageable sections that you can research yourself and then when you get stuck on a specific part you come back here and ask that question.

The Information You Need

First you need to look at how to open a Record Set based on an SQL.

This SQL will need to Join the two tables together on the key that is common between the two tables. I think it might be manager name.

Generally you will need an inner join so that if there is no match then it will not be in the resulting recordset.

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Thus you will get a resulting record set with the managers name alot and in another column each employee he has.

Once you have this RecordSet you will then have to move through it.

RecordSet.movefirst or movenext etc.

Email

Have a look in the internet you will find many many examples of starting outlook and creating an email. Access sometimes is not allowed to automatically send outlook emails and this could be a problem for you. What do you intend to use?


Putting these two things together.

1. Get your recordset
2. move to first record
3. get managers name and email
4. get first employees name and save to a string variable
5. move to next record in a loop adding employee names to the string while manager name does not change
6. when managers name changes add all information to an Outlook emailitem
7. send or save email
8. then repeat the above process for the next managers name