Emailing attachments via Excel

Hi there all,

I've recently started a new job and one of my duties is monitoring and reporting Office electrical usage/demand data. I send every office a weekly email with attachments.. normally 3 attachments showing their overall electrical consumption data and drilling down in to different depts etc.

I've found this task to be really repetitive since there are over 20 office locations.. so every week I've to create over 20 emails... add 3 attachments to each and change the office names in the text etc

The only difference between the emails is the office name and the week number. Therefore the only thing that will change every week is the week number. This is the same with the file names and folder names and email subject etc.

If you see my attachment I have set up 3 worksheets, each with an office.
Each office's email recipients will differ.

Everything on each sheet will stay the same every week apart from the week number. So when I update the week number, how would I go about updating the red txt in my attachment... e.g the week number in the subject, and file names and paths etc

How about setting this up so when I hit the Mail button, excel will create an email, fetch the attachments via the file path (can excel do this?) and show the email (MS Outlook or Lotus Notes) ready for me to hit the send button (this will let me review the email and make sure I haven't made any boo boo's lol).

Any pointers would be a great help.

Thanks loads