send a .html attachment directly from SQL Server
-
Has anyone ever sent a .html attachment directly from SQL Server (not using .NET or anything like that)? I can't figure out how to do it. I found this article: http://databasejournal.com/features/mssql/article.php/10894_3489111_1 but I can't figure out how to do an attachment.
Anyone out there know?
-
Brenda,
I use that sp to send html formatted email documents from SQL Server but I don't send them as attached files they come in as html based emails. If that's what you're looking for I may be able to help out. I'm don't think that that sp has the ability to do attachments built into it but I could be wrong.
-
But what if your html code is over 8000 characters? What do you do? And how do you store your html? In a column? Or how?
-
Brenda,
I did a little research, because i typically don't have outputs greater than 8000 characters which does seem to be a limit for the body variable.
I can attach existing files to an email through using the stored procedure with a few modifications, I've tested it a few times and it seems to work fine. You'd just have to have the file created on the harddrive san or whatever storage device first and know it's location to attach it. Would this help?
-
Todd
YES, that would help! How do I do it?
-
I have pulled an exerpt from the sp code. Add the lines in blue to your code in the stored procedure
***********************************************
create procedure usp_send_cdosysmail
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@smtpserver varchar(25),
@bodytype varchar(10),
@attachment varchar(256)
as
declare @imsg int
declare @hr int
declare @source varchar(255)
declare @description varchar(500)
declare @output varchar(1000)
exec @hr = sp_oacreate 'cdo.message', @imsg out
exec @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
exec @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',
@smtpserver
exec @hr = sp_oamethod @imsg, 'configuration.fields.update', null
exec @hr = sp_oasetproperty @imsg, 'to', @to
exec @hr = sp_oasetproperty @imsg, 'from', @from
exec @hr = sp_oasetproperty @imsg, 'subject', @subject
-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.
exec @hr = sp_oasetproperty @imsg, @bodytype, @body
exec @hr = sp_OAMethod @imsg, 'AddAttachment',null, @attachment
exec @hr = sp_oamethod @imsg, 'send', null
*************************************************
When you call the stored procedure, just call it with the additional @attachment parameter which should be declared as varchar(256) as well and assign it the path to the file as shown below
Excerpt from calling sp.
Declare @attachment varchar(256)
Select @attachment = 'd:\whatever.html'
exec usp_send_cdosysmail @from, @to, @subject, @body, @smtpserver, @bodytype, @attachment
Obviously you will be passing all the other parameters as well
. Let me know if it works for you or if you need further assistance -
That is cool! Will it always show up on the email as an attachment? Or can you just have it view the html? Or does that just need to go in the body part if you want it to show up without an attachment? Am I confusing?
-
Yeah, the attachments will always show up as files unless your email system has some way to have them show automatically.
Basically if you want something to show up in the body of the email you have to put it in the body of the message which I think is limited in cdosys to 8000 characters so to go larger I think you will have to use attachments.
-
Brenda, If you use xp_smtp_sendmail from http://www.sqldev.net it has an option to use a html file as the body of the email. We do this for a couple of quick and dirty admin reports.
--------------------
Colt 45 - the original point and click interface
-
Hi Phill
Can you show me an example of how you do that? I am very curious...
Thanks!
-
Brenda
xp_smtp_sendmail has a parameter called @MessageFile, You just pass in the full path and filename to the html file and it is used as the message body. There is quite a few examples at the website.
Generally we use sp_makewebtask to generate the html files, but one of my colleagues put together a bunch of VBScript to create a series of html reports. Then we just emailed them out using xp_smtp_sendmail.
--------------------
Colt 45 - the original point and click interface
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply