(SQL Server) Create Email with Non-Standard Binary Body

Creates an email where the only body is a binary WAV file. The technique used in the example could be applied to other binary files, such as PDF, MS-WORD docs, Excel docs, etc.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @mime int
    -- Use "Chilkat_9_5_0.Mime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Mime', @mime OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @mime, 'SetBodyFromFile', @success OUT, 'VoiceMessage.wav'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END

    -- The MIME has this header:
    -- Content-Disposition: attachment;
    -- 	 filename="VoiceMessage.wav"
    -- Content-Transfer-Encoding: base64
    -- Content-Type: audio/x-wav;
    -- 	name="VoiceMessage.wav"

    -- We don't want the content-disposition to be an
    -- attachment -- otherwise the email object will self-correct
    -- and put it in a multipart/mixed format...
    EXEC sp_OASetProperty @mime, 'Disposition', ''
    EXEC sp_OASetProperty @mime, 'Filename', ''

    DECLARE @strMime nvarchar(4000)

    EXEC sp_OAMethod @mime, 'GetMime', @strMime OUT

    PRINT @strMime

    -- Now load it into an email object:
    DECLARE @email int
    -- Use "Chilkat_9_5_0.Email" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    EXEC sp_OAMethod @email, 'SetFromMimeText', @success OUT, @strMime
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- Add subject, TO, FROM, etc.
    EXEC sp_OASetProperty @email, 'Subject', 'This is a test'
    EXEC sp_OASetProperty @email, 'From', 'support@chilkatsoft.com'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Matt', 'matt@chilkatsoft.com'

    -- Your email is ready to send.
    -- (but for this example, we'll simply save it to a file...)
    EXEC sp_OAMethod @email, 'SaveEml', @success OUT, 'email.eml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @email
        RETURN
      END


    PRINT 'OK!'

    EXEC @hr = sp_OADestroy @mime
    EXEC @hr = sp_OADestroy @email


END
GO