Excel - E-mail automatically generated depending on date?

[ShearsJ]

Hi,

I've noticed some similar queries on here and wondered if you could help me with a task i'm trying to achieve.

The attached spreadsheet is based on one I found elsewhere on the internet, and when a cell in the B column has a value above 200 it generates an e-mail based on the data. I've shown it in working form at the top of the sheet, and underneath is a test set of data for what i'm trying (and failing!).

What i'd like to do is change it so that rather than MyLimit = 200 (As it seems to be listed in the Macro/sheet view), it's triggered by any cells in Column C with a date on or before the date the spreadsheet is opened. When this happens i'd like it to say 'Sent' or 'Not Sent' as it does at the top of the page, but in Column D instead (I couldn't work out how to transfer it.

Lastly, when the e-mail is triggered it currently says:

"Hi Tom
The following client has now entered their expiration as of the stated date : 220
Regards,
Jake"

Whearas i'd like the expiry date from Column C placed in there, as well as the Client name just after "The following client, "

I've tried to edit it but so far had no luck, apologies for the lengthy message but hopefully these problems are quite easy to fix, any help greatly appreciated!

Thanks,

Jake

p.s. The next step is to work out how to get this spreadsheet to open automatically every day but I think that's a task for a rainy day