• I use the script below for setting up Database Mail. It's useful if you regularly build new servers or set up Database Mail on existing ones. You should be able to tweak it to make it work in your environment:

    DECLARE @account sysname

    DECLARE @email nvarchar(128)

    SET @account = CAST(SERVERPROPERTY('ServerName') AS sysname)

    SELECT @account = REPLACE(@account,'\','_')

    ,@email = REPLACE(@account,'\','.')+'@MyCompany.co.uk'

    -- Create a Database Mail account

    EXEC msdb.dbo.sysmail_add_account_sp

    @account_name = @account,

    @email_address = @email,

    @replyto_address = 'MyAddress@MyCompany.co.uk',

    @display_name = @account,

    @mailserver_name = 'MyExchangeServer.MyCompany.co.uk',

    @port = 25 ;

    -- Create a Database Mail profile

    EXEC msdb.dbo.sysmail_add_profile_sp

    @profile_name = @account ;

    -- Add the account to the profile

    EXEC msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = @account,

    @account_name = @account,

    @sequence_number = 1 ;

    -- Grant the DBMailUsers role access to the profile

    EXEC msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = @account,

    @principal_name = 'public',

    @is_default = 1 ;

    -- Add users to role to allow them to use sp_send_dbmail

    DECLARE @sql varchar(4000)

    SELECT @sql = 'USE msdb' + CHAR(13)

    SELECT

    @sql = @sql + 'EXEC sp_addrolemember ''DatabaseMailUserRole'', ''' + [name] + '''' + CHAR(13)

    FROM syslogins

    WHERE isntgroup=1 AND sysadmin=1

    EXEC (@sql)

    -- Enable the profile in SQL Server Agent

    EXEC master.dbo.xp_instance_regwrite

    @rootkey= N'HKEY_LOCAL_MACHINE'

    ,@key= N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'

    ,@value_name = N'DatabaseMailProfile'

    ,@type= N'REG_SZ'

    ,@value= @account

    -- Verify the correct profile name is enabled

    EXEC master.dbo.xp_instance_regread

    @rootkey= N'HKEY_LOCAL_MACHINE'

    ,@key= N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'

    ,@value_name = N'DatabaseMailProfile'

    John