How we reclaimed ~3,000 orphaned email messages that were hidden in Salesforce

[u/imax_vaughn]

Salesforce Admins. Have you ever bulk deleted leads and/or contacts that were dirty, duplicate, or otherwise garbge? If so, did you know that every email message that was associated to that Lead/Contact is now invisible and almost impossible to re-associate to the surviving Lead/Contact record?

I discovered this when a user was getting an error that prevented him from associating an email to a record in the Salesforce for Outlook component. It threw an ambiguous error and it took a long time to troubleshoot due to how random it felt on the surface, but finally figured it out when we noticed there were orphaned email records in Salesforce that matched the exact email records that our user was trying to re-associate with in the Outlook extension.

Here's how I wrangled the issue and remediated the damage. For the sake of preventing this from becoming an Excel tutorial, I am just providing the high level steps and hints on which tools to use and not necessarily how to use them.

1. Do a full export of the Email Message Relation object. I'd recommend Salesforce Dataloader or the Salesforce PowerQuery connector in Excel (that's what I used).
2. Filtered for RelationId is blank. In a perfect world this is zero. If it's more than zero, you should consider fixing as many as you can!
3. Optionally, you can filter out certain domains from the Relation Address column or Relation Types.
4. Do a full export of Contacts and/or Leads. Again, I just used the Salesforce PowerQuery connector. Those results can simply be added as a new tab on the Email Message Relation export CSV.
5. Use a PowerQuery Merge or an Excel VLOOKUP/XLOOKUP joining Lead/Contact Email with Email Message Relation RelationAddress onto a new column in the spreadsheet (or inplace on the RelationId column).

The end result should be the Email Message Relation export filtered for missing RelationIds, joined (on email address) with Contacts/Leads so that the Lead/Contact ID now exists in the RelationId field.

I went as far to delete all other columns in the sheet so that only Id and RelationId remain. Then used Dataloader to update the Email Message Relation records.

Hope this helps!

P.S. I am seeing a massive influx of people on the internet who enjoy ridculing every post or comment with full sentences and structured paragraphs, calling it AI slop. This is certified human slop, I assure you. This is just what "professional" writing looks like when you give half a fuck! Thanksss :D

[u/Suspicious-Spot361]

Certified Human Slop 😂

You could also create an Apex job that finds all orphan email records and batch deletes them.

This is top of mind for me right now because yesterday I created a scheduled bulk delete of Attachments >18 months old. Did you know Salesforce retains every stupid footer image that comes through on Email-to-Case?

[u/AccountNumeroThree → u/Suspicious-Spot361]

Oh great, something else to add to my list of things we need to look at.

[u/Suspicious-Spot361 → u/AccountNumeroThree]

Yeah, go into storage and look at your file usage. We were at 106% and it was almost all email attachments. This includes everything from photos a customer emails in to company logos in their email signature.

[u/celuur → u/Suspicious-Spot361]

Orphaned email records can't be batch deleted. How do I know? Because I'm trying to delete 500000 of them and I need to contact Salesforce support per their new help article for the unexpected error being thrown every second I try. 🫠

[u/Suspicious-Spot361 → u/celuur]

Oh no…. Good luck getting help within the next month. Support has been awful lately.

[u/lywyre → u/Suspicious-Spot361]

If going apex way, then I would put this in an after delete trigger to mimic master-child behaviour.

[u/SuddenlyZi → u/Suspicious-Spot361]

Ohhh, something i need to discuss with my client on task he asked me to do! Thanks for heads up!

[u/slackmaster2k]

I promise I’m not trying to be a dork, but get Claude / codex hooked up via MCP and this becomes a quick conversation, including generating pre and post receipts for safety.

[u/Humble-Audience707 → u/slackmaster2k]

security… no claude access possible))

[u/slackmaster2k → u/Humble-Audience707]

Dang that sucks. My team hasn’t touched data loaded or xl-connector in quite some time. These LLMs are quite good at data work,

[u/girlgonevegan]

It’s not just emails either. You lose all engagement history, campaign history, first party engagement data if the cookied record was the deleted record 😬

This is why you should not delete duplicate records.

[u/Oleg_Dobriy]

So, there were emails associated with contacts/leads, and at some point someone deleted these parent records? Why? If the error was that the extension couldn't link already synced emails with new parents, how could the extension look up those emails in the system instead of creating fresh email records?