Hello,
I have a monthly task of sending out records (Access/Excel) to various employees that I think is very cumbersome and hoping can be done automatically. I’ve just recently stumbled upon this site and am amazed at what people can do with Access and Excel. We use Access/Excel 97 here at work and will update to XP sometime next year.
Here’s the scenario of the process:
- I receive an Access database that contain table/s. Tables contains various amount of records (could range from 1 to over 1 million) and various fields but always contain an employee email address, employee area mgr email, and employee regional mgr email.
- I create a table for each of the employee where records belong to them. I name the table “employeeemail_yyyymmdd” where yyyymmdd represents the day it’s created.
- Depending on the instructions given to me, I do one of the following:
- Export each table in Excel (in it’s own workbook) using the table name as the file name. (only if records doesn’t exceed Excel limit)
- Create an Access mdb for each of the table using the table name as the file name.
- Combination of both.
- Then I send each file (Excel/Access) to the employee it belongs to copying their area mgr and regional mgr (when available) and sometime blind copying my supervisor when required and zipping the files with Winzip when it exceeds 1 mb and sending through Outlook.
Sorry for the long detail. Can this be done programatically? If so, how?
Any help will be greatly appreciated.
Thanks!
I have a monthly task of sending out records (Access/Excel) to various employees that I think is very cumbersome and hoping can be done automatically. I’ve just recently stumbled upon this site and am amazed at what people can do with Access and Excel. We use Access/Excel 97 here at work and will update to XP sometime next year.
Here’s the scenario of the process:
- I receive an Access database that contain table/s. Tables contains various amount of records (could range from 1 to over 1 million) and various fields but always contain an employee email address, employee area mgr email, and employee regional mgr email.
- I create a table for each of the employee where records belong to them. I name the table “employeeemail_yyyymmdd” where yyyymmdd represents the day it’s created.
- Depending on the instructions given to me, I do one of the following:
- Export each table in Excel (in it’s own workbook) using the table name as the file name. (only if records doesn’t exceed Excel limit)
- Create an Access mdb for each of the table using the table name as the file name.
- Combination of both.
- Then I send each file (Excel/Access) to the employee it belongs to copying their area mgr and regional mgr (when available) and sometime blind copying my supervisor when required and zipping the files with Winzip when it exceeds 1 mb and sending through Outlook.
Sorry for the long detail. Can this be done programatically? If so, how?
Any help will be greatly appreciated.
Thanks!