(SQL Server) Processing a multipart/report Delivery Status Notification (Bounce Notification)

See more Email Object Examples

This example discusses the format of Delivery Status Notification emails and how to process them.

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 @sTmp1 nvarchar(4000)
    -- Here are the MIME structures, showing the content-type and nesting of the MIME parts of three sample
    -- multipart/report DSN (Delivery Status Notification) emails.

    -- This 1st sample includes a "text/rfc822-headers" MIME subpart. 

    -- multipart/report
    --     text/plain
    --     message/delivery-status
    --     text/rfc822-headers
    --     message/rfc822

    -- This 2nd sample lacks the text/rfc-headers part, but the "report type" information
    -- is offered in both plain-text and HTML formats.

    -- multipart/report
    --     multipart/alternative
    --         text/plain
    --         text/html
    --     message/delivery-status
    --     message/rfc822

    -- ----------------------------------------------------------------------------------------------------

    -- A multipart/report MIME delivery status notification follows a specific format defined by the Internet Engineering Task Force (IETF) in RFC 3464, 
    -- which outlines the "An Extensible Message Format for Delivery Status Notifications" standard. The format consists of multiple parts within a 
    -- multipart/report structure. Here's an overview of the main parts involved:
    -- 
    -- The 1st sub-part under multipart/report is the body of the DSN to be displayed by the email client (such as Outlook).
    -- It can be a simple text/plain body, or it can be multipart/alternative and offer a few alternative format, typically plain-text and HTML.
    -- HTML is best for viewing a program such as Outlook. 
    -- This part of the multipart/report is not structured for programmatic processing.  It's meant to be viewed by a human.

    -- --------------------
    -- The "message/delivery-status" part within a multipart/report MIME structure follows a specific format to provide details about the delivery status
    -- of an email message.  Here's an overview of the format and the key components within the "message/delivery-status" part:
    -- 
    -- (1) Content-Type and Reporting-UA:
    -- The "message/delivery-status" part begins with the Content-Type header specifying "message/delivery-status". 
    -- It may also include a Reporting-UA (Reporting User Agent) field that identifies the software or system generating the delivery status notification.
    -- 
    -- For example:
    -- 
    -- Content-Type: message/delivery-status
    -- Reporting-UA: Example Mail System 1.0
    -- 
    -- (2) Fields:
    -- The "message/delivery-status" part contains a series of fields, each providing specific information about the delivery status. 
    -- These fields are structured as key-value pairs.
    -- 
    -- Common fields include:
    -- 
    --     Final-Recipient: Specifies the recipient for whom the delivery status is being reported.
    --     Action: Describes the action performed by the reporting system (e.g., failed, delivered, delayed, etc.).
    --     Status: Indicates the status code or reason for the delivery attempt result.
    --     Remote-MTA: Specifies the host or system that attempted the delivery.
    --     Diagnostic-Code: Provides additional diagnostic information, such as error codes or explanations.
    -- 
    -- For example:
    -- 
    -- Final-Recipient: rfc822; john.doe@example.com
    -- Action: failed
    -- Status: 5.1.1
    -- Remote-MTA: smtp.example.com
    -- Diagnostic-Code: smtp; 550 Requested action not taken: mailbox unavailable
    -- 
    -- (3) Additional Fields:
    -- Additional fields may be included in the "message/delivery-status" part to provide further information about the delivery attempt. 
    -- These fields can vary depending on the implementation or specific needs of the system generating the delivery status notification.
    -- For example:
    -- 
    -- X-Spam-Flag: YES
    -- X-Spam-Score: 7.2
    -- 
    -- Note: The specific fields and their values within the "message/delivery-status" part can vary depending on the implementation
    -- or the email server/application generating the delivery status notification. The structure described above represents the standard format
    -- as defined in RFC 3464, but variations may exist in practice.

    -- --------------------

    -- The "text/rfc822-headers" MIME part, if included, contains the headers of the original email message
    -- for which the delivery status notification is being generated. It provides a subset of the headers from the original message,
    -- typically excluding the message body and attachments.
    -- 
    -- The purpose of including the "text/rfc822-headers" part is to provide contextual information about the original message.
    -- It allows the recipient to review the original headers, such as the subject, sender, recipients, date, and other relevant information,
    -- in order to understand the context and details of the email message for which the delivery status notification is being generated.
    -- 
    -- Note that the specific headers included in the "text/rfc822-headers" part can vary based on the implementation or requirements
    -- of the system generating the delivery status notification.

    -- --------------------

    -- If the message/rfc822 part is present, it contains the full MIME of the email that was not delivered.
    -- In Chilkat terminology, this is an attached message.

    -- OK, let's write code to process a multipart/report email.
    DECLARE @success int

    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

    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'qa_data/eml/deliveryStatus.eml'
    -- success = email.LoadEml("qa_data/eml/sample_multipart_report.eml");
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- Verify this is a multipart/report email..
    EXEC sp_OAMethod @email, 'IsMultipartReport', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'Not a multipart/report email.'
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- Get the body that is to be displayed to a human in an email program (such as Outlook).
    EXEC sp_OAMethod @email, 'HasPlainTextBody', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN

        PRINT 'Plain text body:'
        -- println email.GetPlainTextBody();
      END
    ELSE
      BEGIN
        EXEC sp_OAMethod @email, 'HasHtmlBody', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN
            -- Convert HTML to plain-text..
            DECLARE @h2t int
            -- Use "Chilkat_9_5_0.HtmlToText" for versions of Chilkat < 10.0.0
            EXEC @hr = sp_OACreate 'Chilkat.HtmlToText', @h2t OUT


            PRINT 'HTML body converted to plain-text:'
            EXEC sp_OAMethod @email, 'GetHtmlBody', @sTmp1 OUT
            EXEC sp_OAMethod @h2t, 'ToText', @sTmp0 OUT, @sTmp1
            PRINT @sTmp0
          END
        ELSE
          BEGIN

            PRINT 'Has no plain-text or HTML body...'
          END
      END

    PRINT '---------------------------------'

    -- Now get information from the message/delivery-status part (or the message/disposition-notification part)

    PRINT '--- Delivery Status Information:'

    EXEC sp_OAMethod @email, 'GetDeliveryStatusInfo', @sTmp0 OUT, 'Status'
    PRINT 'Status: ' + @sTmp0

    EXEC sp_OAMethod @email, 'GetDeliveryStatusInfo', @sTmp0 OUT, 'Action'
    PRINT 'Action: ' + @sTmp0

    EXEC sp_OAMethod @email, 'GetDeliveryStatusInfo', @sTmp0 OUT, 'Reporting-MTA'
    PRINT 'Reporting-MTA: ' + @sTmp0

    DECLARE @sa int
    EXEC sp_OAMethod @email, 'GetDsnFinalRecipients', @sa OUT
    DECLARE @numFinalRecipients int
    EXEC sp_OAGetProperty @sa, 'Count', @numFinalRecipients OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numFinalRecipients
      BEGIN

        EXEC sp_OAMethod @sa, 'GetString', @sTmp0 OUT, @i
        PRINT 'final recipient: ' + @sTmp0
        SELECT @i = @i + 1
      END
    EXEC @hr = sp_OADestroy @sa


    PRINT '---------------------------------'

    -- If the multipart/report contains a text/rfc822-headers, it can be retrieved like this:
    DECLARE @headersText nvarchar(4000)
    EXEC sp_OAMethod @email, 'GetNthTextPartOfType', @headersText OUT, 0, 'text/rfc822-headers', 0, 0
    EXEC sp_OAGetProperty @email, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN

        PRINT 'The text/rfc822-headers part exists..'

        PRINT ''

        PRINT @headersText

        -- If you wish to process the headers, you can load them into a MIME object and use the Chilkat MIME functionality to examine the headers.
        DECLARE @mime int
        -- Use "Chilkat_9_5_0.Mime" for versions of Chilkat < 10.0.0
        EXEC @hr = sp_OACreate 'Chilkat.Mime', @mime OUT

        EXEC sp_OAMethod @mime, 'LoadMime', @success OUT, @headersText

        -- Do whatever you want.. 
        -- For example, look at the "To" header.

        PRINT 'MIME To header:'
        EXEC sp_OAMethod @mime, 'GetHeaderField', @sTmp0 OUT, 'To'
        PRINT @sTmp0
      END


    PRINT '---------------------------------'

    -- Finally, if the original email was attached, you can load it into another Chilkat Email object instance and
    -- do what you want with it..

    EXEC sp_OAGetProperty @email, 'NumAttachedMessages', @iTmp0 OUT
    IF @iTmp0 > 0
      BEGIN
        -- Get the 1st attachment message (assume we don't have more than one attached message)
        DECLARE @origEmail int
        EXEC sp_OAMethod @email, 'GetAttachedMessage', @origEmail OUT, 0
        EXEC sp_OAGetProperty @email, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN

            EXEC sp_OAGetProperty @origEmail, 'Subject', @sTmp0 OUT
            PRINT 'Attached message subject: ' + @sTmp0
            -- Do whatever else you want..
            EXEC @hr = sp_OADestroy @origEmail

          END
      END

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @h2t
    EXEC @hr = sp_OADestroy @mime


END
GO