Apex: HTML email status to get open count

Question

I'm writing some Apex to list how many times a certain email has been opened. How can I access the "HTML Email Status" object from the lead?

List<Lead> leads = [SELECT Id, Email, (SELECT Id, Subject FROM ActivityHistories WHERE Subject LIKE 'Email: Hello, %.') FROM Lead];
for (Lead l: leads) {
    if (l.activityHistories.size() > 0) {
        System.debug('History: ' + l.activityHistories);
    }
}

I don't think I can get the read count from the activity histories object, so what can I do to change it?

Answer 1

You'll have to get creative, because the object you're looking for is EmailStatus, which is currently only available as a describeSObjects parameter. You can probably use Andrew Fawcett's Metadata Wrapper to get the describe for this object, but you can't use any normal queries on the object.

Answer 2

The EmailStatus object is not queryable directly (please vote for this idea), but it can be queried indirectly from Leads and Contacts as a child relationship sub-query as noted by Jenny Bennett on this other stackexchange answer.

Leads with EmailStatuses

SELECT
    id, name,
    (
        SELECT
            id, taskId, createdDate, firstOpenDate, lastOpenDate, timesOpened, emailTemplateName
        FROM
            EmailStatuses
    )
FROM
    Lead

Contacts with EmailStatuses

SELECT
    id, name,
    (
        SELECT
            id, taskId, createdDate, firstOpenDate, lastOpenDate, timesOpened, emailTemplateName
        FROM
            EmailStatuses
    )
FROM
    Contact

Other Tidbits

• Note, you can filter on the EmailStatuses by adding a WHERE clause to the sub-query. For example, if you only wanted to get email statuses with 3 or more opens.

• Note, the EmailStatus.CreatedDate is what I imagine is the Sent Date.

• Note, you can use cross-object dot notation to query for other fields from the Task record from the EmailStatus. For example, you could query for task.Subject.

Answer 3

You cannot query the EmailStatus my results from Workbench.