Email Sending Example

Sending mail is one procedure call: sp_api_email. The database does not talk to a mail server itself. The message is handed to the platform's dispatcher, which sends it in the background — the same worker that also handles file and document jobs. Your script returns immediately.

The simplest call

DECLARE @To      NVARCHAR(MAX) = N'someone@example.com';
DECLARE @Subject NVARCHAR(4000) = N'Hello from TSQL.APP';
DECLARE @Body    NVARCHAR(MAX) = N'This message was sent by an action script.';

EXEC sp_api_email @to = @To, @subject = @Subject, @body = @Body;

A send-mail dialog

A complete action: the user enters an address and a message, and the script validates and sends.

-- Variables
DECLARE @To         NVARCHAR(MAX);
DECLARE @Message    NVARCHAR(MAX);
DECLARE @SendButton NVARCHAR(MAX);
DECLARE @Subject    NVARCHAR(4000);
DECLARE @Html       NVARCHAR(MAX);
DECLARE @Feedback   NVARCHAR(MAX);

-- Synchronise
EXEC sp_api_modal_get_value @name = N'@To',         @value = @To OUT;
EXEC sp_api_modal_get_value @name = N'@Message',    @value = @Message OUT;
EXEC sp_api_modal_get_value @name = N'@SendButton', @value = @SendButton OUT;

-- Draw
EXEC sp_api_modal_text   @text = N'Send a message', @class = N'h3';
EXEC sp_api_modal_input  @name = N'@To',      @value = @To OUT,      @placeholder = N'Recipient address';
EXEC sp_api_modal_input  @name = N'@Message', @value = @Message OUT, @placeholder = N'Your message', @rows = 5;
EXEC sp_api_modal_button @name = N'@SendButton', @value = N'Send',
     @valueout = @SendButton OUT, @class = N'btn-primary';

IF @SendButton IS NULL RETURN;

-- Validate
IF @To IS NULL OR @To NOT LIKE N'_%@_%._%' OR LEN(TRIM(ISNULL(@Message, N''))) = 0
BEGIN
    SET @Feedback = N'Enter a valid address and a message.';
    EXEC sp_api_toast @text = @Feedback, @class = N'bg-danger text-white';
    EXEC sp_api_modal_value @name = N'@SendButton', @value = NULL;
    RETURN;
END

-- Send
SET @Subject = CONCAT(N'A message from ', @user_name);
SET @Html = CONCAT(N'<p>', REPLACE((SELECT @Message FOR XML PATH('')), CHAR(10), N'<br>'), N'</p>');
EXEC sp_api_email @to = @To, @subject = @Subject, @html = @Html;

SET @Feedback = CONCAT(N'Message to ', @To, N' queued for sending.');
EXEC sp_api_toast @text = @Feedback, @class = N'bg-success text-white';
EXEC sp_api_modal_clear;

Test environments cannot email your customers

sp_api_email reads the application setting environment. Unless it is prod, the message is not sent to the recipients you named: it goes to the logged-in user instead, with the subject prefixed by Test environment and the original recipients listed in it, and copies are dropped. So a copy of a production database restored for testing cannot accidentally mail real customers.

If the setting does not exist yet, the first email creates it with the value test. Switch it to prod in api_settings when the application goes live. For the rare message that must reach its real recipient even from a test system, pass @ignore_test_environment = 1.

Details worth knowing

Plain text or HTML, not both. Fill @body for a plain message or @html for a formatted one.

Escape what users type. The HTML body above passes the message through FOR XML PATH(''), which escapes <, > and &, so a user cannot inject markup into the mail.

"Queued", not "delivered". sp_api_email returns as soon as the message is handed to the dispatcher. Say so in the confirmation — a toast that claims delivery is promising something the script cannot know yet.

@user_name is free. It is one of the context variables every action script receives, along with @user (the user's email address) and @id (the selected record). Use them; never declare them.

Keep addresses in settings. A fixed recipient — a sales inbox, an operations address — belongs in api_settings, not in the script, so it can be changed without editing code.