It's a brief mention in PIC S3. As the crew approaches the restored Enterprise-D at the Fleet Museum, La Forge makes a stray comment, to the effect of:
...and obviously we can't use the Enterprise-E
at which point, everyone turns to Worf, who insists with indignation,
That was not my fault.
There's a beat as everyone makes a face and gives each other a knowing look, and then... that's it.
It's clearly meant to be a wink and a nod from the writers: "Yes, we know you want to know what happened to the E, and no, we aren't going to tell you."
Even the behind-the-scenes materials are mum on the topic. The Star Trek: Picard Logs, posted on Instagram, mention both an incident at Kriilar Prime that apparently led to Worf's departure, as well as a subsequent classified mission after which the ship was taken out of service. (To me, this seems at odds with what is shown on-screen: it's obvious that Worf had something to do with the E's demise, and it's also obvious that the story is common knowledge -- even known to Crusher, who has been "out of the loop" for 20 years. So I think neither of the Logs' stories are satisfactory explanations.)
But, as I just alluded to, there are a few things we can infer about what happened to the E.
First, there's no way it was destroyed and no way it resulted in any loss of life. For one, La Forge's tone is too glib for that -- there's no way he would describe the destruction of a starship in those terms. But more importantly, there's no way that Worf would shirk responsibility for such a thing.
Second, we know it's something unusual, memorable, and (in my opinion) decidedly unclassified. And, I would argue, it seems like it's something... funny. Or perhaps whimsical or ironic or otherwise something that it's polite to strike a glib tone regarding.
Finally, I would suggest that, whatever it was, it happened around 2384. We get a brief sighting of the E in the battle of the end of Prodigy's first season (though it's a little unclear whether it was actually supposed to be the Enterprise or the Sovereign) so we know it's active at least until then. But it seems unlikely that any loss of a starship after the Attack on Mars in 2385 would be considered a laughable matter. What's more, we need the -E to be out of service early enough for the -F to have a reasonable career before being decommissioned in 2401. Assuming that a loss of a starship in the wake of the Romulan Supernova would also not be a laughing matter, that would push an -F launch date perhaps as late as 2389, which seems like an implausibly short service tenure. Retiring the -E in '84 gives some flexibility for when to launch the -F and still give it a long enough service life.
So, what do you think? What are some scenarios that could satisfy the clues we've been given? (I'll put a couple of my ideas in the comments!)