(SQL Server) Send Email and Put Copy in "Sent" Folder

This example demonstrates how to send an email, and then put a copy of it in the "Sent" mail folder. This is only possible if the "Sent" folder is located on the mail server and accessed via the IMAP protocol. The POP3 protocol can only access the Inbox on the mail server.

This does not work with "Sent", "Sent Items", or "Sent Messages" folders that are stored locally on your computer by an email client such as Outlook or Thunderbird. Chilkat has no ability to copy email to the local files managed by these email clients.

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 @success int

    -- The mailman object is used for sending and receiving email.
    DECLARE @mailman int
    -- Use "Chilkat_9_5_0.MailMan" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Set the SMTP server.
    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.my-mail-server.com'

    -- Set the SMTP login/password (if required)
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'myUsername'
    EXEC sp_OASetProperty @mailman, 'SmtpPassword', 'myPassword'

    -- Create a new 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_OASetProperty @email, 'Subject', 'This is a test'
    EXEC sp_OASetProperty @email, 'Body', 'This is a test'
    EXEC sp_OASetProperty @email, 'From', 'Chilkat Support <support@chilkatsoft.com>'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Chilkat Admin', 'admin@chilkatsoft.com'
    -- To add more recipients, call AddTo, AddCC, or AddBcc once per recipient.

    -- Call SendEmail to connect to the SMTP server and send.
    EXEC sp_OAMethod @mailman, 'SendEmail', @success OUT, @email
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- Some SMTP servers do not actually send the email until 
    -- the connection is closed.  In these cases, it is necessary to
    -- call CloseSmtpConnection for the mail to be  sent.  
    -- Most SMTP servers send the email immediately, and it is 
    -- not required to close the connection.  We'll close it here
    -- for the example:
    EXEC sp_OAMethod @mailman, 'CloseSmtpConnection', @success OUT
    IF @success <> 1
      BEGIN

        PRINT 'Connection to SMTP server not closed cleanly.'

      END


    PRINT 'Mail Sent!'

    -- The mail has been sent.  Now save the email to the "Sent" folder
    -- on your mail server.  (Your "Sent" folder could be named something else,
    -- or you may store the email in any folder of your choosing.)
    DECLARE @imap int
    -- Use "Chilkat_9_5_0.Imap" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT

    -- Connect to an IMAP server.
    -- Use TLS
    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.my-mail-server.com'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Login
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'myLogin', 'myPassword'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Upload (save) the email to the "Sent" mailbox.
    EXEC sp_OAMethod @imap, 'AppendMail', @success OUT, 'Sent', @email
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END


    PRINT 'Email saved to the Sent folder.'

    -- Disconnect from the IMAP server.
    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @imap


END
GO