It looks that there is a bug in Sql Server 2005 SP1 that renders Sql Server Agent job email notification useless. This notification is enabled through Notification page in job property page. Below is the error message you will see in Sql Server Agent log, even when “Agent XPs” setting is set to 1:

[298] SQLServer Error: 2812, Could not find stored procedure ‘msdb.dbo.xp_sqlagent_notify’. [SQLSTATE 42000] (DisableAgentXPs)

You could still use sp_send_dbmail for notification purposes. SP2 fixes this bug.