Extending CDOSYS Mail to include Query Attachments?

Hi,

I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?

I have set up a SP for CDOSYS Mail as outlined in the following link:
http://support.microsoft.com/default.aspx?id=kb;de;312839&sd=tech

Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail

Thanks,
KimHi,

I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?

I have set up a SP for CDOSYS Mail as outlined in the following link:
http://support.microsoft.com/default.aspx?id=kb;de;312839&sd=tech

Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail

Thanks,
Kim

Jasper has written an sp for it check it out here:
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20649|||Sorry, I don't think I explained myself very well!

I currently use xp_sendmail and pass a query as a parameter and attach the results of this query to an email. I would like to know if anyone has extended CDOSYS Mail to have the same functionality? Example of how I use this is in xp_sendmail @.query parameter would be a query which returns the total number of records on an import table and xp_sendmail sends the results in an email attachement OR sends the results as part of the body of the email.

Extract of the xp_sendmail Syntax:
xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.query =] 'query']

[,[@.attach_results =] 'attach_value']|||I had the same problem and found a workaround using osql to write the results of the query to a file. I then include the file as an attachment and delete it from the server. Seems to work well even within a loop.

I used this to work from.
http://www.sqlteam.com/item.asp?ItemID=4722

And came up with this command line that seems to create a file with the same format as the attach query results did with sendMail:

SET @.bcpCommand = "osql -h-1 -w800 /U usrId /P pw /d " + @.dbName + " /Q ""myprocname parm1, parm2"" -o "
SET @.bcpCommand = @.bcpCommand + @.FileName

EXEC master..xp_cmdshell @.bcpCommand

Then just pass @.fileName as the attachment to the cdosys mail procedure. I then use xp_cmdshell to delete the file from the server.