Setting up DB Mail on SQL 2005

The following code will set up all the necessary parts for SQL 2005 DBMail. Note that you will need to also enable Database Mail in Surface Area Configuration for Features. Also the variables need to be set to suit the installation.

USE msdb

GO

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

SET @ProfileName = 'MyMailProfile';
SET @AccountName = 'MyMailAccount';
SET @SMTPAddress = 'my.smtp.server.address';
SET @EmailAddress = 'myemail@myorg.com';
SET @DisplayUser = 'My Real Name';

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 ;

To send an email :

EXEC msdb.dbo.sp_send_dbmail
@recipients =N'someone@someaddress.com',
@body = 'Test Email Body',
@subject = 'Test Email Subject',
@profile_name = 'MyMailProfile'