(SQL Server) Send Email to Distribution List

Sends the same email to a list of 1000 email addresses in 50 sends where each email has 20 recipients.

Note: Chilkat is not intended nor designed for mass emailing. A solution such as this might be used for a corporate emailing to employees, or an emailing to newsletter subscribers.

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

    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.MailMan', @mailman OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.mymailserver.com'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 465
    EXEC sp_OASetProperty @mailman, 'SmtpSsl', 1
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'myUsername'
    EXEC sp_OASetProperty @mailman, 'SmtpPassword', 'myPassword'

    -- Create a new email object
    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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', 'Senders Name <sender@example.com>'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Subscribers', 'subscribers@example.com'

    DECLARE @bdMime int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdMime OUT

    EXEC sp_OAMethod @mailman, 'RenderToMimeBd', @success OUT, @email, @bdMime

    -- Load a file containing one email address per line.
    DECLARE @distList int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringTable', @distList OUT

    EXEC sp_OAMethod @distList, 'AppendFromFile', @success OUT, 1000, 'utf-8', 'qa_data/distList.txt'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @distList, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @bdMime
        EXEC @hr = sp_OADestroy @distList
        RETURN
      END

    DECLARE @sbRecipients int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbRecipients OUT

    DECLARE @i int
    SELECT @i = 0
    DECLARE @szDistList int
    EXEC sp_OAGetProperty @distList, 'Count', @szDistList OUT
    DECLARE @j int
    SELECT @j = 0
    WHILE @i < @szDistList
      BEGIN

        -- Build a list of comma-separated recipients.
        IF @j > 0
          BEGIN
            EXEC sp_OAMethod @sbRecipients, 'Append', @success OUT, ','
          END
        EXEC sp_OAMethod @distList, 'StringAt', @sTmp0 OUT, @i
        EXEC sp_OAMethod @sbRecipients, 'Append', @success OUT, @sTmp0

        SELECT @i = @i + 1
        SELECT @j = @j + 1

        -- If we have 20 recipients, or we have the final recipient in the final chunk, then send.
        IF (@j = 20) or (@i = @szDistList)
          BEGIN
            EXEC sp_OAMethod @sbRecipients, 'GetAsString', @sTmp0 OUT
            EXEC sp_OAMethod @mailman, 'SendMimeBd', @success OUT, 'sender@example.com', @sTmp0, @bdMime
            IF @success <> 1
              BEGIN
                EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
                EXEC @hr = sp_OADestroy @mailman
                EXEC @hr = sp_OADestroy @email
                EXEC @hr = sp_OADestroy @bdMime
                EXEC @hr = sp_OADestroy @distList
                EXEC @hr = sp_OADestroy @sbRecipients
                RETURN
              END
            SELECT @j = 0
            EXEC sp_OAMethod @sbRecipients, 'Clear', NULL
          END
      END

    EXEC sp_OAMethod @mailman, 'CloseSmtpConnection', @success OUT
    IF @success <> 1
      BEGIN

        PRINT 'Connection to SMTP server not closed cleanly.'
      END


    PRINT 'Email sent to distirbution list.'

    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @bdMime
    EXEC @hr = sp_OADestroy @distList
    EXEC @hr = sp_OADestroy @sbRecipients


END
GO