VBA to create a report for each person listed in column

[sej2727]

Hello, first post. Will appreciate any help - even just a pointer to the correct place to look.
I'm struggling to word this requirement but here goes:

I have a data table in excel (a proper table). Each record contains a student and several other fields of use. It also details the teacher, and their email address (separate fields). I want to run a macro that collates all the records just for each teacher and sends this to them in an email (ideally as a pdf attachment).

Notes:
I want to send all columns (I have used power query to slim it down to exactly what I want to send so no need to remove any columns).
The numbers of students to teachers varies. Some teachers only have one student, some teachers have about 50.
I am using a proper excel table so efficient and adaptable referencing in VBA is required. (listobjects and header names?)

example of the data:

Student First Name	Student Last name	Class	Teacher	Teacher email	Date of most recent meeting	Form	Score	etc
Bloggs	Joe	Gladiators	James B	jamesb@email.	10/10/2017	Seers	125	other stuff
Killy	Esspace	Gladiators	Justin S	justins@email.	2/2/2020	King	101	other stuff

Hopefully you get the picture. Looking for James B and Justin S to receive an email with PDF attachment with only their students' info

Ideally sorted by Class, then Last name.

My greatest thanks for looking!
Sej

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet with a little more data. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here (de-sensitized if necessary).