Email Account Management

 

1 What is Email Account

After SQLMessenger installation is completed, you need to configure email accounts in the SQLMessenger system before sending emails. SQLMessenger will use these email accounts to send emails generated by tasks in the system.

SQLMessenger supports sending emails through SMTP mail servers (such as GMail, Outlook, Hotmail, Yahoo Mail, etc.).

SQLMessenger also supports retrieving emails from POP3 and IMAP servers.

You can configure multiple email accounts for SQLMessenger and set their priority.

Note: Based on your system configuration, SQLMessenger may frequently connect to email servers for sending and receiving emails. To prevent any impact on the normal use of your work email, it is recommended that you do not use your work email as the sending account for SQLMessenger. Instead, it is advisable to create dedicated email accounts within your email system for SQLMessenger to send emails.

2 Email Account Manager

You can click the "Email Accounts" icon in the MainMenu to open the Email Account Manager.

Click the "Email Accounts" icon in the MainMenu

Email Account Manager

In the Email Account Manager, you can add new email accounts, modify, disable, or delete existing ones that have already been configured.

3 Adding an Email Account

Click the "Add Email Account" button in the Email Account Manager's toolbar to open the "Email Account Config" dialog.

Click "Add Email Account" button to open open the "Email Account Config" dialog

Email Account Config dialog

In this dialog box:

Send Protocol	Select the type of server protocol to use when sending emails.
Email Address	Enter the address of the email account (e.g, test@sqlmessenger.com).
Authentication Type	Set the login authentication type for the account. SQLMessenger supports the following authentication types: Username and Password:The traditional username and password authentication method. You need to enter the username and password (or App Password) of your email account in this dialog box. This method can be used for email services like Google, Yahoo, etc. Microsoft OAuth2:The OAuth2 authentication method for Microsoft email services requires configuring a Microsoft application and logging in with a Microsoft Account. Email services such as Outlook.com, Hotmail.com, and Microsoft 365 must use this authentication method.
Email System Username	Enter the username of the email account on the mail server. This username is usually the same as the email address.
Email Password	Enter the password of the email account on the mail server. If you use Gmail or Yahoo Mail to send emails, please enter your Gmail or Yahoo Mail App Password here.

For SMTP protocol:

Allow sending emails	Select whether to allow the system to use this account to send emails.
SMTP Server	Enter the domain name or IP address of the SMTP Server here. The system will automatically populate SMTP server addresses for popular email systems (such as Gmail, Outlook, Hotmail, Yahoo Mail, etc.).
SMTP Port	SMTP service port for mail server. If left blank, the system will use the default SMTP port for sending emails. Default SMTP port is 25, or 465 when SSL connection is enabled.
Send Interval(in seconds)	The interval between sending emails, measured in seconds. By default, the system checks for and sends new emails every 10 seconds.
Connect to SMTP server using SSL protocol	Choose whether to use the SSL protocol to connect to the SMTP server for secure communication. This feature requires the support of a mail server to be used. The system will automatically check this option for popular email systems (such as Gmail, Outlook, Hotmail, Yahoo Mail, etc.).
Connect to SMTP server using TLS protocol	Choose whether to use the TLS protocol to connect to the SMTP server for secure communication. This feature requires the support of a mail server to be used. The system will automatically check this option for popular email systems (such as Gmail, Outlook, Hotmail, Yahoo Mail, etc.).
Allow SQLMessenger to receive emails from the email account's inbox	Choose whether to allow SQLMessenger to retrieve emails from this account. If selected, SQLMessenger will read emails from the Inbox folder of this account and store them in its local database (PostgreSQL). Allow SQLMessenger to Receive Emails
Receive Protocol	If the "Allow data extraction requests" option is selected, you need to specify the type of protocol to receive emails. The available protocol types are "POP3" or "IMAP." The system will automatically select the email retrieval protocol for popular email systems (such as Gmail, Outlook, Hotmail, Yahoo Mail, etc.).
POP3/IMAP Server	If the "Allow accepting data extraction requests" option is selected, you need to specify the POP3 or IMAP server address for your email system here. The system will automatically populate POP3/IMAP server addresses for popular email systems (such as Gmail, Outlook, Hotmail, Yahoo Mail, etc.).
POP3/IMAP Port	If the "Allow accepting data extraction requests" option is selected, you need to specify the POP3 or IMAP server port for your email system here. If left blank, the system will use the default POP3/IMAP port to retrieve emails.
Receive Interval(in seconds)	If the "Allow accepting data extraction requests" option is selected, you can set the interval for retrieving emails in seconds. The system will check for new emails periodically. By default, it will retrieve new emails every 180 seconds.
Connect to POP/IMAP server using SSL protocol	This option enables you to choose whether to connect to the POP3/IMAP server using the TLS protocol for secure communication. This feature requires the support of a mail server to be used. The system will automatically check this option for popular email systems (such as Gmail, Outlook, Hotmail, Yahoo Mail, etc.).
Email Account Disabled	Set whether to disable this account. If the account is disabled, the system will suspend sending and receiving emails using this account.

