(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.

Download Chilkat Email ActiveX

Download Chilkat XML ActiveX

Download 32-bit Chilkat IMAP ActiveX (.msi)

Download All 32-bit Chilkat ActiveX Components (.zip)

Download All 64-bit Chilkat ActiveX Components (.zip)

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email2', @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
        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
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  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

    IF Not (@mdnEmail Is NULL )
      BEGIN
        --  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
      END
    ELSE
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
      END

    --  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--

END
GO