Having problem with stored produre

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Having problem with stored produre

Author

Topic

serenethy
Starting Member

2 Posts

Posted - 2006-01-25 : 10:14:51

Hi,i really need help from someone out there because is URGENT! I need to use stored procedure using CDOSYS to run an email that can embed image and shown as inline message. Below is the stored procedure that i created,but still got problem. An attachment is showm as an attachment,however is still now shown as inline message. Can anyone help me to solve this problem? I will really apreciate for the help. Thanks

CREATE PROCEDURE sp_cdosysmail

--specify the input parameters that insert values to stored procedure
@from varchar(100) ,
@to varchar(100) ,
@cc varchar(500),
@bcc varchar(500),
@subject varchar(200),
@body varchar(500) ,
@attachments varchar(1000),
@smtpserver varchar(25) = "aprskuldmn00006"

--body of the stored procedure
as
declare @imsg int
declare @result int
declare @bodytype int

--create an instance of the CDOSYS object
exec @result = sp_oacreate 'CDO.message', @imsg out

--set the value to the property
exec @result = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value', '2'
exec @result = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @smtpserver
exec @result = sp_oamethod @imsg, 'configuration.fields.update', null

--set email properties
exec @result = sp_oasetproperty @imsg, 'from', @from
exec @result = sp_oasetproperty @imsg, 'to', @to
exec @result = sp_oasetproperty @imsg, 'cc', @cc
exec @result = sp_oasetproperty @imsg, 'bcc', @bcc
exec @result = sp_oasetproperty @imsg, 'subject', @subject

EXEC @result= sp_OASetProperty @imsg, 'TextBody', @Body
--if you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.

DECLARE @strBodyHTML VARCHAR(8000)
SET @strBodyHTML = @Body
set @strBodyHTML=@strBodyHTML+'<HTML><HEAD>'
set @strBodyHTML=@strBodyHTML+'</HEAD>'
set @strBodyHTML=@strBodyHTML+'
set @strBodyHTML=@strBodyHTML+'<IMG SRC="@attachments">'
set @strBodyHTML=@strBodyHTML+'
set @strBodyHTML=@strBodyHTML+'</HTML>'
exec @result = sp_oasetproperty @imsg, 'HTMLBody', @strBodyHTML

--add attachment
if @attachments is not null
begin
declare
@charpos int,
@attachment varchar(1000),
@attachmentlist varchar(1000)

set @attachmentlist = @attachments
set @charpos = charindex(';', @attachmentlist, 1)

while @charpos > 0
begin
set @attachment = substring(@attachmentlist, 1, @charpos - 1)

if @attachment <> ''
begin
--@attachsend = 'insert into @attachmentlist values (@attachment)'
exec @result = sp_oamethod @imsg, 'addattachment', null, @attachment
end
set @attachmentlist = right(@attachmentlist, len(@attachmentlist) - @charpos)
set @charpos = charindex(';', @attachmentlist, 1)
end
--set @attachsend = (select attachment from @attachmentlist)
end

declare @type int
exec @bodytype = sp_oacreate 'CDO.BodyPart', @type out
set @type= @imsg
exec @bodytype= sp_oasetproperty @type,'fields("urn:schemas:httpmail:content-disposition-type").value','inline',@attachment
exec @bodytype= sp_oasetproperty @type,'fields ("urn:schemas:httpmail::content-type").value','multipart/mixed'

declare @contenttype varchar
exec @bodytype=sp_oagetproperty @type, 'ContentMediaType', @contenttype out
if @contenttype = 'cdoMultipartMixed'
begin
exec @bodytype = sp_oamethod @type, 'GetDecodedContentStream'
end

EXEC @bodytype = sp_OAMethod @type, 'Fields.Update'
EXEC @bodytype= sp_oamethod @type, 'send',null
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources