Trying to send an email with attachment using VBA

Sorry - just realised I posted this in the wrong forum - I will close this and move to the right one!

Hi

I'm not sure whether what I am trying to do is possible, but I'd be grateful for any replies, even if just "you can't do that"! Every month, I need to send out reports to all my heads, which involves exporting some data from Oracle Discoverer to a main document, then using VBA to create a number of separate workbooks, all of which is working fine. However, I then need to email each of the workbooks I've just created to a different person. There are well over 200 of them, so automation is definitely the way to go. My goal is to open each workbook separately from within the main document, add the opened workbook as an attachment to an email and then send that email to the correct recipient. I have got as far as creating the email and adding the attachment just by using the following...

Workbooks.Open Filename:= _
"[full file path and name] School Health.xlsx"
Application.Dialogs(xlDialogSendMail).Show

...but I can't work out how to use VBA to add the details of the recipient (there will be at least one person in the "To" box and one "CC" and then to automatically send the email. Is this even possible? Many thanks for any help anyone can provide.

Apologies if this has been asked before - I did a quick search but couldn't see anything that exactly matches what I am looking for.