Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only
Back to discussions
Expand all | Collapse all

Email via ExecuteHttpRequest

  • 1.  Email via ExecuteHttpRequest

    Posted Tue October 10, 2023 04:42 AM

    We are using the Planning Analytics Engine and are investigating sending messages to Slack/ Teams etc.. but also to replicate existing functionality of sending emails.

    I saw an old thread by @George Tonkin with a reply from @STUART KING:

    Planning Analytics

    Ibm remove preview
    Planning Analytics
    SummaryThe Cloud/containerized version of TM1 server is coming soon with the next release of Planning Analytics for Cloud Pak for Data. Stuart King, Senior Prod
    View this on Ibm >

    Which referenced the following:

    Email would need to be driven via the ExecuteHTTPRequest - Is IBM planning on creating a simple module that could be called from TI where you simply pass parameters for To, Subject, Body, Attachments, much the same way we do this now but leverage PowerShell to send the mails?

     We will provide sample Turbo Integrator code to show how the ExecuteHTTPRequest feature can be used with services, including an e-mail service.  We are not planning on building an e-mail service into Planning Analytics Engine.  We would consider providing a preconfigured e-mail service as part of a SaaS offering with Planning Analytics Engine.

    Can we get the sample code? Are there any other samples available to showcase/ reveal the power/ potential of executehttprequest?



    ------------------------------
    Edward Stuart
    ------------------------------


  • 2.  RE: Email via ExecuteHttpRequest

    Posted Wed October 18, 2023 12:00 PM

    Hi,

    Any responses on example code for how to send email via ExcecuteHttpRequest? 

    Currently unable to find resources with such examples



    ------------------------------
    Dylan Janse van Rensburg
    ------------------------------

    Original Message


  • 3.  RE: Email via ExecuteHttpRequest

    Posted Thu October 19, 2023 02:22 AM

    At TechExchange the following path for sending emails from PS aaS was outlined. 

    I did not see any code examples of this, but it does mention the AWS services that would be involved. 

    I'm by no means an expert on AWS services (working in MS country), so I won't be of much more help, but hoping it will help for someone knowing more about the AWS services :-) 



    ------------------------------
    Emil Malmberg Fosdal
    Solution Architect
    CogniTech A/S
    ------------------------------

    Original Message


  • 4.  RE: Email via ExecuteHttpRequest

    Posted Thu October 19, 2023 04:49 AM

    Anecdotally I have heard that this is working against Gmail and that it is complex.

    In theory, the Microsoft Graph API should work easily enough once you have an access token:

    https://learn.microsoft.com/en-us/graph/api/user-sendmail?view=graph-rest-1.0&tabs=http

    We also have a number of workflows that fit in and around Power Automate and this could also be a workable solution

    https://make.powerautomate.com/galleries/public/templates/678bc9ff05b04fdbb29f1e581fc5349f

    I've not seen anything as yet that could be a universal solution for all environments



    ------------------------------
    Edward Stuart
    ------------------------------

    Original Message


  • 5.  RE: Email via ExecuteHttpRequest

    Posted Thu October 19, 2023 06:01 AM

    There are many e-mail delivery services out there that have REST API that can all be interacted with using ExecuteHttpRequest (services like SendGrid, MailChimp, etc.). Amazon, if you don't have an Amazon WorkMail account already in which case for low volumes you might be able to simply us that, has Amazon SES, Simple Email Service, that can be utilized (and which WorkMail utilizes under the covers as well) to get e-mail send anywhere.



    ------------------------------
    Hubert Heijkers
    STSM, Program Director TM1 Functional Database Technology and OData Evangelist
    ------------------------------

    Original Message


  • 6.  RE: Email via ExecuteHttpRequest

    Posted Thu October 19, 2023 09:50 AM
      |   view attached

    Thanks Hubert,

    SendGrid was very easy to setup and get going.

    • Registered
    • Created API Key
    • Created Send Email Process with the following code:

    sURL = 'https://api.sendgrid.com/v3/mail/send';
    sDimClients = '}Clients';
    sUser = TM1User;

    vSendGridAPIKey = ATTRS( sDimClients, sUser, 'SendGrid_APIKEY');

    vBody = 
    '{"personalizations": [{"to": [{"email": "edward.stuart@spitfire-analytics.com"}]}],"from": {"email": "edward.stuart@spitfire-analytics.com"},"subject": "Sending with SendGrid is Fun","content": [{"type": "text/plain", "value": "and easy to do anywhere, even with the IBM Planning Analytics Engine"}]}''';

    ExecuteHttpRequest( 
      'POST',
      sURL,
      '-h Content-Type:application/json',
      '-h Authorization: Bearer ' | vSendGridAPIKey,
      '-d ' | vBody
    );

    The attached file is the resultant email



    ------------------------------
    Edward Stuart
    ------------------------------

    Original Message


  • 7.  RE: Email via ExecuteHttpRequest

    Posted Thu October 19, 2023 11:46 AM
    Edited by Hubert Heijkers Thu October 19, 2023 11:50 AM

    Cool, it should indeed not be any harder then that. And with the upcoming support for JSON in TI constructing that JSON becomes even nicer as well!

    Just to give you an idea, building the body using those new JSON function would look something like:

    vBody = JsonAdd( '{}', '/personalizations/to/email', StringToJson( 'edward.stuart@spitfire-analytics.com' ));
    vBody = JsonAdd( vBody, '/from/email', StringToJson( 'edward.stuart@spitfire-analytics.com' ));
    vBody = JsonAdd( vBody, '/subject', StringToJson( 'Sending with SendGrid is Fun' ));
    vBody = JsonAdd( vBody, '/content/0', '{ "type": "text/plain", "value": "and easy to do anywhere, even with the IBM Planning Analytics Engine" }' );

    It's still TI so everything is still strings, and any JSON you have in a string can be used as a JSON value itself as well (the contents in the last line which once could have constructed as well).

    PS actually tried SendGrid last week, to give such an example myself, but it didn't like me (or better any of my e-mail addresses I suppose) as it refused to create an account and let me in (the msg, vague as it was, kind of implied I didn't make it past their balloting committee<LOL>).



    ------------------------------
    Hubert Heijkers
    STSM, Program Director TM1 Functional Database Technology and OData Evangelist
    ------------------------------

    Original Message


  • 8.  RE: Email via ExecuteHttpRequest

    Posted Fri October 20, 2023 02:28 AM

    I also tried with a number of the mailer websites (SendGrid, Mailchimp, etc) and all of them gave me a vague, thanks but no thanks.

    I turned to the Microsoft Graph api;

    https://learn.microsoft.com/en-us/graph/api/user-sendmail?view=graph-rest-1.0&tabs=http

    You have to manage a token, but this was easier than gmail.

    ### API_Get_Token ###
