Excel to send worksheets as PDF based on info in 1st worksheet

Hi

I have an excel file which has several sheets within it. The first sheet is the contact sheet. The last sheet is my data sheet and all the sheets in between are the reports that I need to email every month. The common value in each of these sheets is the store# (1001 - 1005 in the attached excel example). You will see this code used in sheet one along with email addresses.

The store number is hidden in cells A6 on all the report sheets as the number was needed to do a Vlookup using the data sheet.

Now, what I would like is perhaps a button that I can press which will automatically email each report sheet to the corresponding email in the Contacts sheet. As some do not have an email, only a fax number, I also need the macro to skip over that document as I will need to print it and fax it manually.

Is this possible?

NB: the attached is an small example as there is confidential info in there. I will have about 50 report sheets in the real thing.

Thanks!

Linda

Statement of Monthly Turnover EXAMPLE.xlsx