I’ve seen several threads and videos addressing how to use a macro to send an email from excel using outlook. All good there. However, I’ve got a significantly more difficult task to try to accomplish.
I’m trying to get a button added to my first sheet in the workbook that will search each sheet, and if the date is the same as what is listed in a specific column, send a group of specified emails out.
<tbody>
</tbody>
For example, we’ll take Sheet MS-4. If today’s date is equal to the date listed in one of the 4 columns (B-D) it sends out an email to the recipients in column F. The email subject line is made up of elements from cells G2 through J2 depending on which date was flagged from columns B-E, and the email body will be from Cell H6. Also, once the email is sent, it would flag that the email was sent by placing a Y in the cell below the indicated date, and would not send a second email out for that specific sheet if it has already been sent (i.e. 2 different users open the workbook and run the macro, you don’t send out the same email twice.) So, if today was 4/15/2016, the email would go to all recipients in Column F, and would look like this:
Subject:
2R25 Milestone 4 is Open!
Body:
MS.53 – All Pre-Outage Work Completed
Definition
Implement the pre-outage work schedule such that all pre-outage work is completed prior to the outage start.
Milestone Owner - Maintenance Director
Milestone Dates
-5 months to 0 Months
Performance Indicator
Pre-outage work order tasks yet to be worked compared to the work down curve.
Specific Requirements to Satisfy Milestone
• Pre-outage work is completed prior to the start of the outage.
• Pre-outage work status is tracked and report out on the daily plant status call.
NOTE: Pre-outage work that supports critical path outage activities should be completed one week prior to the outage start, (the intent of the one-week requirement is so that the organization could start this critical path work within 24 hours of a unit trip).
Deliverable
None
I know this is a lot, but I would really appreciate the help. Haven’t done much coding before I started looking at this.
I’m trying to get a button added to my first sheet in the workbook that will search each sheet, and if the date is the same as what is listed in a specific column, send a group of specified emails out.
| 2R25 | 1R28 | 2R26 | 1R29 | E-Mail | Milestone | Opening Subject | Reminder Subject | Closing Subject | |
| Open Date | 5/15/2017 | 11/1/2017 | 11/7/2018 | 5/16/2019 | Milestone 53 | is Open! | Closes in 2 Weeks | Closure Due Today! | |
| Email Sent? | |||||||||
| Reminder Date | 10/1/2017 | 3/18/2018 | 3/22/2019 | 9/28/2019 | |||||
| Email Sent? | Message To Send: | ||||||||
| Close Date | 10/15/2017 | 4/1/2018 | 4/5/2019 | 10/12/2019 | MS.53 – All Pre-Outage Work Completed Definition Implement the pre-outage work schedule such that all pre-outage work is completed prior to the outage start. Milestone Owner - Maintenance Director Milestone Dates -5 months to 0 Months Performance Indicator Pre-outage work order tasks yet to be worked compared to the work down curve. Specific Requirements to Satisfy Milestone • Pre-outage work is completed prior to the start of the outage. • Pre-outage work status is tracked and report out on the daily plant status call. NOTE: Pre-outage work that supports critical path outage activities should be completed one week prior to the outage start, (the intent of the one-week requirement is so that the organization could start this critical path work within 24 hours of a unit trip). Deliverable None | ||||
| Email Sent? |
<tbody>
</tbody>
For example, we’ll take Sheet MS-4. If today’s date is equal to the date listed in one of the 4 columns (B-D) it sends out an email to the recipients in column F. The email subject line is made up of elements from cells G2 through J2 depending on which date was flagged from columns B-E, and the email body will be from Cell H6. Also, once the email is sent, it would flag that the email was sent by placing a Y in the cell below the indicated date, and would not send a second email out for that specific sheet if it has already been sent (i.e. 2 different users open the workbook and run the macro, you don’t send out the same email twice.) So, if today was 4/15/2016, the email would go to all recipients in Column F, and would look like this:
Subject:
2R25 Milestone 4 is Open!
Body:
MS.53 – All Pre-Outage Work Completed
Definition
Implement the pre-outage work schedule such that all pre-outage work is completed prior to the outage start.
Milestone Owner - Maintenance Director
Milestone Dates
-5 months to 0 Months
Performance Indicator
Pre-outage work order tasks yet to be worked compared to the work down curve.
Specific Requirements to Satisfy Milestone
• Pre-outage work is completed prior to the start of the outage.
• Pre-outage work status is tracked and report out on the daily plant status call.
NOTE: Pre-outage work that supports critical path outage activities should be completed one week prior to the outage start, (the intent of the one-week requirement is so that the organization could start this critical path work within 24 hours of a unit trip).
Deliverable
None
I know this is a lot, but I would really appreciate the help. Haven’t done much coding before I started looking at this.
