select query in CDNOTS
-
Hi,
I was using xp_sendmail to do this:
___________________________________
declare @query_string = 'select * from tbl_debt'
exec master..xp_sendmail @recipients = 'sho@sho.com',
@subject = 'Outstanding Debt',
@message = 'hello',
@query = @query_string,
___________________________________
it would write out the results of the query_string to the email.
however xp_sendmail doesn't allow for HTML, so i got CDONTS working and created the following SP (from a MS site):
___________________________________
CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@cc varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'BodyFormat', 0
EXEC @hr = sp_OASetProperty @MailID, 'MailFormat', 0
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @cc
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
___________________________________
and i use
exec sp_send_cdontsmail
'Test of CDONTS',
'<table border=1><tr><td>It works</td></tr></table>'
___________________________________
to get it to send me an email, now this is all fine but how do I get the CDONTS version to add the results of a query to the email as there does not seem to be a @query parameter??
all help appreciated. what would be even nicer is if the results of a query would have table borders around it!!
-
You can't attach query results using CDONTS.
Extract the results to a file and then attached the file.
--------------------
Colt 45 - the original point and click interface
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply