sp_send_dbmail fails when query is included

Question

I am running SQL Server 2022 Enterprise inside a company domain. I have a SQL Agent job that runs a stored procedure. The job runs under a domain account say 'domain\myuser' that is a member of 'sysadmin' on this sql server. It's also the same account that runs SQL Server Agent service.

When I manually run this job under SQL Server Agent in SSMS, I get this error whenever the @query parameter is supplied.

Executed as user: domain\myuser. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050).

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'my_profile',           
    @recipients = 'myemail@xyz.com',    
    @subject = 'Test Send',
    @query = 'SELECT * FROM msdb.dbo.backupfile WHERE 1=0';

If I simply remove @query, it works fine. If I include the @query but logged into SSMS as 'sa' and execute the SP in SSMS, it also works fine. It fails with that error when it's run as an agent job.

I have spent almost the entire day searching and reading but haven't been able to find a solution.

Does anyone have any idea what could be wrong or what other permissions might be needed?

Thanks