(SQL Server) Scan for Emails with Attachments and Save Attachments to Files

Scan for emails with attachments and save attachments.

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 @imap int
    -- Use "Chilkat_9_5_0.Imap" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Connect to an IMAP server.
    -- Use TLS
    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    DECLARE @success int
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.someMailServer.com'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Login
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'myLogin', 'myPassword'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Select an IMAP mailbox
    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    DECLARE @messageSet int

    -- We can choose to fetch UIDs or sequence numbers.
    DECLARE @fetchUids int
    SELECT @fetchUids = 1

    -- Get the message IDs of all the emails in the mailbox
    EXEC sp_OAMethod @imap, 'Search', @messageSet OUT, 'ALL', @fetchUids
    EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Fetch the email headers into a bundle object:
    DECLARE @bundle int

    EXEC sp_OAMethod @imap, 'FetchHeaders', @bundle OUT, @messageSet
    EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC @hr = sp_OADestroy @messageSet

        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Scan for emails with attachments, and save the attachments
    -- to a sub-directory.
    DECLARE @i int

    EXEC sp_OAGetProperty @bundle, 'MessageCount', @iTmp0 OUT
    SELECT @i = 0
    WHILE @i <= @iTmp0 - 1
      BEGIN
        DECLARE @email int

        EXEC sp_OAMethod @bundle, 'GetEmail', @email OUT, @i

        -- Does this email have attachments?
        -- Use GetMailNumAttach because the attachments
        -- are not actually in the email object because
        -- we only downloaded headers.
        -- (Had we downloaded the full emails by
        -- calling mailman.FetchBundle, we could look
        -- at the email object's NumAttachments property.)
        DECLARE @numAttach int
        EXEC sp_OAMethod @imap, 'GetMailNumAttach', @numAttach OUT, @email

        IF @numAttach > 0
          BEGIN
            -- Download the entire email and save the
            -- attachments. (Remember, we 
            -- need to download the entire email because
            -- only the headers were previously downloaded.
            -- If the entire emails were downloaded by
            -- calling FetchBundle instead of FetchHeaders,
            -- this would not be necessary.

            -- The ckx-imap-uid header field is added when
            -- headers are downloaded.  This makes it possible
            -- to get the UID from the email object.
            DECLARE @uidStr nvarchar(4000)
            EXEC sp_OAMethod @email, 'GetHeaderField', @uidStr OUT, 'ckx-imap-uid'
            DECLARE @uid int
            SELECT @uid = @

            DECLARE @fullEmail int

            EXEC sp_OAMethod @imap, 'FetchSingle', @fullEmail OUT, @uid, 1
            EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
            IF @iTmp0 = 1
              BEGIN
                EXEC sp_OAMethod @fullEmail, 'SaveAllAttachments', @success OUT, 'attachmentsDir'
                EXEC @hr = sp_OADestroy @fullEmail

              END

            DECLARE @j int

            SELECT @j = 0
            WHILE @j <= @numAttach - 1
              BEGIN
                DECLARE @filename nvarchar(4000)
                EXEC sp_OAMethod @imap, 'GetMailAttachFilename', @filename OUT, @email, @j

                PRINT @filename
                SELECT @j = @j + 1
              END

          END
        EXEC @hr = sp_OADestroy @email

        SELECT @i = @i + 1
      END

    -- Disconnect from the IMAP server.
    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

    EXEC @hr = sp_OADestroy @messageSet

    EXEC @hr = sp_OADestroy @bundle


    EXEC @hr = sp_OADestroy @imap


END
GO