(SQL Server) Create MDN (Message Disposition Notification) Email

Demonstrates how to create a MDN (Message Disposition Notification) Email having the format as defined in RFC 3798.

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 @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @email int
    -- Use "Chilkat_9_5_0.Email" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- An MDN can be used to notify the
    -- sender of a message of any of several conditions that may occur after
    -- successful delivery, such as display of the message contents,
    -- printing of the message, deletion (without display) of the message,
    -- or the recipient's refusal to provide MDNs.
    -- (for more information, see RFC 3798)

    -- IMPORTANT:
    -- Note: Please be careful in sending automated MDN's.
    -- It is important to be aware of an issue called "backscatter".
    -- See the Wikipedia article here:  http://en.wikipedia.org/wiki/Backscatter_%28e-mail%29

    -- The CreateMdn method is called to create a new
    -- MDN email based upon the email object calling CreateMdn.
    -- The email object instance used to call CreateMdn would
    -- typically be an email received from a POP3 or IMAP server.
    -- In this case, to simplify the example, we'll load the email
    -- from a .eml file.

    -- This example will be creating a MDN for "someEmail.eml"
    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'someEmail.eml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- The MDN created by CreateMdn will contain a 
    -- "message/disposition-notification" MIME sub-part that will
    -- be composed of a number of name-value pairs.
    -- See RFC 3798  ( http://tools.ietf.org/html/rfc3798 )
    -- The name-value content for this part is passed
    -- as XML to CreateMdn. This part of the example
    -- prepares the XML:
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    -- For this example, we're just adding a few name-value pairs.
    -- These should not be considered to be correct, required or
    -- even sensible -- they are simply for the example.  
    EXEC sp_OASetProperty @xml, 'Tag', 'DispositionFields'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'Disposition', 'manual-action/MDN-sent-manually; displayed'
    EXEC sp_OAMethod @email, 'GetHeaderField', @sTmp0 OUT, 'Message-ID'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'Original-Message-ID', @sTmp0
    EXEC sp_OAMethod @email, 'GetToAddr', @sTmp0 OUT, 0
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'Final-Recipient', @sTmp0

    DECLARE @xmlStr nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetXml', @xmlStr OUT

    -- The last argument to be passed to CreateMdn will be a boolean
    -- indicating whether to include the entire MIME of the calling
    -- email object in the MDN, or only the header.
    -- It may make sense to include the entire email for small emails,
    -- but for large emails (with large attachments) it's probably
    -- best to make the MDN header-only.
    DECLARE @bHeaderOnly int
    SELECT @bHeaderOnly = 1

    -- The first argument to be passed to CreateMdn will
    -- be a human-readable explanation that will be placed
    -- into the 1st MIME part of the MDN (see RFC 3798).
    DECLARE @humanReadableExplanation nvarchar(4000)
    SELECT @humanReadableExplanation = 'Blah blah blah.  Your message has been received and displayed. Blah blah blah'

    -- OK, we're ready to create MDN...
    DECLARE @mdnEmail int

    EXEC sp_OAMethod @email, 'CreateMdn', @mdnEmail OUT, @humanReadableExplanation, @xmlStr, @bHeaderOnly
    EXEC sp_OAGetProperty @email, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    -- Show the MIME of the source (original) email:
    EXEC sp_OAMethod @email, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0


    PRINT '**************************************'

    -- Show the MIME of the MDN email:
    EXEC sp_OAMethod @mdnEmail, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @mdnEmail

    -- Sample output showing the MIME of the original email,
    -- and the MDN email created based upon it:

    -- X-Account-Key: account2
    -- X-UIDL: 0D154945439D42BD80F180F6D9DE4D82
    -- Received: with MailEnable Postoffice Connector;
    -- 	 Tue, 21 Jun 2011 09:21:23 -0400
    -- Received: from qmta14.emeryville.ca.mail.comcast.net ([76.96.227.212]) by chilkatsoft.com with MailEnable ESMTP;
    -- 	 Tue, 21 Jun 2011 09:21:22 -0400
    -- Received: from omta03.emeryville.ca.mail.comcast.net ([76.196.30.27]) by qmta14.emeryville.ca.mail.comcast.net with comcast id yd4k1g0050b6N64AEdLW9x;
    -- 	 Tue, 21 Jun 2011 13:20:30 +0000
    -- Received: from [127.0.0.1] ([67.175.202.103]) by omta03.emeryville.ca.mail.comcast.net with comcast id ydLV1g00n2EMkZ78PdLWxi;
    -- 	 Tue, 21 Jun 2011 13:20:31 +0000
    -- Message-ID: <4E009AA4.3090205@chilkatsoft.com>
    -- Date: Tue, 21 Jun 2011 08:20:36 -0500
    -- From: Chilkat Software <admin@chilkatsoft.com>
    -- User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
    -- MIME-Version: 1.0
    -- To: Chilkat Support <support@chilkatsoft.com>
    -- Subject: test 123
    -- Content-Type: text/plain; charset=ISO-8859-1; format=flowed
    -- Content-Transfer-Encoding: 7bit
    -- Return-Path: <>
    -- X-Antivirus: avast! (VPS 110621-0, 06/21/2011), Inbound message
    -- X-Antivirus-Status: Clean
    -- 
    -- This is a test 123
    -- 
    -- 
    -- 
    -- **************************************
    -- Content-Type: multipart/report;
    -- 	 report-type=disposition-notification;
    -- 	 boundary="------------000802010206080606030808"
    -- 
    -- --------------000802010206080606030808
    -- Content-Type: text/plain
    -- Content-Transfer-Encoding: 7bit
    -- 
    -- Blah blah blah.  Your message has been received and displayed. Blah blah blah
    -- --------------000802010206080606030808
    -- Content-Type: message/disposition-notification
    -- 
    -- Disposition: manual-action/MDN-sent-manually; displayed
    -- Original-Message-ID: <4E009AA4.3090205@chilkatsoft.com>
    -- Final-Recipient: support@chilkatsoft.com
    -- 
    -- --------------000802010206080606030808
    -- Content-Type: text/rfc822-headers
    -- 
    -- X-Account-Key: account2
    -- X-UIDL: 0D154945439D42BD80F180F6D9DE4D82
    -- Received: with MailEnable Postoffice Connector;
    -- 	 Tue, 21 Jun 2011 09:21:23 -0400
    -- Received: from qmta14.emeryville.ca.mail.comcast.net ([76.96.227.212]) by chilkatsoft.com with MailEnable ESMTP;
    -- 	 Tue, 21 Jun 2011 09:21:22 -0400
    -- Received: from omta03.emeryville.ca.mail.comcast.net ([76.196.30.27]) by qmta14.emeryville.ca.mail.comcast.net with comcast id yd4k1g0050b6N64AEdLW9x;
    -- 	 Tue, 21 Jun 2011 13:20:30 +0000
    -- Received: from [127.0.0.1] ([67.175.202.103]) by omta03.emeryville.ca.mail.comcast.net with comcast id ydLV1g00n2EMkZ78PdLWxi;
    -- 	 Tue, 21 Jun 2011 13:20:31 +0000
    -- Message-ID: <4E009AA4.3090205@chilkatsoft.com>
    -- Date: Tue, 21 Jun 2011 08:20:36 -0500
    -- From: Chilkat Software <admin@chilkatsoft.com>
    -- User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
    -- MIME-Version: 1.0
    -- To: Chilkat Support <support@chilkatsoft.com>
    -- Subject: test 123
    -- Content-Type: text/plain; charset=ISO-8859-1; format=flowed
    -- Content-Transfer-Encoding: 7bit
    -- Return-Path: <>
    -- X-Antivirus: avast! (VPS 110621-0, 06/21/2011), Inbound message
    -- X-Antivirus-Status: Clean
    -- --------------000802010206080606030808--
    -- 
    -- 

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @xml


END
GO