with SQL 2005 and above, tehre is a built in service you can use by calling msdb.dbo.sp_send_dbmail
you have to create a profile in SSMS (there's a simple wizard for this) so that SQL knows which SMTP server you use to send the mail with. once that's set up, you can use it in your code...the nice part is that it is asynchronous, so your proc doesn't wait for the mail to be delivered the way sp_OACreate does.
example code:
declare @body1 varchar(4000)
set @body1 = '<html><head>
<title> Embedded Logo Example</title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<body>
<table><tr><td valign="top" align="left">MyHeader</td></tr>
<tr><td valign="top" align="left"><img src="cid:sqlservercentral_logo.gif" width="235" height="70" border="0" alt=""></td></tr>
</table>
</body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='MyEmail as scripts',
@recipients='lowell@someDomain.net',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT top 3 * from sysobjects where xtype=''U''',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'results.txt',
@query_result_no_padding = 1,
@file_attachments = 'C:\sqlservercentral_logo.gif'
example setup:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!