Hello all

If you remember my past messages, I have an email macro that sends a reminder email out to people if an adjacent column contains the value 5 (as in 5 days remaining until an item is due). Before i started this project, i wanted it to be fully automated. But in review of pros and cons.. making the macro autorun when the workbook is open is not feasable.. Because tons of people open this file daily and it would result in the workbook sending the same email over and over to people. Is there a way to make the workbook only run the macro once a day

Maybe somehow by putting the workbook to auto launch in the background at a certain time using windows scheduler... but i dont know how to make the macro start up during that time only... hm

any ideas? perhaps this is the wrong discussion group to be posting it on.. apologies if it is. But this is the only one i use and its somewhat related to programming since my whole project was based on that macro. heh

Thanks,
Lobo

hm

So what you are saying is.. somewhere in the workbook... have a cell that contains the macro name and the date it was last run.. and then have an auto code... to check

If the date it was last run = today then dont run it again. (do not launch the EmailRoutine sub
If the date it was last run is not equal to today then run it again. (launch the EmailRoutine sub

Hm this would also require me to somehow have the cell containing the last run date change when i run the EmailRoutine sub...ahhh

Sounds simple (somewhat).. but i have no idea where to start... or what to do.

does anyone have any ideas

alas, there is a proble

There are many computers on my network that open this file and i dont want them to send an email out to other people. I want it so that it only works from one computer....hmm maybe this goes beyond excel and is impossible.. thus i mentioned windows scheduler in my first post.

Regards
a confused but happy Lob

Lobo

Here's an idea: Assume your workbook is Emailbook.xls and that your macro
(Sub EmailRoutine()) is in it and is in a standard module. Make a brand new
workbook and call it ScheduleEmail.xls. In ScheduleEmail, create a
Workbook_Open event that opens Emailbook.xls and runs the macro. When the
user opens Emailbook, the macro won't run, but when ScheduleEmail is opened
it will.

To use the Workbook open event, hit Alt-F11 to open the VBE, under your
project in the Project Explorer double click ThisWorkbooks, from the
dropdown boxes select Workbook and Open.

Your Workbook_Open sub may then look like this:

Private Sub Workbook_Open()

Dim wb As Workbook

Set wb = Workbooks.Open("C:\Dick\Emailbook.xls")

Application.Run "Emailbook.xls!EmailRoutine"

wb.Close True

End Sub

Finally, use Windows Scheduler to open ScheduleEmail.xls once per day. You
will probably want to digitally sign your macros so that you don't get a
warning box and so that you don't have to resort to changing your security
settings. See help for how to create a digital signature.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.


"Lobo" wrote in message
...
alas, there is a problem

There are many computers on my network that open this file and i dont want
them to send an email out to other people. I want it so that it only works
from one computer....hmm maybe this goes beyond excel and is impossible..
thus i mentioned windows scheduler in my first post..

Regards,
a confused but happy Lobo