Auto Open file to run Mail macro

[Guest]

I've created a spreadsheet and macro - using the excellent info provided by
Ron de Bruin on his website - to automatically send an email message to
various recipients on dates which are calculated on the spreadsheet.

The next step is to set this up so the file opens every day, the macro runs
and generates emails if appropriate then closes the file.

I thought of doing something like putting the file in the Start menu, naming
the macro Auto_Open and adding some code at the end of the macro to close the
file.

However, I'm concerned that on days a message is due to be sent this could
happen as many times as the computer is booted up plus if the user (not me)
needs to change the dates on the sheet opening the file would run the macro
(and possibly re-send mails) again.

I would be grateful for any suggestions as to how to achieve what I want and
how best to code it.

Many thanks in anticipation

 

[Guest]

A thought -

You could have a hidden sheet living in your document who's only job is
to record instances each day of when you sent your email. Something like
on a Workbook_Open event, have a macro go check the value of a cell for that
day (that keeps getting updated on a Workbook_Close event) ... If the cell
contains
a value, dont send the email (to prevent from spamming over and over for
that day.

Then on a Workbook_Close, if you've already sent for that day, store some
sort
of value there to prevent Workbook_Open from sending out (and check the time
of day), if time of day is, say >5:00pm for example, clear the flag out.

Just an idea,
Chad