M
mdicostanzo
We track waste shipments on an Excel spreadsheet. We want to set up
the spreadsheet to send e-mail alerts when we get close to the
shipping date. Our IT department is looking to write a program to do
this, but this will take time and probably end up way more complicated
than it needs to be.
Here is how the spreadsheet is currently set up:
Each waste shipment is entered on a row.
The user enters a start date in column H.
Column N is set up with some nested If(isblank()...) functions to look
at column H and create a Due Date 90 days after the Start Date in
column H.
Column O is set up as the countdown column. It tells the users the
number of days left before the waste must be shipped offsite.
The number of days left in column O is used in conditional formatting
to color code the rows based on days left. For 16-30 days left, the
row is highlighted as blue; for 1-15 days left, the row is highlighted
as yellow; once passed due, the row is highlighted as red. There is
no highlighting for wastes that have more than 31 days to be shipped
offsite.
Column U is the Date Shipped column. Once the waste is shipped out,
Column O changes to "Shipped" and everything is OK. Column O also has
some If(isblank()...) functions that look at column U for a date.
What we would like is to send an e-mail automatically when there are a
certain number of days left before a shipment must be made. When
there are 16-30 days left, we would like an e-mail to be spent with a
simple message to a specified distribution list. When there are 1-15
days left, we would like a different e-mail sent to potentially a
different distribution list. Finally, when the shipment is overdue,
we would like potentially another e-mail sent.
Ideally, we want the e-mail to fire once per day until the waste is
shipped. We would like to easily change the distribution list, and
the subject/body of the e-mail. The e-mail would only go to people
within the company. The computer where the user enters the data has e-
mail connectivity. Can we do this with a macro?
the spreadsheet to send e-mail alerts when we get close to the
shipping date. Our IT department is looking to write a program to do
this, but this will take time and probably end up way more complicated
than it needs to be.
Here is how the spreadsheet is currently set up:
Each waste shipment is entered on a row.
The user enters a start date in column H.
Column N is set up with some nested If(isblank()...) functions to look
at column H and create a Due Date 90 days after the Start Date in
column H.
Column O is set up as the countdown column. It tells the users the
number of days left before the waste must be shipped offsite.
The number of days left in column O is used in conditional formatting
to color code the rows based on days left. For 16-30 days left, the
row is highlighted as blue; for 1-15 days left, the row is highlighted
as yellow; once passed due, the row is highlighted as red. There is
no highlighting for wastes that have more than 31 days to be shipped
offsite.
Column U is the Date Shipped column. Once the waste is shipped out,
Column O changes to "Shipped" and everything is OK. Column O also has
some If(isblank()...) functions that look at column U for a date.
What we would like is to send an e-mail automatically when there are a
certain number of days left before a shipment must be made. When
there are 16-30 days left, we would like an e-mail to be spent with a
simple message to a specified distribution list. When there are 1-15
days left, we would like a different e-mail sent to potentially a
different distribution list. Finally, when the shipment is overdue,
we would like potentially another e-mail sent.
Ideally, we want the e-mail to fire once per day until the waste is
shipped. We would like to easily change the distribution list, and
the subject/body of the e-mail. The e-mail would only go to people
within the company. The computer where the user enters the data has e-
mail connectivity. Can we do this with a macro?