SQL Server Requires Chilkat v11.0.0+

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