Use sp_send_dbmail to send multiple file attachments
-- I found something interesting if you ever want to send multiple reports using sp_send_dbmail
-- You have to first set the rowcount to 0 (zero).
-- There can't be a space after the semi-colon that separates the full pathnames for the reports.
SET ROWCOUNT 0
@profile_name = 'Your_Server_Profile',
@recipients = 'email@example.com; firstname.lastname@example.org',
@body = 'Reports 1 and 2 are attached',
@file_attachments = 'C:\Report Folder\Report1.TXT;C:\Report Folder\Report2.TXT',
@subject = 'Reports 1 and 2 are attached' ;
-- I did not see anything about this in BOL