Auto Email VBA

[Matt]

This is what I am trying (with little avail) to do:

I would like to write a code for a sheet in a workbook that will
search through a particular column, lets say A, to find any values
greater than 30. Every time it finds a value greater than 30, it
automatically generates an email to inform someone that the value has
gone over 30. Part of the struggle I am having is that the values in
column A are not direct inputs, rather they come from the function in
the cell. The function is the age of the item (=today() - "date for
that item's row, assume it is in col B").

So, for example:

Col A Col B
=today()-B4 4/5/07
=today()-B5 11/10/07
=today()-B6 12/14/07

would generate two emails, one for the items in row 4 and 5.

Further, would this macro run every time the sheet is opened and the
same value is still over 30? Is there a way instead to make it such
that the email is only generated once after that value has gone over
30?

Any help is much appreciated!

Matt

 

[rdwj]

Matt,
One option that you can consider is the "route" functionality in VBA - which
is effectively defining that you send the email to somebody after which they
automatically route it back to you.

The following is example VBA code

ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Delivery = xlOneAfterAnother
.Recipients = EmailAddress
.Subject = "Project Action Register - Outstanding Actions"
.Message = "limited amount of text"
End With
ActiveWorkbook.Route

If you put this in Sub Workbook_Open() in the workbook it will run
everytime. Obviously you need to add some code to determine who you mail it
to and to read if col A is >30. To prevent it from mailing every time,
suggest you add a column with "last mailed" to control this.

rdwj