SQL Server Requires Chilkat v11.0.0+
SQL Server
Find an email by subject and fetch it by IMAP UID
See more Email Object Examples
Search the selected INBOX by subject and fetch the first matching UID into an Email. The example requests complete attachment bodies and uses peek mode to preserve the read flag. Configure your own IMAP TLS endpoint and credentials.
Chilkat SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Assumes Chilkat is unlocked. See Global Unlock Sample.
DECLARE @imap int
EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @imap, 'Ssl', 1
EXEC sp_OASetProperty @imap, 'Port', 993
EXEC sp_OASetProperty @imap, 'RequireSslCertVerify', 1
EXEC sp_OASetProperty @imap, 'PeekMode', 1
EXEC sp_OASetProperty @imap, 'AutoDownloadAttachments', 1
EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.example.com'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
RETURN
END
EXEC sp_OAMethod @imap, 'Login', @success OUT, 'MAIL_USER', 'MAIL_PASSWORD'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
RETURN
END
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 @matches int
EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @matches OUT
EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, 'SUBJECT "Sample message"', 1, @matches
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @matches
RETURN
END
EXEC sp_OAGetProperty @matches, 'Count', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'No matching message in INBOX.'
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @matches
RETURN
END
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @matches
RETURN
END
-- The search returned UIDs. Use the same identifier type for the fetch.
DECLARE @uid int
EXEC sp_OAMethod @matches, 'GetId', @uid OUT, 0
DECLARE @email int
EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
EXEC sp_OAMethod @imap, 'FetchEmail', @success OUT, 0, @uid, 1, @email
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @matches
EXEC @hr = sp_OADestroy @email
RETURN
END
EXEC sp_OAGetProperty @imap, 'UidValidity', @iTmp0 OUT
PRINT 'Mailbox INBOX, UIDVALIDITY ' + @iTmp0 + ', UID ' + @uid
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @matches
EXEC @hr = sp_OADestroy @email
RETURN
END
-- The downloaded Email remains usable after disconnecting.
EXEC sp_OAGetProperty @email, 'Subject', @sTmp0 OUT
EXEC sp_OAGetProperty @email, 'NumAttachments', @iTmp0 OUT
PRINT 'Subject: ' + @sTmp0 + ' Files: ' + @iTmp0
EXEC sp_OAMethod @email, 'SaveEml', @success OUT, 'imap-received.eml'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @matches
EXEC @hr = sp_OADestroy @email
RETURN
END
PRINT 'Saved imap-received.eml.'
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @matches
EXEC @hr = sp_OADestroy @email
END
GO