SQL Server

Download and Save Email Attachments (POP3)

See more POP3 Examples

Downloads emails from a POP3 mailbox and saves all attachments. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- The mailman object is used for receiving (POP3) 
    -- and sending (SMTP) email.
    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Set the POP3 server's hostname
    EXEC sp_OASetProperty @mailman, 'MailHost', 'pop.yourserver.com'

    -- Set the POP3 login/password.
    EXEC sp_OASetProperty @mailman, 'PopUsername', '***'
    EXEC sp_OASetProperty @mailman, 'PopPassword', '***'

    -- Copy the all email from the user's POP3 mailbox 
    -- into a bundle object.  The email remains on the server.
    -- FetchAll is a reasonable choice for POP3 maildrops that don't have too many
    -- emails. For larger mail drops, one might download emails one at a time..
    DECLARE @bundle int
    EXEC @hr = sp_OACreate 'Chilkat.EmailBundle', @bundle OUT

    DECLARE @keepOnServer int
    SELECT @keepOnServer = 1
    DECLARE @headersOnly int
    SELECT @headersOnly = 0
    -- Irrelevent because we are NOT downloading headers-only
    DECLARE @numBodyLines int
    SELECT @numBodyLines = 0
    EXEC sp_OAMethod @mailman, 'FetchAll', @success OUT, @keepOnServer, @headersOnly, @numBodyLines, @bundle
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @bundle
        RETURN
      END

    -- The directory path can be relative or absolute.
    -- This shows a Windows style directory path.  On other operating systems, the path
    -- would be different..
    DECLARE @dirPath nvarchar(4000)
    SELECT @dirPath = 'c:/myAttachments'

    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    DECLARE @bundleIndex int
    SELECT @bundleIndex = 0
    DECLARE @numMessages int
    EXEC sp_OAGetProperty @bundle, 'MessageCount', @numMessages OUT

    WHILE (@bundleIndex < @numMessages)
      BEGIN
        EXEC sp_OAMethod @bundle, 'EmailAt', @success OUT, @bundleIndex, @email

        -- Save all attachments to the specified directory.
        -- The directory is automatically created if it does not yet exist.
        EXEC sp_OAMethod @email, 'SaveAllAttachments', @success OUT, @dirPath
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @mailman
            EXEC @hr = sp_OADestroy @bundle
            EXEC @hr = sp_OADestroy @email
            RETURN
          END

        -- The OverwriteExisting property controls whether already-existing files
        -- are automatically overwritten.  By default, it is set to 1 so that existing
        -- files will be overwritten.

        -- Setting OverwriteExisting = 0 will cause the attachment-saving methods to generate
        -- unique filenames if a file with the same name already exists.  The actual filename(s)
        -- saved will be present by calling GetAttachmentFilename for each attachment *after*
        -- saving.
        -- For example...
        EXEC sp_OASetProperty @email, 'OverwriteExisting', 0
        EXEC sp_OAMethod @email, 'SaveAllAttachments', @success OUT, @dirPath
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @mailman
            EXEC @hr = sp_OADestroy @bundle
            EXEC @hr = sp_OADestroy @email
            RETURN
          END

        DECLARE @numAttachments int
        EXEC sp_OAGetProperty @email, 'NumAttachments', @numAttachments OUT
        DECLARE @attachIndex int
        SELECT @attachIndex = 0
        WHILE (@attachIndex < @numAttachments)
          BEGIN
            -- If the attachment filename was changed to prevent overwriting,
            -- GetAttachmentFilename will return the new filename.
            EXEC sp_OAMethod @email, 'GetAttachmentFilename', @sTmp0 OUT, @attachIndex
            PRINT @sTmp0
            SELECT @attachIndex = @attachIndex + 1
          END

        -- Attachments can also be saved individually.
        SELECT @attachIndex = 0
        WHILE (@attachIndex < @numAttachments)
          BEGIN


            EXEC sp_OAMethod @email, 'GetAttachmentFilename', @sTmp0 OUT, @attachIndex
            PRINT 'Original Filename: ' + @sTmp0
            EXEC sp_OAMethod @email, 'SaveAttachedFile', @success OUT, @attachIndex, @dirPath
            IF @success = 0
              BEGIN
                EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
                EXEC @hr = sp_OADestroy @mailman
                EXEC @hr = sp_OADestroy @bundle
                EXEC @hr = sp_OADestroy @email
                RETURN
              END

            -- If OverwriteExisting = 1, the saved filename will always equal the original filename,
            -- unless there are characters present in the filename that are not allowed by Windows,
            -- such as * ? < > | etc.  In those cases the illegal characters are either removed or replaced
            -- with underscore characters to allow the file to be saved.

            EXEC sp_OAMethod @email, 'GetAttachmentFilename', @sTmp0 OUT, @attachIndex
            PRINT 'Saved Filename: ' + @sTmp0
            SELECT @attachIndex = @attachIndex + 1
          END

        SELECT @bundleIndex = @bundleIndex + 1
      END

    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @bundle
    EXEC @hr = sp_OADestroy @email


END
GO