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
"nospaminlich" wrote:
> 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
>
>
|
Similar Threads
