Installation:
Official Website: http://www.sqldev.net/xp/xpsmtp.htm
To install XPSMTP follow these instructions:
1. For SQL Server 7.0 download XPSMTP70.ZIP and unzip the files http://www.sqldev.net/download/xp/70/xpsmtp70-v1.1.0.8.zip
For SQL Server 2000, download XPSMTP80.ZIP and unzip the files http://www.sqldev.net/download/xp/80/xpsmtp80-v1.1.0.8.zip

2. Copy xpsmtpXX.dll into the SQL Server BINN directory.
For SQL Server 7.0 copy XPSMTP70.DLL, for SQL Server 2000 copy XPSMTP80.DLL
For SQL Server 7.0 the default installation location is "C:\MSSQL7\BINN"
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:
-- SQL Server 7.0 install
exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp70.dll'
-- SQL Server 2000 install
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

Sample to Run:

declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = 'MyEmail@MyDomain.com',
@FROM_NAME = 'Joe Mailman',
@TO = 'MyFriend@HisDomain.com',
@CC = 'MyOtherFriend@HisDomain.com',
@BCC = 'MyEmail@MyDomain.com',
@priority = 'HIGH',
@subject = 'Hello SQL Server SMTP Mail',
@message = 'Goodbye MAPI, goodbye Outlook',
@type = 'text/plain',
@attachments= 'c:\attachment1.txt;c:\attachment2.txt',
@server = 'mail.mydomain.com'

select RC = @rc

go