Small script that reacts from a button press in outlook when an email is selected. I've described the process below.
Approximately 10 emails received each month containing a company name in the subject line and a zip file attached.
I want to highlight all ten emails in the inbox and hit a button which triggers the following process
1. Each email is forwarded, its subject line and contents (except zip file) are stripped and replaced with a standard template.
2. Template includes the following dynamic fields: a company name in the subject line and in the body, an employee name (up to two) at the top of the email, a To address (up to 2) and a CC address and the zip file from the original email.
3. The dynamic content lookup table can be in excel, or in a list somewhere that is easily editable for me (no preference where). It will contain the following structure:
- Company name column A
- Employee name 1 column B
- Employee name 2 column C
- To email address 1 in column D
- To email address 2 in column E
- CC email address in column F
4. The forwarded documents are then left open for me to QA and I will complete the process by hitting send manually
5. A popup box displays emails where no match was found in the lookup table
6. If two names exist the word "and" is placed in between their name in the template.
* Only the company name is required to make a match and open up a forwarded email. If no other information exists the email will contain the standard template but no name information.
* Emails are not automatically sent - requires a final "human" click on the send button.
* Location of lookup file (if) outside outlook is static
* The script should be able to deal with internal email addresses such as "Me M Self" - these can be put into the to field as is.
* Script variables for location of files, static text, etc should be easy to find (at top of script for example)
* I can assign the macro/script to a button myself
* Microsoft Office Professional Plus 2007
* Windows XP Service Pack 3
* Example of data lookup table
| Company Name | Name 1 | Name 2 | To 1 | To 2 | CC |
| Bobs Bricks Ltd | John | Mary | john@[url removed, login to view] | mary@[url removed, login to view] | supervisor@[url removed, login to view] |
| Charlies Chalk | Charlie | | charlie@[url removed, login to view] | | |
| Debbies Doodles | Jane | | Jane@[url removed, login to view] | | debbie@[url removed, login to view] |
| Edwards Eggs | Edward | Phil | eddie@[url removed, login to view] | phil@[url removed, login to view] | John J Supervisor |
* Example of original email (lookup value highlighted in blue)
From: An address
Subject: Bobs Bricks Ltd Quarterly Sales Doc Q1
Body: blah blah blah
Attachments: Bobs Bricks Quarterly Sales [url removed, login to view]
* Example of data lookup file (dynamic fields in red)
To: john@[url removed, login to view]; mary@[url removed, login to view]
CC: supervisor@[url removed, login to view]
Subject: Bobs Bricks Ltd Q1 Quarterly Sales Results
Body: John and Mary,
Please find the quarterly sales document for Bobs Brick Ltd attached. Let me know if you have any questions.
Attachment: Bobs Bricks Quarterly Sales [url removed, login to view]