A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Auto Email VBA



 
 
Thread Tools Display Modes
  #1  
Old December 20th 07, 05:33 PM posted to microsoft.public.excel.misc
Matt
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




Ads
  #2  
Old December 20th 07, 06:25 PM posted to microsoft.public.excel.misc
rdwj
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
>
>
>
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 10: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 10:49 PM
Auto Email from Excel doesn't appear in Sent Items WimR Excel Discussion (Misc queries) 1 January 6th 05 12:18 AM


All times are GMT +1. The time now is 02:57 PM.


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