Email Account Config dialog (Advanced Options)

Advanced Options:

Connect though HTTP Proxy Server	(SMTP only) Select whether to connect to the mail server through a HTTP Proxy Server
Keep original sender information	(SMTP only) Some email systems do not allow senders to customize the sender's name. In such cases, you can select this option to preserve the original sender's name to prevent emails from being rejected by the server.
Display sender name as	(SMTP only) When "Keep original sender information" is not selected, you can define the sender's display name for emails sent through this email account. This name will be displayed in the sender's information when recipients view the emails.
Maximum number of emails that can be sent per connection	Set the maximum number of emails to be sent per connection. If the number of emails exceeds this limit, the system will disconnect from the server and send the remaining emails during the next connection.
Enable sending quota	Set the maximum number of emails to be sent from the email account within a specified time period. If the number of emails sent exceeds the set limit within that time period, the system will suspend sending emails using this account until the next time period. For example, the following configuration indicates "Starting from 03/14/2024 00:00:00, use this account to send a maximum of 1000 emails per day":

4 Email Account Priority Setting

When multiple email accounts are configured in the system, you can set priorities for them. When sending emails, the system will prioritize using the account with the highest priority for sending. If the email sending fails with the highest priority account, the system will then sequentially select lower priority accounts for sending.

You can click the Priority Setting button on the Email Account Manager's toolbar to open the Priority Setting dialog.

Click the Priority Setting button to open the Priority Setting dialog

Select the account to adjust and click "Move Up" or "Move Down" to change its priority.

Accounts at the top of this list have higher priority, while those at the bottom have lower priority.

After making the adjustments, click "OK" to save the priority setting.

5 Email Account Management Toolbar

Email Account Management Toolbar

	Click to open the Email Account configuration dialog to modify the Email Account settings.
/	Click to disable or enable Email Account. If the account is disabled, the system will suspend sending and receiving emails using this account.
	Click to delete the Email Account.
	Click to view the email sending logs for the Email Account.
	Click to view the email receiving logs for the Email Account.

6 Gmail , Yahoo Mail, Outlook & Hotmail Account Config

Unlike other email services, configuring Gmail and Yahoo Mail, Outlook and Hotmail accounts requires entering the App Password as the login password for the email account, instead of the Gmail or Yahoo or Microsoft account password.

6.1 Create Gmail App Password

Step 1: Log in to your Google account, then click the Account icon on the Google menu.

Step 2: Enter "App Password" in the "Search Google Account" field, then click on the "App Passwords Security" option.

Step 3: Enter a "App Name" for the new App Password, then click the "Create" button.

Step 4: After successfully creating the app password, Google will display the new password on the page. Please enter this password in the SQLMessenger's "App Password" text box.

6.2 Create Yahoo App Password

Step 1: Log in to Yahoo Mail in your browser, then click the "Account info" icon.

Step 2: Click "Security" on the page.

Step 3: On the page, locate "App Passwords" and click "Generate App Password".

Step 4: Enter "App Name", then click "Generate Password".

Step 5: After successfully creating your password, Yahoo will display it on the page. Please enter this password in the SQLMessenger's "App Password" text box.

6.3 Config OAuth2 Authentication for Outlook & Hotmail Accounts

Microsoft email accounts such as Outlook.com and Hotmail.com require configuring the OAuth2 authentication method for login. Please follow these steps for configuration:

Step 1: Set the Authentication Type of the account to "Microsoft OAuth2".

Step 2: Select the app for authentication in the OAuth2 Authentication App. If you cannot create an app yourself, you can choose the "SQLMessenger Azure App" provided by SQLMessenger developers. If you have already created your own app in Azure, you can click the "App Manager" button to add your app to the SQLMessenger system and then select this app as your login app.

Step 3: Click the "Microsoft Account Login" button to open the Microsoft Account login page in your browser, and enter your Microsoft account credentials to log in. Note that the Microsoft Account you log in with must match the Email Account you entered.

Step 4: When prompted with API permissions after successfully logging in to your account, click the "Accept" button.

