Outlook - Combine Name from Distribution List & Attach specific files from folder

[Scarecrow181]

Hi Folks
I am trying to write up VBA code that refers to an excel file called 'Distribution_List.xls' to select a player's email address and attach various stat files for that particular player from a specific file path.

A) In excel file called 'Distribution_List.xls':
* Column A has Player Codes
* Column B has Player Email Addresses

Cell A1 has '1001 Michael Jordan'
Cell A2 has '1002 Scottie Pippen'
Cell A3 has '1003 Toni Kukoc'
etc...

Cell B1 has 'm_jordan@yahoo.com'
Cell B2 has 's_pippen@yahoo.com'
Cell B3 has 't_kukoc@yahoo.com'
etc...

B) In file path 'C:\Player Stats' I have the following files:
'1001 Michael Jordan - Offensive Stats.pdf'
'1001 Michael Jordan - Defensive Stats.pdf'
'1002 Scottie Pippen - Offensive Stats.pdf'
'1002 Scottie Pippen - Defensive Stats.pdf'
etc...

QUERY:
I need to write up a code that CREATES an email for EACH player on the 'Distribution_List.xls' and attach specific stat files for that particular player from 'C:\Player Stats'.

Using the above examples (A and B), the first email that needs to be created:

- should be sent to Michael Jordan
To: m_jordan@yahoo.com

- files to be attached for this player would be
'1001 Michael Jordan - Offensive Stats.doc'
'1001 Michael Jordan - Defensive Stats.doc'

And then the code will move onto the next player on the 'Distribution List' and email the next player's stat files, and so on until it reaches the end of the list.

Thanking you in advance! I appreciate any help where possible!

[JP2112]

I would put the stats onto the spreadsheet, then create a mail merge in Word using the Excel worksheet as the source. You can do the merge directly to email.

Otherwise you'd need to write VBA code to loop through the worksheet, figure out which PDF to grab, then create the email and attach the file.

If it's at all feasible to get the stats from the PDF onto the worksheet, I would do that instead.