• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1313
  • Last Modified:

How Do I Embed an image using CDO within a SQL Server 2000 Stored Procedure

I have created the following Stored Procedure in SQL Server 2000 in an attempt to embed an image into an HTML formatted email.  Though I get the email - the image fails to appear (I get the infamous red x where the image should appear.   I've tried referencing the image as a URL, a network file location and a direct path.  Below is my code - what is it I seem to be missing??

CREATE PROCEDURE dbo.ART_SENDMAIL
 (
  @FROM                       VARCHAR(200),
  @TO                         VARCHAR(200),
  @SUBJECT                    VARCHAR(200),
  @MSG_CONTENT                VARCHAR(8000),
  @MSG_TYPE                   VARCHAR(200),
  @MAIL_SERVER                VARCHAR(200)
 )
AS
 BEGIN
   DECLARE @iMsg          INT,
           @hr            INT,
           @source        VARCHAR(255),
           @description   VARCHAR(500),
           @output        VARCHAR(1000),    
           @BP            INT,
           @image         VARCHAR(100),
           @image_path    VARCHAR(2000)

   SET @image       = 'HR_Birthday.gif'
   SET @image_path  = 'http://artweb/images/'
   --SET @image_path  = '\\file01\Shares\DropBox\'
   --SET @image_path  = 'C:\ArtDorr\'
   SET @MSG_CONTENT = '<html><head><body><img src="cid:' + @image + '"><br><center><b>Testing....</b></center><body></html>'          

   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', @MAIL_SERVER
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout").Value','10'
   EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
   EXEC @hr = sp_OASetProperty @iMsg, 'MimeFormatted', '1'
   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, @MSG_TYPE , @MSG_CONTENT
   
   EXEC @hr = sp_OAMethod @iMsg, 'AddRelatedBodyPart', @BP out, @image_path, @image, '1'
   EXEC @hr = sp_OASetProperty @BP, 'Fields.Item("urn:schemas:mailheader:Content-ID").Value', @image
   EXEC @hr = sp_OAMethod @BP, 'Fields.Update', null

   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

   -- Error handling.
   IF @hr <>0
     BEGIN
       SELECT @hr
     END

   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

   -- Clean up the objects created.
   EXEC @hr = sp_OADestroy @iMsg


 END
0
artdorr
Asked:
artdorr
1 Solution
 
nschaferCommented:
You are very close.  The @Image_Path variable must contain the path and the file name.

See the attached.

Hope the helps,

Neal.
ALTER PROCEDURE dbo.ART_SENDMAIL
 (
  @FROM                       VARCHAR(200),
  @TO                         VARCHAR(200),
  @SUBJECT                    VARCHAR(200),
  @MSG_CONTENT                VARCHAR(8000),
  @MSG_TYPE                   VARCHAR(200),
  @MAIL_SERVER                VARCHAR(200)
 )
AS
 BEGIN 
   DECLARE @iMsg          INT,
           @hr            INT,
           @source        VARCHAR(255),
           @description   VARCHAR(500),
           @output        VARCHAR(1000),    
           @BP            INT,
           @image         VARCHAR(100),
           @image_path    VARCHAR(2000)

   SET @image       = 'logo1.jpg'  -- changed to a file I have
   --SET @image_path  = 'http://artweb/images/'
   --SET @image_path  = '\\file01\Shares\DropBox\'
   SET @image_path  = 'C:\logo1.jpg'  -- must contain path and file name
   SET @MSG_CONTENT = '<html><head><body><img src="cid:' + @image + '"><br><center><b>Testing....</b></center><body></html>'           

   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', @MAIL_SERVER 
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout").Value','10'
   EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
   EXEC @hr = sp_OASetProperty @iMsg, 'MimeFormatted', '1'
   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, @MSG_TYPE , @MSG_CONTENT
   
   EXEC @hr = sp_OAMethod @iMsg, 'AddRelatedBodyPart', @BP out, @image_path, @image, '1'
   EXEC @hr = sp_OASetProperty @BP, 'Fields.Item("urn:schemas:mailheader:Content-ID").Value', @image
   EXEC @hr = sp_OAMethod @BP, 'Fields.Update', null 

   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

   -- Error handling.
   IF @hr <>0 
     BEGIN
       SELECT @hr
     END

   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

   -- Clean up the objects created.
   EXEC @hr = sp_OADestroy @iMsg


 END

Open in new window

0
 
artdorrAuthor Commented:
Excellent!  It's always the small thing isn't it!

Thanks for the help!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now