Email Using CDOSYS (embedding images)
-
I am using an active X script to accomplish sendin mail by embedding theimage into the email. (Code Snippet A below). I want to use an stored procedure to accomplish this same task. I can do it all but emdbed the image. (See Code Snippet b). How do I set this property? HTis is a hot issue for me! please respond!
Code Snippet A ***************************************
If Not Result.EOF and not result.bof then
Set objMessage = CreateObject("CDO.Message")
'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = DTSGlobalVariables("gv_SMTP_Server").Value
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==
objMessage.AddRelatedBodyPart DTSGlobalVariables("gv_Header_Image_Path").Value, DTSGlobalVariables("gv_Image").Value, 1
objMessage.AddRelatedBodyPart DTSGlobalVariables("gv_small_image_Path").Value, DTSGlobalVariables("gv_small_image").Value, 1
Do While Not Result.EOF
'The line below shows how to send using HTML included directly in your script
objMessage.Subject = Result("Subject")
objMessage.From = Result("Email_From")
objMessage.To = Result("Email_To")
objMessage.HTMLBody = "
" + "
" + Result("email_text") + " "
objMessage.Send
Result.MoveNext
Loop
end if
******************************************************** end Snippet A
Code Snippet B *****************************************
alter PROCEDURE [dbo].[sp_send_cdosysmail_Dave]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@BodyType varchar(20)='TextBody'
/*********************************************************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
declare @out int
select @out =0
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mail.mail.com'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
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, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @out =@hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
--PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
return @out
******************************************************end code snippet b
Kindest Regards,
David** Obstacles are those frightening things that appear when we take our eyes off the goal. **
-
You might want to check out more of a coding site for this. Like http://www.4guysfromrolla.com.
The other thing is I've used the free ASPEMail component that seems to work better for sending mail.
-
We dont want to add any third party components to the SQL server. I can make this work from a VBSCRipt but not from T-SQl itself.
Kindest Regards,
David** Obstacles are those frightening things that appear when we take our eyes off the goal. **
-
-
Hi, I know this question have a long time posted but I'd like to leave one solution for future references.
I saw an article at http://support.jodohost.com/showthread.php?t=7692 showing how to add embedded images only using VBScript, then I decided to modify your Stored Procedure using that programming logic and following the object model of this component.... also, I included a line for attach one file.
It was tested on Windows 2003 running SQLServer 2000 (both with all possible services pack installed) also using Outlook and hotmail as the email readers.
The script for testing the new Stored Procedure is
exec sp_send_cdosysmail_Dave 'from_user@mailserver.com', 'to_user@email.com', 'Message subject'
Your SP modified is:
CREATE PROCEDURE [dbo].[sp_send_cdosysmail_Dave]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)="
/*********************************************************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
***********************************************************************/
AS
Declare @iMsg int
Declare @bp int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
Declare @out int
Declare @BodyType varchar(20)
select @BodyType = 'HTMLBody'
--This lines were added
Declare @attachment varchar(200)
Declare @img varchar(50)
Declare @imgPath varchar(200)
Declare @Body varchar(4000)
select @out =0
--This lines were added
select @attachment = 'C:\AttachFile.pdf'
select @img = 'myimage.gif'
select @imgPath = 'C:\images\' + @img
select @Body = '<html><head><body><img src="cid:' + @img + '"><br><center><b>Testing....</b></center><body></html>'
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'MailServerName'
-- This is the timeout, replace it for any you want
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout").Value','10'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'MimeFormatted', '1' --this line is new
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- Im using HTML e-mail
EXEC @hr = sp_OASetProperty @iMsg, @BodyType, @Body
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', null, @attachment
-- The AddRelatedBodyPart method accepts two values in the cdoReferenceType, we're going to use cdoRefTypeLocation
-- cdoRefTypeId = 0 The reference paramter contains a value for the Content-ID header. The HTML body refers to the resource using this Content-ID header
-- cdoRefTypeLocation = 1 The reference parameter contains a value for the Content-Location MIME header. The HTML body refers to this resource using this message-relative URL
EXEC @hr = sp_OAMethod @iMsg, 'AddRelatedBodyPart', @bp out, @imgPath, @img, '1'
EXEC @hr = sp_OASetProperty @bp, 'Fields.Item("urn:schemas:mailheader:Content-ID").Value', @img
EXEC @hr = sp_OAMethod @bp, 'Fields.Update', null
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @out =@hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
--PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
return @out
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply