There are many reasons to fail the SQL Server job with this error code like -
- Permission issue for the Job owner
- Permissions for the SQL Server Agent account
- Permissions on MSDB and User Datbases
- Issues with the sp_send_dbmail parameters
Open profiler and choose the below events and then run the job.
In our case this was the culprit.
Now fix the code and run the job, which will succeed.