Step 5: When the browser displays the following message, it indicates that the login is successful. At this point, return to the SQLMessenger console to continue configuring your Email account.

After successful OAuth2 login, "Microsoft OAuth2" should display "Authorized". If you change the settings of your OAuth2 app in the future, you will need to click the "Microsoft Account Login" button to re-authenticate your Microsoft Account.

7 Save Emails to Local Database from the Mail Server

You can use SQLMessenger 2.1 or later to save emails to a local database.

Check the "Allow SQLMessenger to save emails to local database" option in the email account configuration dialog

If this option is enabled, SQLMessenger will periodically read the emails from the mail server and save them in the tables of SQLMessenger's database (a PostgreSQL database). You can access the email data through the views `v_inbox_mail` and `v_inbox_mail_content` in SQLMessenger's default data source.

The field descriptions for the relevant view are as follows:

v_inbox_mail: stores the summary information of the emails.

inbox_mail_id	long int	The ID of the email, generated by SQLMessenger
mail_acct_id	int	The ID of the account that received this email, generated by SQLMessenger.
mail_acct_addr	text	The email address of the account that received this email.
message_id	text	The Message ID of the email, generated by the mail server.
from_addr	text	The address that sent this email.
reply_to	text	The reply-to address of the email, set by the sender.
folder_name	text	The folder name of the email on the mail server.
received_time	datetime	The time the email was received by the mail server.
send_to	text	The list of recipients of the email.
copy_to	text	The list of carbon copy (CC) recipients of the email.
bcc_to	text	The list of blind carbon copy (BCC) recipients of the email.
mail_size	long int	The size of the email.
sent_date	datetime	The time the email was sent.
subject	text	The subject of the email.
create_time	datetime	The time SQLMessenger received this email.
state	char(1)	The status of the email in SQLMessenger. A: Active; X: Deleted
state_time	datetime	The status time of the email in SQLMessenger.

v_inbox_mail_content: Stores the content of the email body and attachments.

mail_content_id	long int	The content ID of the email, generated by SQLMessenger.
inbox_mail_id	long int	The corresponding email ID, generated by SQLMessenger.
content_type	text	The type of the content.
html_content	text	If the content type is "TEXT/HTML," this field stores the HTML content of the email.
text_content	text	Stores the TEXT content of the email.
file_name	text	If the content type is "attachment," this field stores the filename of the attachment.
file_size	long int	If the content type is text or HTML，this field stores the size of the content in bytes. If the content type is "attachment," this field stores the size of the attachment in bytes.
file_uuid	text	If the content type is "attachment," this field stores the UUID of the attachment. You can use this UUID to locate the file on the local disk.
comments	text	If SQLMessenger encounters any abnormal situation while processing this content, this field will record the details of the exception. For example, when the size of an attachment file exceeds the limit, SQLMessenger will not download the attachment and instead record the reason for not downloading the file in this field.

7.1 Email Saving Settings

Set the options for email saving

In this dialog box:

Save emails received after	SQLMessenger will save emails received after this time from the mail server. By default, it only save emails received after the first time the "Allow SQLMessenger to save emails to local database" option is enabled. You can set this option to the time point you need to save historical emails to local.
Save emails in the selected folders to local	Set which folders' emails to save to the local database. By default, SQLMessenger only saves the emails from the Inbox folder.
Keep saved emails in SQLMessenger for xxx day(s)	Set the retention period of emails in SQLMessenger's database. The default value is 365 days. After this period, the system will delete the saved emails from the local database. This operation will not affect the email data on the mail server.
Allow accepting data extraction requests	Set whether to allow data extraction requests through this email account. Using Data Extraction Code

7.2 Location of Attachment Files

After SQLMessenger receives an email attachment, it assigns a UUID (unique identifier) to each file and stores the file in the `arch\inbox` directory under the SQLMessenger installation directory. The folder name is related to the email's received time. For example, if an email has an email ID (in the `v_inbox_mail` table's `inbox_mail_id` field) of 6173 and the received time (in the `v_inbox_mail` table's `create_time` field) is 18/08/2024 12:34:55, the email's attachment will be stored in the folder "SQLMessenger\arch\inbox\20240818\6173".

The name of the attachment file is `v_inbox_mail_content.file_uuid` + file's extension. For example, if the `file_uuid` is "<b423ecdc38dd52f8166506349c5ff853>" and the original file name is "Report.xlsx", SQLMessenger will store the file as "SQLMessenger\arch\inbox\20240818\6173\b423ecdc38dd52f8166506349c5ff853.xlsx".