How to send Email from a SQL Job with results of multiple select statements I want to create a SQL job and send an emails with multiple select statements including in query results. Is it possible with SQL Job or should I use SSIS?

Edit 1:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Adventure Works Administrator',  
    @recipients = '****@abc.com', 
    @Execute_query_database = 'AdventureWorks',
    @query = 'SELECT COUNT(*) FROM AdventureWorks.Production.WorkOrder  
                  WHERE DueDate &gt; '2018-03-30'  
                  AND  DATEDIFF(dd, '2004-04-30', DueDate) &lt; 2
              SELECT * FROM NorthWind.Production.Work  
                  WHERE DueDate &gt; '2018-04-30'  
                  ' ,  
    @subject = 'Work Order Count',  
    @attach_query_result_as_file = 1 ;



  Error message: File attachment or query results size exceeds allowable
  value of 1000000 bytes.


Edit 2: I feel that SQL agent job not pointing to desired DB and sp_send_email has no database name. so I added @Execute_query_database = 'AdventureWorks' but still there is no improvement because in SQL SELECTS using two different databases. 

Any thoughts?
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.