Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Email VBA
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Email VBA
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 "Matt" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create an email macro to auto fill the email? | Excel Discussion (Misc queries) | |||
auto email generator | Excel Discussion (Misc queries) | |||
Auto generate email | Excel Discussion (Misc queries) | |||
Auto Email | Excel Discussion (Misc queries) | |||
Auto Email from Excel doesn't appear in Sent Items | Excel Discussion (Misc queries) |