In order to send mail by SQL Server 2008, there are 3 basic things to do:

Create Profile and Account

Configure Email

Send Email

Step 1: Create Profile and Account

You need to create a profile and account using the Configure DatabaseMail Wizard which can be accessed from the Configure DatabaseMail context menu of the DatabaseMail node in Management Node.

Account name is Database Mail (happybirthday)Description is an optionalE-Mail Address: xxx@xxx.xx

Display Name: happybirthday

Reply Email: It can be blank or we can use the same email as above.

Server Name: localhost. This is SMTP server.

Port Number: server port number to be used is default port number is 25.

Secure Connection: We have to select a SSL connection as shown in the picture for mail.

Step 2: Configure Email

After the Account and the Profile are created successfully, we need to configure the DatabaseMail. To configure it, we need to enable the DatabaseMail XPs parameter through the sp_configure stored procedure, as shown here:

Step 1: Ensure that SQL Server Agent is up and running. You can see it below.

Click the YES button.

Step 2: Right-click on SQL Server Agent. You will see the option for "New" there. Add the Job as in the following.:

Step 3: The New Job popup will appear. Specify the name of the job.

Step 4: Click next on the "Steps" in the left menu. A SQL job can contain one or more steps. A step might be simply a SQL statement or a stored procedure call. Add your step here.

Job added:

Step 5: Click next on the "Schedules" in the left menu. A SQL job can contain one or more schedules. A schedule is basically the time at which the sql job will run itself. You can specify recurring schedules also.