2

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?

4

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.

  • I was afraid of that. Thanks, I'll explore this wrapper thing. – okay56k Jan 18 '16 at 21:52
  • 2
    @CD-RUM I feel your pain. It's the rare object like this that makes me wonder what they were doing when they designed the feature. – sfdcfox Jan 18 '16 at 22:05
4

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.

  • How do you query the leads that have email statuses? – Edmondo1984 Oct 6 '17 at 5:18
  • I don't think you can efficiently. You have to query for Leads and use sub query per the code sample in the answer. You'd have to iterate the query results to know which leads actually had any emails statuses. – Doug Ayers Oct 6 '17 at 5:22
  • The following doesn't work: SELECT id, name,(SELECT id, taskId, task.Subject,createdDate, firstOpenDate, lastOpenDate, timesOpened, emailTemplateName FROM EmailStatuses) FROM Lead where Id In (SELECT task.whoId FROM EmailStatuses) – Edmondo1984 Oct 6 '17 at 5:26
  • Correct, you cannot do that query. You can only query the EmailStatuses as part of the select statement on the contact or lead. You must then after retrieving the query results into an apex variable then loop over them, for each result then check how many and what values are in the email statuses then do any further logic. – Doug Ayers Oct 7 '17 at 16:17
  • What if we do not set the whoId because of the platform limit with saveAsActivity, setTargetObjectId, setWhoId? Is there another way to query the object for example relating it to Task? – Edmondo1984 Oct 9 '17 at 17:36
2

You cannot query the EmailStatus my results from Workbench.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.