As you may or may not know, you can set up SQL Server Agent to email you whenever a job fails. This however requires SQL Mail to be properly working. Again, as you have probably experience this is a lot easier said than done. What I have ended up doing is using XPSMTP for sending email notifications to me whenever a job fails. This does require a little bit of extra work in the job definition. You must create a new step to be run only if that job step fails, this however isn't much of a problem if you don't have a lot of jobs defined. Below is the procedure that I use for sending out the email. This procedure requires you to send in the job_id, which you will need to pull out of the msdb.dbo.sysjobs[job_id]. For more information about the system tables Dave's last post.

/*
 * BE SURE TO CHANGE THE MAIL SERVER
 * AND EMAIL ADDRESSES TO THE ADDRESSES
 * YOU WANT THE NOTIFICATIONS TO BE
 * DELIVERED TO.
 *
 * IF YOU DO NOT CHANGE THE ADDRESSES
 * YOU WILL END UP TELLING ME ALL OF YOUR
 * DEEPEST DARKEST SECRETS!!!! ;)
 * 
 */

CREATE PROCEDURE dbo.sp_SendJobAlert
 @job_id UNIQUEIDENTIFIER
AS

DECLARE @FROM NVARCHAR(4000),
  @FROM_NAME NVARCHAR(4000),
  @TO NVARCHAR(4000),
  @Subject VARCHAR(4000),
  @Message VARCHAR(4000),
  @priority NVARCHAR(10),
  @rc INT

SELECT TOP 1
  @subject = '[' + hist.server + '] JOB FAILED: ' + hist.step_name,
  @message = hist.message
FROM msdb.dbo.sysjobhistory hist
WHERE job_id = @job_id AND step_id <> 0 AND run_status = 0
ORDER BY instance_id DESC

SELECT @FROM = N'sql@aaronweiker.com',
  @FROM_NAME = N'SQL Server',
  @TO = N'alerts@aaronweiker.com',
  @priority = N'HIGH'

EXEC @rc = master.dbo.xp_smtp_sendmail
  @FROM = @FROM,
  @TO = @TO,
  @priority = @priority,
  @subject = @subject,
  @message = @message,
  @server = N'mail.aaronweiker.com


RETURN @rc