Skip to content

Emails

jetQuery has a built-in:

  • Mailing service that is fully customizable, with support for:
    • sending emails using SMTP and receiving emails using POP3,
    • mail storage,
    • multiple email accounts.
  • System service which sends emails using SMTP for system messages like password recovery and notifications.

Mailing service

REST API

  • POST {{host}}/api/core/mail/send/{account} - send a single email using the specified account.
  • GET/POST {{host}}/api/core/mail/pull/{account?} - pull emails; the account is optional.
  • Experimental GET/POST {{host}}/api/core/mail/push/{account?} - push emails; the account is optional.
  • Experimental GET/POST {{host}}/api/core/mail/sync/{account?} - pull and push emails; the account is optional.

Data objects

  • [app].[mail] - mail storage for incoming and outgoing emails.
    • [app].[mail].[mail_attachments] - XML field attachments.
    • [app].[mail].[mail_body_text] - text only.
    • [app].[mail].[mail_body_html] - safe HTML representation.
  • [app].[files] - file storage.

Notes: Mails can be put directly into storage and sent using the push or sync API.

Mail accounts configuration

Mail account configuration is stored in team settings: Settings+Teams+Team Secret . The team type must be set to mail to be able to store mail account configuration. To send or receive emails you need to be part of the mail team

Example configuration

JSON
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
{
  "account": "example_account",
  "mail_pop3_server": "pop3.example.com",    
  "mail_pop3_port": 110,
  "mail_smtp_server": "smtp.example.com",
  "mail_smtp_port": 25,
  "mail_smtp_valid_cert": true,
  "mail_usessl": false,
  "mail_starttls": false,
  "mail_username": "example_username",
  "mail_password": "example_password",
  "mail_from": "[email protected]",
  "mail_from_name": "Example Sender"
}

Office365 example configuration

  • Shared mailbox send is supported in this scenario
    • mail_from - your shared mailbox email
    • mail_username - your real email
  • Authentication must be enabled
  • POP3 must be enabled in the options.
JSON
1
2
3
4
5
6
7
8
"system": {
    "mail_from": "[email protected]",     //or shared mailbox email
    "mail_username": "[email protected]", //your account name 
    "mail_smtp_server": "outlook.office365.com",
    "mail_smtp_port": 587,
    "mail_password": "",
    "mail_usessl": false,
}

Google Workspace / Gmail configuration

  • POP3 must be enabled in the options.
  • less secure apps option must be enabled in google account settings > security
  • There must be a 'recent:' prefix before the username, otherwise you might receive, first 270 messages unread emails only.
  • App passwords must be set if two factor authentication is enabled. An app password is a 16-digit passcode https://myaccount.google.com/apppasswords
JSON
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
{
  "mail_pop3_server": "pop.gmail.com",    
  "mail_pop3_port": 995,
  "mail_smtp_server": "smtp.gmail.com",
  "mail_smtp_port": 587,
  "mail_starttls": true,
  "mail_username": "recent:[email protected]",
  "mail_password": "xxxxx",
  "mail_from": "[email protected]",
  "mail_from_name": "Your company name"
}

Examples

Pull email example

HTTP
1
2
3
GET {{host}}/api/core/mail/pull/{{mailaccount}}
Authorization: Token {{token}}
Content-Type: application/json

Optional query parameters:

  • debug=true - debug mode, usefull in case

Send email example

HTTP
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
POST {{host}}/api/core/mail/send/e-invoice
Authorization: Token {{token}}
Content-Type: application/json

{
  "mail_subject": "Document attached F001/202401",
  "mail_to": "[email protected]",
  "mail_cc": "[email protected]",
  "mail_body_text": "Przesyłam dokument \n\nPozdrawiama\n",
  "mail_remote_id": "932304ce-497d-4699-9c81-aa7decad31f6",
  "mail_remote_source": "saleinvoice",
  "mail_attachments": "a7590409-b792-8f1c-81d0-0191a509d671",
  "mail_account": "e-invoice"
}

