(SQL Server) Office365 SMTP Send Email Client Credentials

See more Office365 Examples

Demonstrates how to send email using the Office365 SMTP server with OAuth2 client credentials. This example uses a new feature available starting in Chilkat v9.5.0.97, which is to provide the client ID, client secret, and tenant ID to Chilkat, and internally Chilkat will fetch OAuth2 access token via client credentials as necessary. Therefore, it is no longer necessary for the application to explicitly fetch the OAuth2 access token beforehand.

Note: This requires the Azure Entra ID application to be configured for client credentials as detailed in this guide: Office365 App Setup for SMTP, POP, IMAP OAuth2 Client Credentials

Note: This example requires Chilkat v9.5.0.97 or greater.

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

    DECLARE @success int

    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.office365.com'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 587
    EXEC sp_OASetProperty @mailman, 'StartTLS', 1

    -- ----------------------------------------------------------------------------------------
    -- Important:
    -- Your Azure Entra ID app must be explicitly setup to allow for the OAuth2 client credentials flow.
    -- Please follow the detailed guide for how to do it here:
    -- Office365 App Setup for SMTP, POP, IMAP OAuth2 Client Credentials
    -- 
    -- Note: Your registered App must have the SMTP.SendAsApp permission.
    -- ----------------------------------------------------------------------------------------

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    -- Use your App's actual client ID here:
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_id', '2871da2c-8176-4b7f-869b-2311aa82e743'

    -- Use your App's actual client secret here:
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_secret', '2hu9Q~r5QuryUcEkNbg1btLtnfU1VUXzhSCG6brH'

    -- Use this specific scope.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'scope', 'https://outlook.office365.com/.default'

    -- Replace the tenant ID part of the URL with your tenant ID.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'token_endpoint', 'https://login.microsoftonline.com/114d7ed6-71bf-4dbe-a866-748364121bf2/oauth2/v2.0/token'

    -- Here is where we use a new feature introduced in Chilkat v9.5.0.97
    -- Instead of providing a previously fetched OAuth2 access token, we instead provide the information (above)
    -- required for Chilkat to automatically fetch the access token via client credentials.
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OASetProperty @mailman, 'OAuth2AccessToken', @sTmp0

    -- Use your Office365 email address for the SmtpUsername.
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'OFFICE365_EMAIL_ADDRESS'

    -- 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', 'MY_NAME <OFFICE365_EMAIL_ADDRESS>'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'John Doe', 'somebody@example.com'

    -- 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 @mailman
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

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

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


    PRINT 'Mail Sent!'

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


END
GO