Setup

Open SQL Server Management Studio

Click New Query

Type in the Code Below:

• Declare @@ProfileName as varchar(50)
• Declare @@AccountName as varchar(50)
• Declare @@EmailServer as varchar(50)
• Declare @@EmailAddress as varchar(100)
• Declare @@ReplyAddress as varchar(100)
• Declare @@Username as varchar(100)
• Declare @@Password as Varchar(50)
  
  

 Please Note that you must:

•  turn on SQLmail/DBMail in the Surface area configuration in Facets of the SQL server 
• You must change these Items before running to match local settings 

• Set @@ProfileName = 'WB Report Scheduling'
• Set @@AccountName = 'WB Report Scheduling'
• Set @@EmailServer = '192.168.2.1'
• Set @@EmailAddress = 'john.doe@domain_name.com'
• Set @@ReplyAddress = 'john.doe@domain_name.com'

 If You want annon login leave these two as they are:

• Set @@Username = 'Username' 
• Set @@Password = 'Password' 

Create a Database Mail profile:

• EXECUTE msdb.dbo.sysmail_add_profile_sp 
• @profile_name = @@ProfileName, 
• @description = 'Workbench Report Scheduler Email Profile' ; 

Create a Database Mail account:

IF @@UserName = 'Username'

BEGIN

• EXECUTE msdb.dbo.sysmail_add_account_sp 
• @account_name = @@AccountName 
• @description = 'Workbench Report Scheduler Email Account' 
• @email_address = @@EmailAddress 
• @replyto_address = @@ReplyAddress 
• @display_name = 'Workbench Report Scheduler'
• @mailserver_name = @@EmailServer 

END

OTHERWISE

• EXECUTE msdb.dbo.sysmail_add_account_sp 
• @account_name = @@AccountName
• @description = 'Workbench Report Scheduler Email Account'
• @email_address = @@EmailAddress 
• @replyto_address = @@ReplyAddress 
• @display_name = 'Workbench Report Scheduler' 
• @mailserver_name = @@EmailServer 
• @username = @@Username 
• @password = @@Password ; – Add the account to the profile 

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

 Grant access to the profile to the DBMailUsers role

• EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
• @profile_name = 'SQL2005_Email_ProfileName'
• @principal_id = 0,

Make sure you change the values in the section with the "Set Statements" to valid settings, you can leave the first two if you like.

Click the Execute button to run.

If the code completes without failure, The Database mail is now setup.