SQL Server
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 SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- 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 @email int
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
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 @jsonDsnInfo int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonDsnInfo OUT
EXEC sp_OAMethod @email, 'GetDsnInfo', @success OUT, @jsonDsnInfo
EXEC sp_OASetProperty @jsonDsnInfo, 'EmitCompact', 0
EXEC sp_OAMethod @jsonDsnInfo, 'Emit', @sTmp0 OUT
PRINT @sTmp0
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
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 @hr = sp_OACreate 'Chilkat.Email', @origEmail OUT
EXEC sp_OAMethod @email, 'GetAttachedEmail', @success OUT, 0, @origEmail
IF @success = 1
BEGIN
EXEC sp_OAGetProperty @origEmail, 'Subject', @sTmp0 OUT
PRINT 'Attached message subject: ' + @sTmp0
-- Do whatever else you want..
END
END
EXEC @hr = sp_OADestroy @email
EXEC @hr = sp_OADestroy @h2t
EXEC @hr = sp_OADestroy @jsonDsnInfo
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @origEmail
END
GO