Dug further and found this error:
The server principal "xxxx" is not able to access the database "yyyy" under the current security context.
Where xxxx is the SQL Server Agent account. Added that account specifically with sa privileges and fixed the problem. Weird we had to do that just for jobs that have query results as an attachment because as mentioned all others were working fine.
Thank you for your replies.