I tried it and it failed:

exec sun_sp_send_mailhtml

'OUTBOUND.SMTP.USI.NET', --SMTP Server

'chemapptabware@sunocoinc.com', --From

'TabWareRTCheck@sunocoinc.com', --To

'', --CC

'', --CCi

'NOTICE: RT TabWare - Missing GL Transactions', --Subject

'select plant, convert(varchar, transaction_date,110) transaction_date, transaction_type

from NEV_TW_PROD.dbo.in_log_material

where plant = ''RT'' and

transaction_date = convert(smalldatetime,convert(varchar, getdate() - 1,101),101) and

transaction_type in (''Cvar'',''Xvar'',''Xrecp'',''Accr'',''Taxes'',''Frght'',''Xinv'') and carrier is null

order by plant, convert(varchar, transaction_date,110), transaction_type'

Here is my results:

[Execute SQL Task] Error: Executing the query "exec sun_sp_send_mailhtml 'OUTBOUND.SMTP.USI.NET', --SMTP Server 'chemapptabware@sunocoinc.com', --From 'TabWareRTCheck@sunocoinc.com', --To '', --CC '', --CCi 'NOTICE: RT TabWare - Missing GL Transactions', --Subject 'select plant, convert(varchar, transaction_date,110) transaction_date, transaction_type from NEV_TW_PROD.dbo.in_log_material where plant = ''RT'' and transaction_date = convert(smalldatetime,convert(varchar, getdate() - 1,101),101) and transaction_type in (''Cvar'',''Xvar'',''Xrecp'',''Accr'',''Taxes'',''Frght'',''Xinv'') and carrier is null order by plant, convert(varchar, transaction_date,110), transaction_type'" failed with the following error: "The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Added "TOP 100 PERCENT" and it worked