Outlook Mail (Office 365) Connector
Documentation
Version: 3
Documentation
Home » API Integration Hub » Outlook Mail (Office 365) Connector » EndPoints » Get Messages

EndPoint Get Messages

Name

get_messages

Description

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

Parameters

Parameter Label Required Options Description
UserId User ID or Email YES 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')
EmailBodyFormat Email Body Format YES
Option Value
html html
text text
SearchCriteria Search Criteria NO
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'
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] [API reference]
MailFolderId MailFolderId NO
OrderBy Order By NO
Option Value
None
AscendingOrder SomeColumn asc
DescendingOrder SomeColumn desc
Order the specified column by ascending or descending order [API reference]
PageSize Page Size NO The number of records to include in each page of results [API reference]
SelectedColumns Selected Columns NO Select the individual columns to be included in the response, to reduce the response size and increase performance [API reference]

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Raw Description
Id DT_WSTR nvarchar(1000) 1000 False
ReceivedDateTime DT_DBTIMESTAMP datetime False
HasAttachments DT_BOOL bit False
Subject DT_WSTR nvarchar(800) 800 False
BodyPreview DT_WSTR nvarchar(4000) 4000 False
SenderEmailAddressName DT_WSTR nvarchar(800) 800 False
SenderEmailAddress DT_WSTR nvarchar(800) 800 False
Importance DT_WSTR nvarchar(500) 500 False
SentDateTime DT_DBTIMESTAMP datetime False
CreatedDateTime DT_DBTIMESTAMP datetime False
LastModifiedDateTime DT_DBTIMESTAMP datetime False
ChangeKey DT_WSTR nvarchar(1000) 1000 False
ToRecipients DT_WSTR nvarchar(1190) 1190 False
CcRecipients DT_WSTR nvarchar(1190) 1190 False
BccRecipients DT_WSTR nvarchar(1190) 1190 False
ToRecipient1 DT_WSTR nvarchar(200) 200 False
ToRecipient2 DT_WSTR nvarchar(200) 200 False
ToRecipient3 DT_WSTR nvarchar(200) 200 False
ToRecipient4 DT_WSTR nvarchar(200) 200 False
ToRecipient5 DT_WSTR nvarchar(200) 200 False
CcRecipient1 DT_WSTR nvarchar(200) 200 False
CcRecipient2 DT_WSTR nvarchar(200) 200 False
CcRecipient3 DT_WSTR nvarchar(200) 200 False
CcRecipient4 DT_WSTR nvarchar(200) 200 False
CcRecipient5 DT_WSTR nvarchar(200) 200 False
BccRecipient1 DT_WSTR nvarchar(200) 200 False
BccRecipient2 DT_WSTR nvarchar(200) 200 False
BccRecipient3 DT_WSTR nvarchar(200) 200 False
BccRecipient4 DT_WSTR nvarchar(200) 200 False
BccRecipient5 DT_WSTR nvarchar(200) 200 False
Categories DT_WSTR nvarchar(1000) 1000 False
ParentFolderId DT_WSTR nvarchar(1200) 1200 False
ConversationId DT_WSTR nvarchar(1200) 1200 False
ConversationIndex DT_WSTR nvarchar(1200) 1200 False
IsDeliveryReceiptRequested DT_BOOL bit False
IsReadReceiptRequested DT_BOOL bit False
IsRead DT_BOOL bit False
IsDraft DT_BOOL bit False
WebLink DT_WSTR nvarchar(4000) 4000 False
InferenceClassification DT_WSTR nvarchar(800) 800 False
InternetMessageHeaders DT_NTEXT nvarchar(MAX) False
BodyContentType DT_WSTR nvarchar(500) 500 False
BodyContent DT_NTEXT nvarchar(MAX) False
FromEmailAddressName DT_WSTR nvarchar(800) 800 False
FromEmailAddress DT_WSTR nvarchar(800) 800 False
ReplyTo DT_WSTR nvarchar(1190) 1190 False
UniqueBodyContentType DT_WSTR nvarchar(500) 500 False
UniqueBodyContent DT_WSTR nvarchar(4000) 4000 False
FlagFlagStatus DT_WSTR nvarchar(800) 800 False
ODataETag DT_WSTR nvarchar(1000) 1000 False
InternetMessageId DT_WSTR nvarchar(1800) 1800 False

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Raw 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 component to read data or in API Destination component to read/write data:

Get Messages using API Source

Outlook Mail (Office 365)
Get Messages
SSIS API Source - Read from table or endpoint

Get Messages using API Destination

Outlook Mail (Office 365)
Get Messages
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];