Macro to email specific worksheets to specific recipients

**LeanAccountant** · 02-09-2015, 12:51 PM

I am struggling to find a macro to email specific worksheets as an attachment to specific recipients - can anyone help, please?

On the "email matrix" sheet, column A consists of 50 worksheet names, column B consists of email addresses to use in the To: section of the email and columns C-E contain email addresses of recipients to appear in the CC: - on the "email matrix" sheet, row 1 is a heading row with the data extending to row 51 at the moment (but this could vary as new cost centres are added/deleted).

Is it possible to create either of the following solutions (or both, if you're up for it?):
1) a macro to send 50 emails, each with one sheet as an attachment, sent to the To: address in column B and the CC: address from columns C-E for that particular sheet (e.g. data in row 2 for the first cost centre/sheet)
2) a macro to send one email to each combination of recipients with all the sheets they're due to receive - e.g. if person A, B and C are the recipients for sheets 1, 2, 3, 4, 5 can these sheets be collated into just one email to be sent to person A, B and C, but a separate email for person A, B and D who receive sheets 6, 7 and 8

I sense option 1 is much easier and would probably be OK, but I think some recipients would prefer to just receive one email rather than several emails...

Can anyone help, please?

Thanks

**Leith Ross** · 02-09-2015, 01:16 PM

Hello LeanAccountant,

It is certainly possible. To make creating the code both faster and easier, can you put together a a sample workbook with the matrix sheet and a data sheet?

I realise that is confidential and you will need to change the names in the workbook before you post. Having this as a guide will be a big help when writing the macros.

**LeanAccountant** · 02-10-2015, 05:59 AM

Hi Leith,

Thanks for getting in touch - I've enclosed a very basic example of the "email matrix" (I've put together a basic file, but seem unable to upload it at the moment as the upload tool just hangs..?).

Sheet name	Email recipient - To:	Email recipient - CC: (1)	Email recipient - CC: (2)	Email recipient - CC: (3)
12345	abc@123.com	zyx@123.com	wvu@123.com	qpo@123.com
23456	abc@123.com	zyx@123.com	wvu@123.com
34567	abc@123.com	zyx@123.com	wvu@123.com
45678	def@123.com	zyx@123.com	tsr@123.com	qpo@123.com
56789	efg@123.com	zyx@123.com	tsr@123.com
67890	efg@123.com	zyx@123.com	tsr@123.com	qpo@123.com

Using this example, there are two options I am trying to find a solution for:

Option 1 would be to send one email with sheet 12345 attached to the 4 recipients (one as "to:" and three as "cc:"), which would result in person "abc" receiving three emails, etc... and 6 emails being sent out in total and person "zyx" receiving 6 emails. The subject would be the cost centre number from column A on the "email matrix" sheet, with the body text being generic (e.g. "here is the report for your cost centre").

Option 2 would be to try to group the attachments and emails so multiple sheets can be sent as one attachment if the recipients are the same - in this example, sheets 23456 and 34567 would be grouped together as their distribution list is the same, whereas the other sheets would remain as individual emails as their distribution list is slightly different.
In option 2, it would be nice if recipients receiving more than one sheet could have these collated into one attachment rather than separate attachments.

If any further clarification will be helpful, please let me know.

Thanks