(SQL Server) Load .eml and Examine the Structure, Attachments, and Related Items

Demonstrates how to load examine the MIME structure of a .eml, and also examine the attachment and related item filenames, attached messages, and multipart/report and DSN information.

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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @emlPath nvarchar(4000)
    SELECT @emlPath = 'C:/AAWorkarea/beatrix/roesner.eml'

    DECLARE @mime int
    -- Use "Chilkat_9_5_0.Mime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Mime', @mime OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @mime, 'LoadMimeFile', @success OUT, @emlPath
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END


    PRINT '---- MIME structure ----'
    EXEC sp_OAMethod @mime, 'GetStructure', @sTmp0 OUT, 'text'
    PRINT @sTmp0

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

    DECLARE @email int
    -- Use "Chilkat_9_5_0.Email" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, @emlPath

    -- Was this a signed and/or encrypted email?
    -- If so, then loading the .eml automatically unwraps
    -- (i.e. verifies signatures and decrypts) and the resultant
    -- email is what existed prior to signing/encrypting.

    EXEC sp_OAGetProperty @email, 'ReceivedSigned', @iTmp0 OUT
    PRINT 'Email was Signed: ' + @iTmp0

    EXEC sp_OAGetProperty @email, 'ReceivedEncrypted', @iTmp0 OUT
    PRINT 'Email was Encrypted: ' + @iTmp0
    EXEC sp_OAGetProperty @email, 'ReceivedSigned', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN

        EXEC sp_OAGetProperty @email, 'SignaturesValid', @iTmp0 OUT
        PRINT 'Signature(s) valid = ' + @iTmp0
      END
    EXEC sp_OAGetProperty @email, 'ReceivedEncrypted', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN

        EXEC sp_OAGetProperty @email, 'Decrypted', @iTmp0 OUT
        PRINT 'Decrypted successfully = ' + @iTmp0
      END

    DECLARE @i int
    SELECT @i = 0
    DECLARE @numAttach int
    EXEC sp_OAGetProperty @email, 'NumAttachments', @numAttach OUT

    PRINT 'Number of attachments = ' + @numAttach

    WHILE @i < @numAttach
      BEGIN

        PRINT '---- Attachment ' + @i

        -- Examine the filename (if any)

        EXEC sp_OAMethod @email, 'GetAttachmentFilename', @sTmp0 OUT, @i
        PRINT 'filename: ' + @sTmp0
        -- Examine the content-ID (if any)

        EXEC sp_OAMethod @email, 'GetAttachmentContentID', @sTmp0 OUT, @i
        PRINT 'Content-ID: ' + @sTmp0
        -- Examine the content-type

        EXEC sp_OAMethod @email, 'GetAttachmentContentType', @sTmp0 OUT, @i
        PRINT 'Content-Type: ' + @sTmp0
        -- Examine the content-disposition

        EXEC sp_OAMethod @email, 'GetAttachmentHeader', @sTmp0 OUT, @i, 'content-disposition'
        PRINT 'Content-Disposition' + @sTmp0
        -- Examine the attachment size:

        EXEC sp_OAMethod @email, 'GetAttachmentSize', @iTmp0 OUT, @i
        PRINT 'Size (in bytes) of the attachment: ' + @iTmp0

        SELECT @i = @i + 1
      END

    PRINT '--'

    -- Now for the related items.

    -- Note: A MIME sub-part can potentially be both a related item AND an attachment.
    -- The typical case is when the item is contained under the multipart/related enclosure and 
    -- the item also has a "Content-Disposition" header indicating "attachment".
    -- The location within multipart/related makes it a "related item", yet the Content-Disposition can also make it semantically an attachment.
    -- Related items and attachments are not necessarily mutually exclusive.

    DECLARE @numRelated int
    EXEC sp_OAGetProperty @email, 'NumRelatedItems', @numRelated OUT

    PRINT 'Number of related items = ' + @numRelated
    SELECT @i = 0
    WHILE @i < @numRelated
      BEGIN

        PRINT '---- Related Item ' + @i

        -- Examine the filename (if any)

        EXEC sp_OAMethod @email, 'GetRelatedFilename', @sTmp0 OUT, @i
        PRINT 'filename: ' + @sTmp0
        -- Examine the content-ID (if any)

        EXEC sp_OAMethod @email, 'GetRelatedContentID', @sTmp0 OUT, @i
        PRINT 'Content-ID: ' + @sTmp0
        -- Examine the content-type

        EXEC sp_OAMethod @email, 'GetRelatedContentType', @sTmp0 OUT, @i
        PRINT 'Content-Type: ' + @sTmp0
        -- Examine the content-location (if any)

        EXEC sp_OAMethod @email, 'GetRelatedContentLocation', @sTmp0 OUT, @i
        PRINT 'Content-Location' + @sTmp0

        SELECT @i = @i + 1
      END

    -- The email could also have attached messages.
    -- An attached message is another email that was attached to this email.
    DECLARE @numAttachedMessages int
    EXEC sp_OAGetProperty @email, 'NumAttachedMessages', @numAttachedMessages OUT

    PRINT 'Number of attached messages = ' + @numAttachedMessages
    SELECT @i = 0
    WHILE @i < @numAttachedMessages
      BEGIN

        PRINT '---- Attached message ' + @i

        -- Examine the attached email
        DECLARE @em int
        EXEC sp_OAMethod @email, 'GetAttachedMessage', @em OUT, @i

        EXEC sp_OAGetProperty @em, 'From', @sTmp0 OUT
        PRINT 'from: ' + @sTmp0

        EXEC sp_OAGetProperty @em, 'Subject', @sTmp0 OUT
        PRINT 'subject: ' + @sTmp0
        EXEC @hr = sp_OADestroy @em

        SELECT @i = @i + 1
      END

    -- An email could also be a multipart/report email. 
    -- This is a DSN (Delivery Status Notification)
    -- The NumReports property indicates how many "reports" exist.
    DECLARE @numReports int
    EXEC sp_OAGetProperty @email, 'NumReports', @numReports OUT

    PRINT 'Number of reports = ' + @numReports
    SELECT @i = 0
    WHILE @i < @numReports
      BEGIN

        PRINT '---- Report ' + @i
        -- Get the raw report data...
        EXEC sp_OAMethod @email, 'GetReport', @sTmp0 OUT, @i
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    -- If the email is a multipart/report, then the information
    -- from the message/delivery-status part of the email can be retrieved:
    EXEC sp_OAMethod @email, 'IsMultipartReport', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN


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

      END

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


END
GO