SQL Server

Read Gmail POP3 Mailbox

Reads the header for each email in a GMail POP3 mailbox and displays the FROM and SUBJECT header fields. In your GMail "Forwarding and POP" settings, be sure to select "When messages are accessed with POP keep Gmail's copy in the Inbox". 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 @iTmp0 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 requires 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 GMail account POP3 properties.
    EXEC sp_OASetProperty @mailman, 'MailHost', 'pop.gmail.com'
    EXEC sp_OASetProperty @mailman, 'PopUsername', 'myLogin'
    EXEC sp_OASetProperty @mailman, 'PopPassword', 'myPassword'
    EXEC sp_OASetProperty @mailman, 'PopSsl', 1
    EXEC sp_OASetProperty @mailman, 'MailPort', 995

    -- Read mail headers and one line of the body.
    -- To get the full emails, set headersOnly = 0
    DECLARE @bundle int
    EXEC @hr = sp_OACreate 'Chilkat.EmailBundle', @bundle OUT

    DECLARE @keepOnServer int
    SELECT @keepOnServer = 1
    DECLARE @headersOnly int
    SELECT @headersOnly = 1
    DECLARE @numBodyLines int
    SELECT @numBodyLines = 1
    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

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

    EXEC sp_OAGetProperty @bundle, 'MessageCount', @iTmp0 OUT
    WHILE @i < @iTmp0
      BEGIN
        EXEC sp_OAMethod @bundle, 'EmailAt', @success OUT, @i, @email

        -- Display the From email address and the subject.

        EXEC sp_OAGetProperty @email, 'From', @sTmp0 OUT
        PRINT 'From: ' + @sTmp0

        EXEC sp_OAGetProperty @email, 'Subject', @sTmp0 OUT
        PRINT 'Subject: ' + @sTmp0

        SELECT @i = @i + 1
      END

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


END
GO