SQL Server

Add Binary Email Attachment from In-Memory Bytes

Demonstrates how to add an attachment to an email via the AddDataAttachment and AddDataAttachment2 methods. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- First populate a simple email.
    EXEC sp_OASetProperty @email, 'Subject', 'This is the subject.'
    EXEC sp_OASetProperty @email, 'Body', 'This is a simple email body.'
    EXEC sp_OASetProperty @email, 'From', 'Chilkat Software <support@chilkatsoft.com>'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Some Recipient', 'someRecipient@somewhere.com'

    -- Load a JPG file.
    -- The actual source of your attachment data can come from anywhere.
    -- The point here is that we're first getting the bytes in memory, and THEN adding
    -- the bytes as an attachment.
    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT

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

    -- Add the JPG bytes via AddDataAttachment.
    EXEC sp_OAMethod @email, 'AddDataAttachment', @success OUT, 'tinyStarfish.jpg', @jpgBytes

    -- We could also add the JPG and explicitly set the content-type w/ AddDataAttachment2
    EXEC sp_OAMethod @email, 'AddDataAttachment2', @success OUT, 'teenyStarfish.jpg', @jpgBytes, 'image/jpg'

    -- Examine the MIME:
    EXEC sp_OAMethod @email, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    -- This is the MIME of the email:

    -- 	MIME-Version: 1.0
    -- 	Date: Mon, 17 Oct 2016 19:28:30 -0500
    -- 	Message-ID: <44E3519660F8C3B1F0D95F25D1FD5B3840C3E3FE@CHILKAT13>
    -- 	Content-Type: multipart/mixed; boundary="------------020509000105040006080600"
    -- 	X-Priority: 3 (Normal)
    -- 	Subject: This is the subject.
    -- 	From: "Chilkat Software" <support@chilkatsoft.com>
    -- 	To: "Some Recipient" <someRecipient@somewhere.com>
    -- 
    -- 	--------------020509000105040006080600
    -- 	Content-Type: text/plain
    -- 	Content-Transfer-Encoding: 7bit
    -- 
    -- 	This is a simple email body.
    -- 	--------------020509000105040006080600
    -- 	Content-Type: image/jpeg; name="tinyStarfish.jpg"
    -- 	Content-Transfer-Encoding: base64
    -- 	Content-Disposition: attachment; filename="tinyStarfish.jpg"
    -- 
    -- 	/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==
    -- 
    -- 	--------------020509000105040006080600
    -- 	Content-Type: image/jpg; name="teenyStarfish.jpg"
    -- 	Content-Transfer-Encoding: base64
    -- 	Content-Disposition: attachment; filename="teenyStarfish.jpg"
    -- 
    -- 	/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==
    -- 
    -- 	--------------020509000105040006080600--
    -- 

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @fac


END
GO