SQL Server Requires Chilkat v11.0.0+
SQL Server
Read email headers and display the body as text
See more Email Object Examples
Read the sender, recipients, subject, date, and an optional custom header from an EML file. Display the plain-text body when available, or convert HTML to text. Handle messages that contain neither body format.
Chilkat SQL Server Downloads
-- 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
DECLARE @sTmp0 nvarchar(4000)
DECLARE @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Assumes Chilkat is unlocked. See Global Unlock Sample.
DECLARE @email int
EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'with-attachments.eml'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @email
RETURN
END
EXEC sp_OAGetProperty @email, 'From', @sTmp0 OUT
PRINT 'From: ' + @sTmp0
EXEC sp_OAGetProperty @email, 'Subject', @sTmp0 OUT
PRINT 'Subject: ' + @sTmp0
EXEC sp_OAGetProperty @email, 'EmailDateStr', @sTmp0 OUT
PRINT 'Date: ' + @sTmp0
DECLARE @i int
SELECT @i = 0
EXEC sp_OAGetProperty @email, 'NumTo', @iTmp0 OUT
WHILE (@i < @iTmp0)
BEGIN
EXEC sp_OAMethod @email, 'GetToName', @sTmp0 OUT, @i
EXEC sp_OAMethod @email, 'GetToAddr', @sTmp1 OUT, @i
PRINT 'To: ' + @sTmp0 + ' ' + @sTmp1
SELECT @i = @i + 1
END
DECLARE @referenceId nvarchar(4000)
EXEC sp_OAMethod @email, 'GetHeaderField', @referenceId OUT, 'X-Reference-ID'
EXEC sp_OAGetProperty @email, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 1
BEGIN
PRINT 'Reference: ' + @referenceId
END
EXEC sp_OAMethod @email, 'HasPlainTextBody', @iTmp0 OUT
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @email, 'GetPlainTextBody', @sTmp0 OUT
PRINT @sTmp0
END
ELSE
BEGIN
EXEC sp_OAMethod @email, 'HasHtmlBody', @iTmp0 OUT
IF @iTmp0 = 1
BEGIN
DECLARE @reader int
EXEC @hr = sp_OACreate 'Chilkat.HtmlToText', @reader OUT
EXEC sp_OAMethod @email, 'GetHtmlBody', @sTmp1 OUT
EXEC sp_OAMethod @reader, 'ToText', @sTmp0 OUT, @sTmp1
PRINT @sTmp0
END
ELSE
BEGIN
PRINT 'No text or HTML body.'
END
END
EXEC @hr = sp_OADestroy @email
EXEC @hr = sp_OADestroy @reader
END
GO