#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 60
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 95
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create an email macro to auto fill the email? Justin[_4_] Excel Discussion (Misc queries) 0 November 14th 07 11:49 PM
auto email generator Brettjg Excel Discussion (Misc queries) 2 May 23rd 07 11:38 PM
Auto generate email Phil P Excel Discussion (Misc queries) 1 June 12th 06 10:45 AM
Auto Email superkopite Excel Discussion (Misc queries) 2 January 25th 06 11:49 PM
Auto Email from Excel doesn't appear in Sent Items WimR Excel Discussion (Misc queries) 1 January 6th 05 01:18 AM


All times are GMT +1. The time now is 07:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"