sCode = CELLGETS( 'sys.API_Assumptions', 'Code', 'Azure');
sClient_id= CELLGETS( 'sys.API_Assumptions', 'Client_id', 'Azure');
sTenant_id= CELLGETS( 'sys.API_Assumptions', 'tenant_id', 'Azure');
sRedirect_uri= CELLGETS( 'sys.API_Assumptions', 'redirect_uri', 'Azure');

sURL = 'https://login.microsoftonline.com/' | sTenant_id | '/oauth2/v2.0/token';

sHeader = '-h Content-type: application/x-www-form-urlencoded';

sJSON = '-d client_id=' | sClient_id;

sJSON = sJSON | '&scope=mail.send offline_access';

sJSON = sJSON | '&code=' | sCode;

sJSON = sJSON | '&redirect_uri=' | sRedirect_uri;

sJSON = sJSON | '&grant_type=authorization_code';


ExecuteHttpRequest( 'POST',  sURL , sHeader, sJSON); 
#, '-o token.txt' );

sTokentext = HttpResponseGetBody();


    sTokenStr = '"access_token":"';
    sTokenStart = SCAN(  sTokenStr, sTokentext);
    sTokenLen = LONG( sTokenStr);
    sTokenStarttoEnd = SUBST( sTokentext, sTokenStart+sTokenLen, LONG( sTokentext));
    sTokenEnd = SCAN( '"', sTokenStarttoEnd)-1;
    sToken = SUBST( sTokenStarttoEnd, 1, sTokenEnd);
    CELLPUTS( sToken, 'sys.API_Assumptions', 'token', 'Azure');
  
    sTokenStr = '"refresh_token":"';
    sTokenStart = SCAN(  sTokenStr, sTokentext);
    sTokenLen = LONG( sTokenStr);
    sTokenStarttoEnd = SUBST( sTokentext, sTokenStart+sTokenLen, LONG( sTokentext));
    sTokenEnd = SCAN( '"', sTokenStarttoEnd)-1;
    sToken = SUBST( sTokenStarttoEnd, 1, sTokenEnd);
    CELLPUTS( sToken, 'sys.API_Assumptions', 'refresh_token', 'Azure');


    ### API_Send_Email ###

