How to send Email from a SQL Job with results of multiple select statements
You are basically sending 1GB file from sql server which is max. You can reconfigure the default using 


  exec msdb.dbo.sysmail_configure_sp 'MaxFileSize','2000000'-- 2GB


I would say, use bcp out the file, compress it using zip or 7zip and then send using powershell or ssis.