In SSIS, you could use the stored procedure sp_send_dbmail from msdb.
http://msdn.microsoft.com/en-us/library/ms190307(v=sql.105).aspx
There are 2 ways I have used it in an SSIS package.
1. Use sp_send_dbmail to run a query and attach the results without storing them.
Put an Exec SQL task in SSIS, the query runs sp_send_dbmail, and uses the parameters to run a query and attach the results. It does not allow Excel files, so the attachment extension would have to be .csv
http://thebakingdba.blogspot.com/2010/05/files-tricking-excel-with-spsenddbmail.html
2. Create the Excel file and use sp_send_dbmail to attach it.
Create a data flow task to create an excel file - with an OLE DB source and a flat file destination. Then create an Exec SQL task to run sp_send_dbmail and attach the file.
Hope this helps.