Super Metroid has a suprisingly emotional ending for a game with no dialogue.

Planescape: Torment has a highly emotional ending for a game with oodles of dialogue.

Both Freespace games had well done endings.

RE3 wasn't bad either. Heck of a finale with Jill and Nemesis.

And the Merge ending of Deus Ex was pretty cool.