Are you asking about xp_sendmail? In SQL 2005.. While it still exists, let me be really clear.
DO NOT USE IT UNDER ANY CIRCUMSTANCES!
(climbing down off soapbox..)
It requires an Outlook client to be installed on the server and uses MAPI, however, whenever MAPI craps out (frequency varies greatly in any given environment) you have to reboot the server and the email requests just fail.
With all that said, let me point you at the replacement. Database Mail, it uses SMTP transport the email to the server, which is the basis of pretty much all email on the net.. Many (most?) environments have an internal SMTP server, often on their exchange box. When the Exchange server is not responding to SMTP the SQL server queues up the requests for sending later..
The big thing to set this up is the internal SMTP server name and login credentials for the SMTP account they want to use, this is often not required.. It works great and should have been the way it worked all along..
CEWII