SQL Server

GMail SMTP Send using Previously Obtained OAuth2 Access Token

See more GMail SMTP/IMAP/POP Examples

Demonstrates how to send email using GMail and OAuth2 authentication. This example assumes an OAuth2 access token was previously obtained. SQL Server

-- 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)
    DECLARE @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- See Get GMail SMTP Access Token 
    -- for sample code showing how to obtain a GMail SMTP access token for desktop apps.

    -- In this example, our access token was previously saved to the following file which contains JSON like this:
    -- 	{
    -- 	 "access_token": "ya39.Ci-XA_C5bGgRDC3UaD-h0_NeL-DVIQnI2gHtBBBHkZzrwlARkwX6R3O0PCDEzRlfaQ",
    -- 	 "token_type": "Bearer",
    -- 	 "expires_in": 3600,
    -- 	 "refresh_token": "1/r_2c_7jddspcdfesrrfKqfXtqo08D6Q-gUU0DsdfVMsx0c"
    -- 	}
    -- 
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/tokens/_gmailSmtp.json'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load GMail access token file.'
        EXEC @hr = sp_OADestroy @json
        RETURN
      END
    DECLARE @accessToken nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @accessToken OUT, 'access_token'

    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT

    -- Set the properties for the GMail SMTP server:
    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.gmail.com'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 587
    EXEC sp_OASetProperty @mailman, 'StartTLS', 1

    -- The SMTP username should be the GMail address of the user's account that authorized your app to send email.
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'chilkat.support@gmail.com'
    EXEC sp_OASetProperty @mailman, 'OAuth2AccessToken', @accessToken

    -- Create a new email object
    DECLARE @email int
    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 <chilkat.support@gmail.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.
    -- The connection (i.e. session) to the SMTP server remains
    -- open so that subsequent SendEmail calls may use the
    -- same connection.  
    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 @json
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- You may close the connection here.  If the connection is kept open, 
    -- the next call to mailman.SendEmail will continue using the already-established connection
    -- (and automatically re-connect if needed).
    EXEC sp_OAMethod @mailman, 'CloseSmtpConnection', @success OUT
    IF @success <> 1
      BEGIN

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


    PRINT 'Email Sent via GMail with OAuth2 authentication.'

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @email


END
GO