ExecuteProcess( 'API_Refresh_Token');


sToken = CELLGETS( 'sys.API_Assumptions', 'token', 'Azure');

sURL = 'https://graph.microsoft.com/v1.0/me/sendMail';

sHeader = '-h Content-type: application/json';
sHeader1 = '-h Authorization: Bearer ' | sToken;


sJSON = '-d {  "message": {   ';

sJSON = sJSON | ' "subject": "' | pSubject | '",   ';

sJSON = sJSON | ' "body": {      "contentType": "HTML",      "content": "' | pBody | '"    },   ';

sJSON = sJSON | ' "toRecipients": [      {        "emailAddress": {          "address": "' | pTo | '"        }      }    ] ';

sJSON = sJSON | ' }, ';

sJSON = sJSON | '  "saveToSentItems": "true" } ';



ExecuteHttpRequest( 'POST',  sURL , sHeader, sHeader1, sJSON );

    I have a few TIs that connect to APIs, and I've added a check around the token call so it only calls the token refresh if it hasn't been refreshed within a defined timeframe.

    Happy coding.

    Karl Blackler

    Head of Product Development

    Cortell Australia Pty Limited



    ------------------------------
    Karl Blackler
    ------------------------------

    Original Message


  • 9.  RE: Email via ExecuteHttpRequest

    Posted Fri October 20, 2023 02:34 AM

    Thanks everyone!

    Managed to get e-mails working via the Microsoft Graph API. 

    I was wondering if anyone has had to add an attachment to an e-mail, as we have this requirement. We would usually make use of a PowerShell script to accomplish this, as I do believe in most cases you will have to convert the file to bytes, and send the bytes over as the attachment. 

    Is there a way to convert a file to bytes in Tm1/PA Engine v12? 

    Apologies if this is common knowledge, tried my luck in googling and I could not find anything.



    ------------------------------
    Dylan Janse van Rensburg
    ------------------------------

    Original Message


  • 10.  RE: Email via ExecuteHttpRequest

    Posted Mon October 23, 2023 02:31 AM

    Dylan

    I want to second this question. 

    @Hubert Heijkers Is there a possibility to pick up a file, stream to to bytes and send in a http request?. If not this such addition be really helpful for multiple use-cases.

    I noted above that json parsing is coming. THANK you for that - parsing json with scan() and subst() is a hazzle. :-)



    ------------------------------
    David Pretorius
    ------------------------------

    Original Message


  • 11.  RE: Email via ExecuteHttpRequest

    Posted Tue October 24, 2023 03:24 PM

    Hi David,

    I'm guessing that API to send those e-mails has a way to inject attachments as 'part' of the body, potentially even base64url encoded or something I'm guessing (Microsoft Graph API is OData compliant as well;-).

    That would, today, still be a bit of a challenge in TI. And since these attachments obviously can be large, if not huge, I can't really think of a simply fix to add this to TI either. 

    I'll do a write-up for this to not forget and will discuss with the team but I think the real solution, I do have one in mind based on some other work I'm experimenting with (so early days), will take a tad longer. TBC.



    ------------------------------
    Hubert Heijkers
    STSM, Program Director TM1 Functional Database Technology and OData Evangelist
    ------------------------------

    Original Message


  • 12.  RE: Email via ExecuteHttpRequest

    Posted Tue May 20, 2025 12:38 PM

    Any follow-up on this item?  Looking to attach files to emails using Microsoft GraphAPI.  I have emails working, sending HTML body, but need a way of converting file contents to Base64 bytes to send attachments.



    ------------------------------
    Chad Harvey
    ------------------------------

    Original Message


  • 13.  RE: Email via ExecuteHttpRequest

    Posted Tue May 20, 2025 05:07 PM

    I did some thinking and came up with a quasi-solution to the attachment issue.  As part of my email TI process I am looking to see if there is an attachment.  If there is, I am calling a sub-process to read the file and Base64Encode the contents.  I then store that encoded string in a Control Cube, to be retrieved and sent using the calling process.

    For the Parsing TI process, I set the data source to File and used the '~' character as my delimiter (knowing it wasn't used in my files).  I added a single variable "vString" to read each line of the file in the Data section.

    Process:

    #Region ############################################# HEADER #############################################
    # Process: System - Parse File
    # Purpose: Parse file for Attachment in Office 365
    # Date Created: 2025.05.20 (ACGI)
    # Last Update: 2025.05.20
    # Notes: 
    #     2025.05.20 (ACGI) - Initial Process Creation
    #EndRegion

    #Region --------------- Variables ---------------
    sString = '';
    DatasourceNameforServer = pFile;
    #EndRegion

    #Section Metadata

    #Section Data

    sString = sString | vString |CHAR(10);

    #Section Epilog

    sString_Enc = Base64Encode( sString);

    CellPutS(sString_Enc, '}System - Parameters', 'Email_String', 'String');

    Email calling process relevant logic:

    sString = CellgetS('}System - Parameters', 'Email_String', 'String');
        sJSONBody =   '-d ' | '{
          "message": {
            "subject": "' | pSubject | '",
            "body": {
              "contentType": "html",
              "content": "' | pBody | '"
            },
            "toRecipients": [
              {
                "emailAddress": {
                  "address": "' | pEmailAddress | '"
                }
              }
            ],
            "attachments": [
                {
                    "@odata.type": "#microsoft.graph.fileAttachment",
                    "name": "' | sAttach | '",
                    "contentType": "text/plain",
                    "contentBytes": "' | sString | '"
                }
            ]
          },
          "saveToSentItems": "false"
        }';



    ------------------------------
    Chad Harvey
    ------------------------------

    Original Message


  • 14.  RE: Email via ExecuteHttpRequest

    Posted Tue May 20, 2025 06:55 PM

    Great job, Chad!

    For anyone using IBM Planning Analytics Cloud or Local (v11), here's a quick example of how you can send emails with attachments using the SPACE API:



    ------------------------------
    Vlad Didenko
    Founder at Succeedium
    TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
    https://succeedium.com/teamone/
    Succeedium Planning Analytics Cloud Extension
    https://succeedium.com/space/
    ------------------------------

    Original Message


  • 15.  RE: Email via ExecuteHttpRequest

    Posted Wed May 21, 2025 01:33 AM

    Hello Chad,

    That's a good workaround for:

    • very small files
    • text files
    • accessible from the server

    Any other file for an attachment would need to have a different solution and real solution, as part of IBM PA, since tools like PowerShell are out of the question in V12. Hope that IBM can provide something in the near future since this is an added benefit in ANY PA project. For instance, sending log files could be done with the REST API to retrieve the contents but anything beyond is becomes a problem.



    ------------------------------
    Wim Gielis
    Senior Consultant
    Aexis International
    Sint-Stevens-Woluwe
    +32496225001
    ------------------------------

    Original Message


  • 16.  RE: Email via ExecuteHttpRequest

    Posted Sun October 29, 2023 12:45 PM

    I was able to implement the GMail and Slack integration in under 40 minutes (that includes researching and configuring the API tokens):

    The next video shows how to send emails from TI processes:

    You can also start your own SMPT server and attach files:

    import smtplib
from email.message import EmailMessage
with open('Your TM1 data.csv') as f:
    msg = EmailMessage()
    msg.set_content(f.read())
    msg['Subject'] = 'TM1 data email delivery'
    msg['From'] = 'space@succeedium.com'
    msg['To'] = ['tm1-support@succeedium.com','recipient2@succeedium.com']
    s = smtplib.SMTP('localhost')
    s.send_message(msg)
    s.quit()

    The next video shows how to send Slack messages from TI processes:

    Using the same idea, you can integrate MS Teams or ANYTHING that has REST API or speaks Python



    ------------------------------
    Vlad Didenko
    Founder at Succeedium
    TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
    https://succeedium.com/teamone/
    Succeedium Planning Analytics Cloud Extension
    https://succeedium.com/space/
    ------------------------------

    Original Message


Related Content