Power BI Connector for Email

Build 21.0.7930

IMAP

IMAP Specific Information

Email address fields accept the following formats for email addresses:

  • "Friendly Name" <address@company.com>
  • address@company.com

SELECT

Email will be listed newest to oldest. By default, the MessageBody and Headers will not be returned when listing multiple messages.

Note: By default, the max number of messages returned will be 100. This can be changed by setting either LIMIT or MaxItems. If you wish to return all email within a mailbox, you will need to specify a value of 0 or -1.

List Mail Messages

To list messages within a specific mailbox, specify the mailbox as the table. This will list the most recent messages.

Alternatively, you can specify a range of message Ids to list. This includes the following formats:

WHERE Id='10' //For returning only the message with an Id of 10.
WHERE Id BETWEEN 10 AND 20 //For returning all messages from 10 to 20.
WHERE Id IN (1,3,5) //For returning messages 1, 3, 5.
Search Email

To search mail, simply specify a value for any of the following columns in the WHERE clause: To, From, BCC, CC, Subject, MessageBody, Flags, Labels, Size, or Date. This includes complex statements. For example:

Note that the CONTAINS function requires full words (For example Test, not Tes), just like the search bar from the UI. For example:

SELECT * FROM [Inbox] WHERE CONTAINS(MessageBody, "Test") AND ([From] = test1@email.com OR [From] = test2@email.com) AND Date>'1-1-2012'
This will return all messages where the message body contains the text 'Test', and the message is from either test1@email.com or test2@email.com.

In addition to searching by standard columns, you can also search by attachment file names and by whether or not an attachment is on the email. For example:

SELECT * FROM [Inbox] WHERE HasAttachment='True' AND Attachments='.txt'

Use of parentheses is also supported for complex WHERE clauses.

As an alternative to the above method of creating search criteria, the SearchCriteria pseudo column is also available. This will accept any valid IMAP search criteria as specified by RFC.

INSERT

Please refer to the SMTP section.

UPDATE

To move an email from one mailbox to another, you will need to specify the Mailbox in the SET clauses of the message and the ID in the WHERE clause. For example:

UPDATE [Inbox] SET Mailbox='NewMailboxName' WHERE Id='MessageId'

Id may consist of a single message number, a range of messages specified by two message numbers separated by ':' (e.g. "1:5"), and/or individual message numbers separated by ',' (e.g. "1:5,7,10").

When moving an email from one mailbox to another, you may not specify any additional updates.

Additional Notes

By default, the number of messages returned per page will be 25. To change this, you can set the ItemsPerPage pseudo column.

All message Ids returned are temporary Ids and may change in subsequent requests to the server. To use static Ids, you can set UIDMode=true. However, only listing messages is supported with UIDs.

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930