Business Objects:

  • [app].[mail_set] - set mail
  • [app].[mail_onComplete] - business logic
  • mail_attachments - comma separated values of file_uuid from [app].[files] data object

System configuration

For system messages, for example password recovery, the system configuration is used.

Configuration is stored in app.settings.json After changes, Recycle application pool is required!

Standard SMTP configuration:

JSON
1
2
3
4
5
6
7
8
9
"system": {
    "mail_from": "[email protected]",
    "mail_username": "[email protected]",
    "mail_smtp_server": "xxxx.pl",
    "mail_smtp_port": 465,
    "mail_password": "",
    "mail_usessl": true,
    "mail_smtp_valid_cert": false   //IF invalid cert
}

Faq

Troubleshooting Pop3

Pop3 test in python

In case of any problems you can configuration using python script

Python
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
# Python mail test library
# This library is used to test the mail API

import poplib
import getpass

# Get user credentials
system = {
  "mail_smtp_server": "smtp-relay.gmail.com", 
  "mail_smtp_port": "587",
  "mail_username": "[email protected]",
  "mail_password": "xxxxx",
  "mail_from": "[email protected]" 
}

# Connect to the mail server
mail_box = poplib.POP3_SSL(mailserver,995)

# Login to your account
mail_box.user(user)
mail_box.pass_(password)

# Get the number of mail messages
num_messages = len(mail_box.list()[1])
print('Total emails: {}'.format(num_messages))

# iterate mails baackwards and make read  

for i in range(num_messages, 0, -1):
  response, headerLines, bytes = mail_box.retr(i)
  for header in headerLines:
    if header.startswith(b"Date:"):
      print("Email {} Date: {}".format(i, header.decode()))
  print("Email {} Bytes: {}".format(i, bytes))

# Iterate mails and print dates
# for i in range(num_messages):
#   response, headerLines, bytes = mail_box.retr(i+1)
#   for header in headerLines:
#     if header.startswith(b"Date:"):
#       print("Email {} Date: {}".format(i+1, header.decode()))

# Close the connection
mail_box.quit()

Troubleshooting Smtp

SMTP test in python

In case of any problems you test configuration using python script

Python
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

system = {
  "mail_smtp_server": "smtp-relay.gmail.com", 
  "mail_smtp_port": "587",
  "mail_username": "[email protected]",
  "mail_password": "xxxxx",
  "mail_from": "[email protected]" 
}


msg: MIMEMultipart = MIMEMultipart()
msg['To'] = "[email protected]"
msg['Subject'] = "Test"

# add in the message body
msg.attach(MIMEText('Test message', 'plain'))

# create server
server = smtplib.SMTP(system["mail_smtp_server"])

server.starttls()


# Login Credentials for sending the mail
server.login(system['mail_username'], system["mail_password"])

# send the message via the server
server.sendmail(system['mail_from'], msg['To'], msg.as_string())

server.quit()

Query to get email attachments for single email from mail storage

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Example Read single email attachments

SELECT 
  p.value('(./hash)[1]', 'uniqueidentifier') AS hash,
  p.value('(./name)[1]', 'VARCHAR(5000)') AS name,
  p.value('(./bytes)[1]', 'int') AS bytes,
  p.value('(./extension)[1]', 'VARCHAR(10)') AS extension
FROM [dbo].[mail]
  CROSS APPLY mail_attachments.nodes('/ArrayOfFile/file') t(p)
where 
mail_id= 1198

/*
Result
<ArrayOfFile>
  <file>
    <name>TEST 7.pdf</name>
    <bytes>29223</bytes>
    <hash>5283dce8-1041-da71-5362-134ba3dc7dfe</hash>
    <extension>.pdf</extension>
  </file>
  <file>
    <name>TEST 1.pdf</name>
    <bytes>29223</bytes>
    <hash>5283dce8-1041-da71-5362-134ba3dc7dfe</hash>
    <extension>.pdf</extension>
  </file>
</ArrayOfFile>
*/