Published on January 4, 2021.

A common request that I get from my clients is for help monitoring the status of email messages that are generated from within NetSuite. The goal is to get a handle on the messages that are being generated, the status of the messages, and perhaps most importantly, the messages that have bounced.

There are several tables that are exposed to SuiteQL that can help with this, and in this post I'll discuss some of the those tables, and share queries that you can use to get the data that you need.

Sent Email

NetSuite stores outbound email messages in a table named SentEmail. It includes columns for the date sent, status, who sent the message, who it was addressed to (including any "cc" or "bcc" recipients), the subject, body, and more.

Here's a query that you can use to get all email sent within a specified date range.

SELECT
	SentDate,
	Status,
	"From",
	ReplyTo,
	ToRecipients,
	CCRecipients,
	BCCRecipients,
	Subject,
	Body,
	EmailMessageID
FROM
	SentEmail 
WHERE 
	( SentDate BETWEEN TO_DATE( '2021-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2021-01-31', 'YYYY-MM-DD' ) )
ORDER BY
	SentDate

A note about the three recipients-related columns (ToRecipients, CCRecipients, and BCCRecipients): If the message was addressed to multiple recipients, then the column will include a comma-delimited list of the addresses. For example, if an email was sent to "jane@test.com" and "john@test.com," then the "ToRecipients" column would be: "jane@test.com,john@test.com" That can make it difficult to join the records in SentEmail to Entity records.

However, NetSuite provides another table that can help. The table is named "SentEmailRecipient" and you can use it to join individual recipients to records in the SentEmail table.

SELECT
	SentEmail.SentDate,
	SentEmail.Status AS EmailStatus,
	SentEmailRecipient.Status AS RecipientStatus,
	SentEmail."From",
	SentEmail.ReplyTo,
	SentEmail.ToRecipients,
	SentEmail.CCRecipients,
	SentEmail.BCCRecipients,
	SentEmail.Subject,
	SentEmail.Body,
	SentEmail.EmailMessageID,
	Entity.EntityID,
	Entity.AltName,
	Entity.FirstName,
	Entity.LastName
FROM
	SentEmail
	INNER JOIN SentEmailRecipient ON
		( SentEmailRecipient.ID = SentEmail.Key )
	LEFT OUTER JOIN Entity ON
		( Entity.Email = SentEmailRecipient.Recipient )		
WHERE
	( SentEmail.SentDate BETWEEN TO_DATE( '2021-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2021-01-31', 'YYYY-MM-DD' ) )
	AND ROWNUM <=3
ORDER BY
	SentEmail.SentDate

We're now able to get the email message, each recipient that it was sent to, and their related Entity record.

Notice that I've added the "Status" column from the SentEmailRecipient. This gives us visibility into the status of the message at the recipient level. Those statuses include Undelivered, Delivered, and Skipped.

Undelivered Email

NetSuite stores undelivered email messages in a table named - you guessed it - UndeliveredEmail. Here's a query that returns all undelivered messages that were sent within a specified date range.

SELECT
	UndeliveredEmail.SentDate,
	UndeliveredEmail.Reason,
	UndeliveredEmail.Subject,
	UndeliveredEmail."From",
	UndeliveredEmail.Recipients,
	UndeliveredEmail.MessageID,
	Entity.Type,
	Entity.EntityID,
	Entity.AltName,
	Entity.FirstName,
	Entity.LastName
FROM 
	UndeliveredEmail 
	LEFT OUTER JOIN Entity ON
		( Entity.Email = UndeliveredEmail.Recipients )
WHERE
	  ( UndeliveredEmail.SentDate BETWEEN TO_DATE('2020-12-01', 'YYYY-MM-DD' ) AND TO_DATE('2020-12-31', 'YYYY-MM-DD' ) )
ORDER BY
	UndeliveredEmail.SentDate DESC

Notice that I've joined to the Entity table, based on the email address of the intended recipient. I'm using a LEFT OUTER JOIN so that any messages whose recipients aren't in the Entity table aren't excluded from the results.

Other Email-Related Tables

There are several additional email-related tables that are available to us, including tables used for email campaigns, email templates, and so on. Here's a partial list of those tables:

• CampaignEmailAddress
• CampaignEmailCampaignEventResponse
• EmailCampaignEventResponse
• EmailCapturePlugin
• EmailTemplate
• EmailTemplateCategoryCRMTemplate
• SalesCampaignEmailCampaignEventResponse
• SearchCampaignEmailCampaignEventResponse
• SystemEmailTemplate
• SystemEmailTemplateCategoryCrmTemplate

I'm not going to discuss those tables in this post, but depending on your needs, you might want to explore them.

Wrapping Up

Using the SentEmail, SentEmailRecipient, and UndeliveredEmail tables, we can use SuiteQL to easily monitor email-related activity in a NetSuite account.

If you have any questions about the queries in this post, please feel free to reach out to me.