Fun one today X-)
Summary of Excel File
The purpose of the file is to convert paper purchase requests to electronic ones. Entirely.. which means it needs to securely keep track of who approved the request, and whether they approved as a requestor, a manager, etc.
So it's password secured, and can be edited only through a userform. After the requestor enters all the data, they indicate they are the requestor and that they approve, then enter the next person in line to receive the request. Then they click a "Send" button, which automatically:
- Saves the file
- Resolves the intended recipient
- Generates an email with the intended recipient and some pertinant information in the body of the message
- Attaches the request
- Displays the email so the user can view and edit it before sending.
Then the next person opens the email, indicates they approve, and forwards it on in the same way, and so on, until it eventually has all necessary approvals. Then it goes to Purchasing.
Question/Problem
All of the above works fine. But after the user is finished viewing the email, and they actually send the email, I want it to automatically generate another email, if the current user is not the requestor. This second email will go to the requestor, stating that the request has now been routed to so-and-so. I would like it to automatically send this message, without displaying it. This way, requestors can see where their requests are, in terms of getting approvals.
Initially, I'd thought of just having the first email CC the recipient. But the users (Purchasing) don't like that idea because there are a couple problems with security involved there (they don't want the requestor getting the attachment back).
So instead, I need to indicate that a second email needs to be generated and sent after the user makes whatever edits to the first email, and sends it. Then I need to generate the text of the second email somehow, using the data from the Excel spreadsheet.
Help?
Summary of Excel File
The purpose of the file is to convert paper purchase requests to electronic ones. Entirely.. which means it needs to securely keep track of who approved the request, and whether they approved as a requestor, a manager, etc.
So it's password secured, and can be edited only through a userform. After the requestor enters all the data, they indicate they are the requestor and that they approve, then enter the next person in line to receive the request. Then they click a "Send" button, which automatically:
- Saves the file
- Resolves the intended recipient
- Generates an email with the intended recipient and some pertinant information in the body of the message
- Attaches the request
- Displays the email so the user can view and edit it before sending.
Then the next person opens the email, indicates they approve, and forwards it on in the same way, and so on, until it eventually has all necessary approvals. Then it goes to Purchasing.
Question/Problem
All of the above works fine. But after the user is finished viewing the email, and they actually send the email, I want it to automatically generate another email, if the current user is not the requestor. This second email will go to the requestor, stating that the request has now been routed to so-and-so. I would like it to automatically send this message, without displaying it. This way, requestors can see where their requests are, in terms of getting approvals.
Initially, I'd thought of just having the first email CC the recipient. But the users (Purchasing) don't like that idea because there are a couple problems with security involved there (they don't want the requestor getting the attachment back).
So instead, I need to indicate that a second email needs to be generated and sent after the user makes whatever edits to the first email, and sends it. Then I need to generate the text of the second email somehow, using the data from the Excel spreadsheet.
Help?
