SQL 2005 has easier step to send email than SQL 2000 (of course), and hopefully so is SQL 2008. You only need to run the scripts under your msdb database (For example). Please take a look below scripts.

DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)

SET @ProfileName = ‘MyMail’;
SET @AccountName = ‘MyMailAccount’;
SET @SMTPAddress = ‘smtp.mySMTPServer.com’;
SET @EmailAddress = ‘username@myMail.com’;
SET @DisplayUser = ‘The Mail Man’;

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,@account_name = @AccountName,
@sequence_number = 1

After above scripts run well, you could send the email by writing below scripts.

EXEC msdb.dbo.sp_send_dbmail
@recipients=’recipients@myMail.com’,
@body= ‘BODY PERFECT, SEEMS GOOD’,
@subject = ‘My Man’,
@profile_name = ‘MyMail’

If it’s working properly, there will be a message Mail Queued. You also could see the sent status by selecting from tables sysmail_allitems in msdb database.

select sent_status, * msdb.dbo.sysmail_allitems

Have a nice try 🙂