VBA: Attachment Send

[Trishka88]

Dear Tek-Tips,

I have a "pivot table" with course names (column A) and number of attendees(column B).

Column A Column B Column C
Course Title Number of ttendee(s) Course Owner
course A 5 Rey
course B 4 Walter
Course C 12 Jonah

If I click on attendee number (column B) for column A course, this opens a worksheet with the actual attendees. Example: Course A = 20, opens to detail =

sheet1
Ran
Reil
Joy
Peter
Mel

For each course clicked, a worksheet (sheet1, sheet2 ... etc.) appears, with attendees of course row in column A.

What needs to done VBA:
Send these sheets individually, as individual files password protected and attached to a mail merge document to be sent out to course owners (column C)

The mail merge is a letter with merge fields:

Dear <course owner>,

The <course> has <number of attendees> for <course date> at <room>. Attached is the attendee list. Password will be sent on a separate communication.

Thank you,
<sender>
----------------------------

Thank you, let me know if you need further information

 

[kjv1611]

I imagine the best way would be to reference Outlook for the email portion, and in the code, put a loop which loops through the worksheets, saving separately, and applying the security as necessary, and then sending each via the Outlook model... Also, if the email is generally/always the same, you could create an Outlook Mesage Template, and use that for sending to save some

 

[Trishka88]

Thanks!