The facility that does this is called "SQLMail", here's the msdn article on how to get started with it: http://msdn.microsoft.com/en-us/library/aa213788(v=SQL.80).aspx.
However, be forewarned: SQLMail requires you to install the Outlook client on the same box as your SQL Server. As MS Outlook is specifically intended to not run on a Server, this can be quite diffcult to get working.