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?

Hi M,

Ron de Bruin has an extensive range of sample email code at:

Example Code for sending mail from Excel
http://www.rondebruin.nl/sendmail.htm

Perhaps, for example, you could adapt the code at:

Mail a message to each person in a range
http://www.rondebruin.nl/mail/folder3/message.htm.

Try replacing the 'yes' condition with a reference to your
countdown column.


---
Regards,
Norman


wrote in message
oups.com...
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?