For the sql server mail the Xp_smtp_sendmail has been used. So first that dll should be downloaded.

To setup the xp_smtp_sendmail:

1)     For SQL Server 2000, download XPSMTP80.ZIP and unzip the files

2)     Copy xpsmtpXX.dll into the SQL Server BINN directory.For SQL Server 2000 copy XPSMTP80.DLL.

     For SQL Server 2000 the default location is "C:\Program Files\Microsoft   

     SQL Server\MSSQL\Binn"

3) Register the extended stored procedure using OSQL or SQL Query

    Analyzer by executing:

     exec sp_addextendedproc 'xp_smtp_sendmail',   

        'xpsmtp80.dll'

4)     Grant rights to the correct set of users using OSQL or SQL Query

     Analyzer by executing:

     grant execute on xp_smtp_sendmail to public

    By default only the member of the sysadmin role have execution rights    

         on the XP after it is being registered

 A test case of how you can use this :  

                         Script to Check the Offline Databases

1)     This Script is to check the status of the Databases on the servers and send a Mail to the DBADMIN when any of the Database goes OFFLINE.

2)     The script can be run as a Sql Server Agent Job on the Server and can be scheduled according to the requirements to run every one hour or every minute.

The Script is :

SET NOCOUNT ON

DECLARE     @Msg VARCHAR(8000)

SELECT        @Msg = ISNULL(@Msg + CHAR(13), '') + 'Database ' + z.Name + ' on machine ' + z.ServerName + ' is ' + z.Status + ' at ' + z.Now + '.'

FROM (

                        SELECT                    TOP 100 PERCENT @@SERVERNAME ServerName,

                                                Name,

                                                CONVERT(VARCHAR, DATABASEPROPERTYEX(Name, 'Status')) Status,

                                                CONVERT(VARCHAR, GETDATE(), 109) Now

                        FROM             master..sysdatabases

                        WHERE                     status & 512 = 512

                        ORDER BY    Name

            ) z

PRINT @Msg

IF @Msg IS NOT NULL

            EXEC master.dbo.xp_smtp_sendmail

            @FROM = N'address',

            @TO = N ' address',

            @server = N'smtp.depaul.edu',

            @subject = N'Status of the Database on Testsqlserver!',

            @type = N'text/html',

            @message = @Msg