Writing to emails (Email.Send)

You can request an Email.Send provider from FINBOURNE and use it to send the results of your Luminesce queries in emails via SMTP.

Note

The LUSID user running the query must have sufficient access control permissions to use this provider. This should automatically be the case if you are the domain owner.

You can use this provider in conjunction with other providers to write the results of a query to an email and send this to a custom list of recipients. See example 2.

Setup

Prerequisites: An SMTP server account.

To get started using the Email.Send provider, you must supply your SMTP server account credentials to the Configuration Store. To do this:

  1. Sign in to the LUSID web app using the credentials of a LUSID administrator.

  2. From the top left menu, select System Settings > Configuration Store.

  3. Select the Add configuration set button.

  4. Set the Scope value to Luminesce-Provider and Code value to Email.

  5. Provide the following Key and Value pairs to the configuration set:

    • UserName: Your SMTP server account email address.

    • Password: Your SMTP server account password. Note you may need to create an app-specific password in order for your credentials to work.

    • ServerAddress: Your SMTP server address.

    • ServerPort: We currently support 587.

    • Type: We currently support Smtp.

    • SecureSocketOptions: We currently support StartTls.

    • AddressFrom: How your sender address should appear in emails, specified in the format [<text>](<your-email-address>).

  6. Click Save to create the configuration set.

Basic usage

Once you have supplied your SMTP server account credentials to the Configuration Store, you can begin using the Email.Send provider.

@data = select * from <some-provider> where <filter-expression>;

@email = 
use Email.Send with @data
--subject="<some-text>"
--<optional-arguments>

------------

<email-content-in-Markdown>

enduse;

select * from @email

Input tables

Email.Send takes in one or more input tables and outputs a table of data containing what was requested of SMTP and how it responded. It also sends an email with one or more attachments containing the input tables. See examples.

Options

Email.Send has options that enable you to refine a query.

An option takes the form --<option>=<value>, for example --attachAs=Csv. Note no spaces are allowed either side of the = operator. If an option takes a boolean value, then specifying that option (for example --allowFailure) sets it to True; omitting the option specifies False.

The table below provides information on the available options for Email.Send:

Option

Value

Status

Information

--subject

String          

Mandatory

The subject for the email.

--addressTo

<email-address> or [<name>](<email-address>)

Optional

Email addresses to send the email to.

--addressCc

Email addresses to CC into the email.

--addressBcc

Email addresses to BCC into the email.

--topN

Integer

When not attaching a table of data as a file, the maximum number of rows to show in any table. Defaults to 5.

--attachAs

Any of the following:

  • Csv

  • Excel

  • SqLite

  • Json

Attaches tables of data as files of this type.

--attachAsOneFileName

<filename.type>

When attaching more than one file, combines into one file with the filename you specify.

--allowFailure

Boolean

When omitted, the query produces an error if the email can’t be sent.

--ignoreOnZeroRows

Boolean

If specified, does not send an email if one or more tables are provided and all have no rows.

Examples

Example 1: Sending an email with a CSV attachment

In this example, we pass a table of data into the Email.Send provider and specify it should be attached as a CSV.

@table_of_data = select 'this' as MyFirstColumn
        union all
        select 'that' as MySecondColumn;

@email = 
use Email.Send with @table_of_data
--addressTo="[J Smith](jane.smith@acme.com)"
--attachAs=Csv
--subject="Here are the results of your query"

------------

Hello,

Please find the results of your query attached.

enduse;

select * from @email

The table of data returned by the query looks like this, showing us the SMTP request and how it responded:

The email recipient receives the following email, with a CSV file attached containing our table of data:

Example 2: Sending valuation results as an email

In this example, we use Lusid.Portfolio.Valuation to perform a valuation on a portfolio and pass the results into the Email.Send provider. We specify the results should be attached as an Excel file and sent to jane.smith@acme.com, as well as CCing joe.bloggs@acme.com.  

@table_of_data = select * from Lusid.Portfolio.Valuation
  where Recipe = 'FBNUniversity/Module-4-1Recipe'
    and PortfolioCode = 'Module-4-1'
    and PortfolioScope = 'FBNUniversity'
    and EffectiveAt = #2022-03-07#;

@email = 
use Email.Send with @table_of_data
--addressTo="[J Smith](jane.smith@acme.com)"
--addressCc="[J Bloggs](joe.bloggs@acme.com)"
--attachAs=Excel
--subject="Valuation results"

------------

Hello,

Here are the results of your valuation.

enduse;

select * from @email

The email recipients receive the following email, with an Excel file attached containing our table of data:

Example 3: Sending an email with many attachments

In this example, we pass two different tables of data into Email.Send and specify they should be attached as CSV files. We also use Markdown to explain the attachments in the email body.

@valuation_data = select * from Lusid.Portfolio.Valuation
  where Recipe = 'FBNUniversity/Module-4-1Recipe'
    and PortfolioCode = 'Module-4-1'
    and PortfolioScope = 'FBNUniversity'
    and EffectiveAt = #2022-03-07#;

@instrument_data = select * from Lusid.Instrument.Equity limit 10;

@email = 
use Email.Send with @valuation_data, @instrument_data
--addressTo="[J Smith](jane.smith@acme.com)"
--attachAs=Csv
--subject="Some results with more than one attachment"

------------

Hello,

Please find the following attached:
- The results of your valuation
- The first ten equity instruments

enduse;

select * from @email

The email recipient receives the following email, with formatted Markdown in the email body and two CSV files attached containing our tables of data:

Example 4: Using Email.Send in a custom view

In this example, we create a custom view using Sys.Admin.SetupView that takes in a LUID and an email recipient, retrieves data about the instrument, and then sends the results in an email.

To prevent issues occurring due to ---- being required by both the Sys.Admin.SetupView and Email.Send providers, we instead use @@separator = select '--' || '--' as x; to pass in ---- to the Email.Send provider as {@@separator}.

@x = use Sys.Admin.SetupView
--provider=Views.EmailInstruments
--parameters
LUID,Text,LUID_00003D5T,true
recipient,Text,[J Smith](jane.smith@acme.com),true

----

@data = select * from Lusid.Instrument where LusidInstrumentId = #PARAMETERVALUE(LUID);

@@recipient = #PARAMETERVALUE(recipient);

@@separator = select '--' || '--' as x;

@email = 
use Email.Send with @@separator, @data, @@recipient 
--addressTo="{@@recipient}"
--attachAs=Csv
--subject="Instruments from your view"
{@@separator}
Hello,

Here are your results.

enduse;

select * from @email where Ok = true;
enduse;
select * from @x

When the new custom view Views.EmailInstruments is called, the email recipient receives the following email which contains the table of data attached as a CSV file: