Reference

Endpoint Get Messages

Name

get_messages

Description

Get the mail messages for the specified user ID or email address. [API reference]

Parameters

Parameter Required Options
Name: UserId

Label: User ID or Email

The Microsoft Mail user ID GUID or email address for the account to read messages from. (Examples: '4ff54a77-9c09-4274-958f-83bdc0a53900' or 'first.last@domain.com') 		YES
Name: EmailBodyFormat

Label: Email Body Format

 YES
Option Value
html html
text text
Name: SearchCriteria

Label: Search Criteria

Data filter (e.g. somecolumn -eq 'somevalue'), you can use Date time functions for dynamic date. You can use keywords like now,today,yesterday,monthstart,monthend,yearstart,yearend,weekstart,weekend along with operator + / - [hour,minute,second,day,month,year]
Option Value
None
From specific email 'from/emailAddress/address eq 'microsoft-noreply@microsoft.com'
Has Attachments hasAttachments eq true
Has Attachments, Sent After, and Subject containing 'party' hasAttachments eq true and sentDateTime gt 2023-03-01 and contains(subject, 'party')
Received before receivedDateTime lt 2023-02-01
Sent after sentDateTime gt 2023-03-01
Sent after (dynamic) sentDateTime gt <>
Search by Internet MessageId contains(internetMessageId,''59e62584-e364-453a-8444-5591a324ddaa@az.centralus.production.microsoft.com'') Has attachment(s)=hasAttachments eq true
Subject contains 'case' contains(subject, 'case')
Subject starts with 'Your' startsWith(subject, 'Your')
Subject does not start with 'Your' not(startsWith(subject, 'Your'))
Only normal importance importance eq 'normal'
Only low importance importance eq 'low'
Only high importance importance eq 'high'
Only if delivery receipt requested isDeliveryReceiptRequested eq true
Only if delivery receipt not requested isDeliveryReceiptRequested eq false
Only if read receipt requested isReadReceiptRequested eq true
Only if read receipt not requested isReadReceiptRequested eq false
Only if message is read isRead eq true
Only if message is unread isRead eq false
Only if message is a draft isDraft eq true
Only if message is not a draft isDraft eq false
Body content contains contains(body/content, 'money')
From Sender 'John Doe' sender/emailAddress/name eq 'John Doe'
Name: MailFolderId

Label: MailFolderId
Name: OrderBy

Label: Order By

Order the specified column by ascending or descending order
Option Value
None
AscendingOrder SomeColumn asc
DescendingOrder SomeColumn desc
Name: PageSize

Label: Page Size

The number of records to include in each page of results
Name: SelectedColumns

Label: Selected Columns

Select the individual columns to be included in the response, to reduce the response size and increase performance

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
Id DT_WSTR nvarchar(1000) 1000
ReceivedDateTime DT_DBTIMESTAMP datetime
HasAttachments DT_BOOL bit
Subject DT_WSTR nvarchar(800) 800
BodyPreview DT_WSTR nvarchar(4000) 4000
SenderEmailAddressName DT_WSTR nvarchar(800) 800
SenderEmailAddress DT_WSTR nvarchar(800) 800
Importance DT_WSTR nvarchar(500) 500
SentDateTime DT_DBTIMESTAMP datetime
CreatedDateTime DT_DBTIMESTAMP datetime
LastModifiedDateTime DT_DBTIMESTAMP datetime
ChangeKey DT_WSTR nvarchar(1000) 1000
ToRecipients DT_WSTR nvarchar(1190) 1190
CcRecipients DT_WSTR nvarchar(1190) 1190
BccRecipients DT_WSTR nvarchar(1190) 1190
ToRecipient1 DT_WSTR nvarchar(200) 200
ToRecipient2 DT_WSTR nvarchar(200) 200
ToRecipient3 DT_WSTR nvarchar(200) 200
ToRecipient4 DT_WSTR nvarchar(200) 200
ToRecipient5 DT_WSTR nvarchar(200) 200
CcRecipient1 DT_WSTR nvarchar(200) 200
CcRecipient2 DT_WSTR nvarchar(200) 200
CcRecipient3 DT_WSTR nvarchar(200) 200
CcRecipient4 DT_WSTR nvarchar(200) 200
CcRecipient5 DT_WSTR nvarchar(200) 200
BccRecipient1 DT_WSTR nvarchar(200) 200
BccRecipient2 DT_WSTR nvarchar(200) 200
BccRecipient3 DT_WSTR nvarchar(200) 200
BccRecipient4 DT_WSTR nvarchar(200) 200
BccRecipient5 DT_WSTR nvarchar(200) 200
Categories DT_WSTR nvarchar(1000) 1000
ParentFolderId DT_WSTR nvarchar(1200) 1200
ConversationId DT_WSTR nvarchar(1200) 1200
ConversationIndex DT_WSTR nvarchar(1200) 1200
IsDeliveryReceiptRequested DT_BOOL bit
IsReadReceiptRequested DT_BOOL bit
IsRead DT_BOOL bit
IsDraft DT_BOOL bit
WebLink DT_WSTR nvarchar(4000) 4000
InferenceClassification DT_WSTR nvarchar(800) 800
InternetMessageHeaders DT_NTEXT nvarchar(MAX)
BodyContentType DT_WSTR nvarchar(500) 500
BodyContent DT_NTEXT nvarchar(MAX)
FromEmailAddressName DT_WSTR nvarchar(800) 800
FromEmailAddress DT_WSTR nvarchar(800) 800
ReplyTo DT_WSTR nvarchar(1190) 1190
UniqueBodyContentType DT_WSTR nvarchar(500) 500
UniqueBodyContent DT_WSTR nvarchar(4000) 4000
FlagFlagStatus DT_WSTR nvarchar(800) 800
ODataETag DT_WSTR nvarchar(1000) 1000
InternetMessageId DT_WSTR nvarchar(1800) 1800
If the column you are looking for is missing, consider customizing Outlook Mail (Office 365) Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use Outlook Mail (Office 365) Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

API Source - Outlook Mail (Office 365)
Outlook Mail Connector (Office 365) can be used to integrate Office 365 Outlook Mail API in your App / BI Tools. You can download attachment, read / search Emails, Users, MailFolders, Send email and more.
Outlook Mail (Office 365)
Get Messages
Required Parameters
User ID or Email Fill-in the parameter...
SSIS API Source - Read from table or endpoint

API Destination

API Destination - Outlook Mail (Office 365)
Outlook Mail Connector (Office 365) can be used to integrate Office 365 Outlook Mail API in your App / BI Tools. You can download attachment, read / search Emails, Users, MailFolders, Send email and more.
Outlook Mail (Office 365)
Get Messages
Required Parameters
User ID or Email Fill-in the parameter...
SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Get messages for a specified user 

SELECT * FROM get_messages
WITH (UserID='firstname.lastname@domainname.com')

Get first 5 messages for a specified user 

SELECT * FROM get_messages
WITH (UserID='firstname.lastname@domainname.com', PageSize='5')

Get all messages in a specific Mail Folder 

SELECT * FROM get_messages
WITH (
MailFolderID='INBOX' --or use mai folder id
)

Get messages with a subject line that starts with 'Announcements for' 

SELECT * FROM get_messages
WITH (UserID='firstname.lastname@domainname.com', SearchCriteria='startsWith(Subject,''Announcements for'')')

Get messages that have at least one attachment associated with them 

SELECT * FROM get_messages
WITH (UserID='firstname.lastname@domainname.com', SearchCriteria='hasAttachments eq true')

Get messages that are unread 

SELECT * FROM get_messages
WITH (UserID='firstname.lastname@domainname.com', SearchCriteria='IsRead eq false')

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Get messages for a specified user 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_messages
WITH (UserID=''firstname.lastname@domainname.com'')';

EXEC (@MyQuery) AT [LS_TO_OUTLOOK_MAIL_OFFICE_365_IN_GATEWAY];

Get first 5 messages for a specified user 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_messages
WITH (UserID=''firstname.lastname@domainname.com'', PageSize=''5'')';

EXEC (@MyQuery) AT [LS_TO_OUTLOOK_MAIL_OFFICE_365_IN_GATEWAY];

Get all messages in a specific Mail Folder 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_messages
WITH (
MailFolderID=''INBOX'' --or use mai folder id
)';

EXEC (@MyQuery) AT [LS_TO_OUTLOOK_MAIL_OFFICE_365_IN_GATEWAY];

Get messages with a subject line that starts with 'Announcements for' 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_messages
WITH (UserID=''firstname.lastname@domainname.com'', SearchCriteria=''startsWith(Subject,''''Announcements for'''')'')';

EXEC (@MyQuery) AT [LS_TO_OUTLOOK_MAIL_OFFICE_365_IN_GATEWAY];

Get messages that have at least one attachment associated with them 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_messages
WITH (UserID=''firstname.lastname@domainname.com'', SearchCriteria=''hasAttachments eq true'')';

EXEC (@MyQuery) AT [LS_TO_OUTLOOK_MAIL_OFFICE_365_IN_GATEWAY];

Get messages that are unread 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_messages
WITH (UserID=''firstname.lastname@domainname.com'', SearchCriteria=''IsRead eq false'')';

EXEC (@MyQuery) AT [LS_TO_OUTLOOK_MAIL_OFFICE_365_IN_GATEWAY];