(SQL Server) Build a multipart/related MIME Message

Demonstrates how to construct a multipart/related MIME message containing HTML and a related JPG image.

Chilkat ActiveX Downloads

ActiveX for 32-bit and 64-bit Windows

// Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
//
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

    -- Start with an empty MIME object...
    DECLARE @mime int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Mime', @mime OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Initialize it as multipart/related.
    EXEC sp_OAMethod @mime, 'NewMultipartRelated', @success OUT

    -- Set a top-level MIME header field.
    EXEC sp_OAMethod @mime, 'SetHeaderField', @success OUT, 'Subject', 'Test HTML email with JPG image.'

    -- Generate a Content-ID.  This will contain random data that will allow
    -- the HTML's "img" tag to reference the image part in the MIME.
    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'

    DECLARE @sbContentId int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbContentId OUT

    EXEC sp_OAMethod @sbContentId, 'Append', @success OUT, 'part1.'
    EXEC sp_OAMethod @crypt, 'GenRandomBytesENC', @sTmp0 OUT, 4
    EXEC sp_OAMethod @sbContentId, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbContentId, 'Append', @success OUT, '.'
    EXEC sp_OAMethod @crypt, 'GenRandomBytesENC', @sTmp0 OUT, 4
    EXEC sp_OAMethod @sbContentId, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbContentId, 'Append', @success OUT, '@chilkatsoft.com'

    -- Create the HTML body.
    DECLARE @sbHtml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbHtml OUT

    EXEC sp_OAMethod @sbHtml, 'Append', @success OUT, '<html><head>' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbHtml, 'Append', @success OUT, '<meta http-equiv="Content-Type" content="text/html; charset=utf-8">' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbHtml, 'Append', @success OUT, '  </head>' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbHtml, 'Append', @success OUT, '  <body bgcolor="#FFFFFF" text="#000000">' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbHtml, 'Append', @success OUT, '    <p>This is a test.&nbsp; <img src="cid:CONTENT_ID" height="20" width="20"></p>' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbHtml, 'Append', @success OUT, '  </body>' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbHtml, 'Append', @success OUT, '</html>' + CHAR(13) + CHAR(10)
    DECLARE @numReplacements int
    EXEC sp_OAMethod @sbContentId, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @sbHtml, 'Replace', @numReplacements OUT, 'CONTENT_ID', @sTmp0

    -- Create the HTML MIME part.
    DECLARE @htmlMime int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Mime', @htmlMime OUT

    EXEC sp_OAMethod @sbHtml, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @htmlMime, 'SetBodyFromHtml', @success OUT, @sTmp0
    EXEC sp_OASetProperty @htmlMime, 'Encoding', '8bit'
    EXEC sp_OASetProperty @htmlMime, 'Charset', 'utf-8'

    -- Load our JPG image from a file.
    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.FileAccess', @fac OUT

    EXEC sp_OAMethod @fac, 'ReadEntireFile', @jpgBytes OUT, 'qa_data/jpg/starfish20.jpg'

    -- Create an image/jpeg MIME part.
    DECLARE @jpgMime int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Mime', @jpgMime OUT

    EXEC sp_OAMethod @jpgMime, 'SetBodyFromBinary', @success OUT, @jpgBytes
    -- Setting the following properties has the effect of adding
    -- the Content-Type, Content-Transfer-Encoding, and Content-Disposition header fields.
    -- These header fields could also be set via the SetHeaderField method, in which case
    -- the values of the affected properties would be updated.
    EXEC sp_OASetProperty @jpgMime, 'ContentType', 'image/jpeg'
    EXEC sp_OASetProperty @jpgMime, 'Name', 'starfish20.jpg'
    EXEC sp_OASetProperty @jpgMime, 'Encoding', 'base64'
    EXEC sp_OASetProperty @jpgMime, 'Disposition', 'inline'
    EXEC sp_OASetProperty @jpgMime, 'Filename', 'starfish20.jpg'

    DECLARE @sbContentIdHdr int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbContentIdHdr OUT

    EXEC sp_OAMethod @sbContentIdHdr, 'Append', @success OUT, '<CONTENT_ID>'
    EXEC sp_OAMethod @sbContentId, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @sbContentIdHdr, 'Replace', @numReplacements OUT, 'CONTENT_ID', @sTmp0
    EXEC sp_OAMethod @sbContentIdHdr, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @jpgMime, 'SetHeaderField', @success OUT, 'Content-ID', @sTmp0

    -- Add both parts as children of the multipart/related
    EXEC sp_OAMethod @mime, 'AppendPart', @success OUT, @htmlMime
    EXEC sp_OAMethod @mime, 'AppendPart', @success OUT, @jpgMime
    EXEC sp_OAMethod @mime, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    -- --------------------------------------------------------------
    -- --------------------------------------------------------------
    -- This example builds the following MIME message.  The boundary
    -- and Content-ID strings contain random data and will be different.  

    -- Content-Type: multipart/related; boundary="------------A940F1230E6F0105F03DB2CB"
    -- Subject: Test HTML email with JPG image.
    -- 
    -- --------------A940F1230E6F0105F03DB2CB
    -- Content-Type: text/html; charset="utf-8"
    -- Content-Transfer-Encoding: 8bit
    -- 
    -- <html><head>
    -- <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    --   </head>
    --   <body bgcolor="#FFFFFF" text="#000000">
    --     <p>This is a test.&nbsp; <img src="cid:part1.E16AE3B4.1505C436@chilkatsoft.com" height="20" width="20"></p>
    --   </body>
    -- </html>
    -- 
    -- --------------A940F1230E6F0105F03DB2CB
    -- Content-Transfer-Encoding: base64
    -- Content-Type: image/jpeg; name="starfish20.jpg"
    -- Content-Disposition: inline; filename="starfish20.jpg"
    -- Content-ID: <part1.E16AE3B4.1505C436@chilkatsoft.com>
    -- 
    -- /9j/4AAQSkZJRgABAQEASABIAAD//gAmRmlsZSB3cml0dGVuIGJ5IEFkb2JlIFBob3Rvc2hvcD8g
    -- NC4w/9sAQwAQCwwODAoQDg0OEhEQExgoGhgWFhgxIyUdKDozPTw5Mzg3QEhcTkBEV0U3OFBtUVdf
    -- YmdoZz5NcXlwZHhcZWdj/9sAQwEREhIYFRgvGhovY0I4QmNjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj
    -- Y2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj/8IAEQgAFAAUAwERAAIRAQMRAf/EABcAAAMBAAAA
    -- AAAAAAAAAAAAAAIDBAX/xAAYAQADAQEAAAAAAAAAAAAAAAABAgMEAP/aAAwDAQACEAMQAAAB2kZY
    -- NNEijWKddfTmLgALWH//xAAbEAACAgMBAAAAAAAAAAAAAAABAgMRAAQSE//aAAgBAQABBQL0XqN+
    -- pM2aqJGMiqFFCyg7z//EABwRAAICAgMAAAAAAAAAAAAAAAERAAIQIQMSUf/aAAgBAwEBPwHqU5aq
    -- Axx+y1tMQl4elj//xAAcEQEAAQUBAQAAAAAAAAAAAAABEQACEBIhA1H/2gAIAQIBAT8B3Bhqy7Zc
    -- enyiwmGgDhiOzj//xAAdEAABAwUBAAAAAAAAAAAAAAABAAIREBIhIkFR/9oACAEBAAY/ArZyn+Cg
    -- xtxWuJaoCnqDuin/xAAcEAABBAMBAAAAAAAAAAAAAAABABEhYRAxQVH/2gAIAQEAAT8hkEwPUUR9
    -- DYfE4nxtRpIkBTsayuALIiuY/9oADAMBAAIAAwAAABDWPTsf/8QAGhEAAwADAQAAAAAAAAAAAAAA
    -- AAEREDFBIf/aAAgBAwEBPxC0DVPcWm+Ce4OesrkE6bjH/8QAGBEBAQEBAQAAAAAAAAAAAAAAAREA
    -- QRD/2gAIAQIBAT8QahMiOc8YgSrnTY3ELclHXn//xAAcEAEBAAIDAQEAAAAAAAAAAAABEQAhMUFx
    -- EFH/2gAIAQEAAT8Qn3igmSZSj+c4N4zapMy9IjFV98wncN2iuLFsCEbDGxQkI6RO/n//2Q==
    -- 
    -- --------------A940F1230E6F0105F03DB2CB--
    -- 

    EXEC @hr = sp_OADestroy @mime
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sbContentId
    EXEC @hr = sp_OADestroy @sbHtml
    EXEC @hr = sp_OADestroy @htmlMime
    EXEC @hr = sp_OADestroy @fac
    EXEC @hr = sp_OADestroy @jpgMime
    EXEC @hr = sp_OADestroy @sbContentIdHdr


END
GO