Sending an automated email through a nightly job
The query will be executed by sp_send_dbmail asynchronously, on a separate session that has msdb as current database. So you must use three-part names in your query:

@query = N'select substring(PostOfficeBox,1,4) as [Postcode]
...
from TestCRM.dbo.AccountExtensionBase as a
JOIN TestCRM.dbo.CustomerAddressBase as b ON a.AccountId = b.ParentId
...'