Auto email to outlook of worksheets

[Manexcel]

I am running MsExcel 2016 and Outlook 2016
I have a large master worksheet that contains many members names and addresses and contact details, including email address.
Members are in different groups (300) and members in a group are linked by a common ‘secretary’.
e.g.

• 30 members are associated to group 1 and have a common secretary called sec 1
• 50 members are associated to group 2 and have a common secretary called sec 2
• 15 members are associated to group 3 and have a common secretary called sec 3
• And so on...

On a regular basis I email each secretary with their members names and addresses and contact details to ensure nothing has changed.

I currently format the master worksheet that contains ALL members / groups with;

• Orientation – landscape
• Margins – narrow
• Font –
• Print titles – Headers and footers/ rows to repeat at top of each page
• Tracking details

I then filter on each group and copy paste and manually email to each secretary their particular groups member details. Obviously, this takes a lot of time!

Is there a procedure whereby I use the data in the master worksheet to;

1. create an Individual worksheet for each group secretary
2. keeps all the formatting setup in the Master worksheet
3. create an email automatically with worksheet attachment which contains the group’s members details with an option to include “subject text” (which is common) and other text to be held within the body of the email (which is common)
4. Attach other files e.g. PDF file if possible

So if I wish to contact 50 of the 300 groups
I automatically send 50 emails with the data (worksheet attachment) relevant to that group which retains the master worksheet formatting with the option of attaching other files (if possible)

I trust the above assists and thank you for your time and consideration.