|Well, I wouldn't say that rondebruin website has everything that gillkate needs.|
It doesn't address the "The Spreadsheet shall only be opened once a week so I would need that to act as a trigger" or the "for the email to be sent 2 weeks before the actual expiry date" issues.
To have the code run when the workbook is opened, you could use the Workbook_Open event, stored in the ThisWorkbook module. You would want to use a key to prevent duplicate emails from being sent.
For example, one of the last instructions in the code could be to place a date in a cell, e.g. A1. One of the first instructions could be to compare that date to the system date and decide if the email portion of the code should be run or if the macro should exit.
As far as checking the expiry date, you could loop through the "expiry date' column to determine which emails should get sent. By including a column of email addresses in the worksheet, the code could pick up the email address when it finds an "email required" date and use that email address in the .To line in the code.
The rodebruin site certainly has the core of what you need, but any code found there is going to need some serious tweaking.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.