Man, these things are always hard, even when you just limit to RPGs. I know there are bunches that I'm just forgetting.
Writing
Planescape: Torment - good overall story and lots of interesting characters.
Elder Scrolls: Oblivion - the main story wasn't all that much until the VERY end but I do remember lots of really good side-quests. The troll painting, the paranoid guy, SAVE the rats in the basement, the adoring fan, the main quest of Shivering Isles…
Dragon Age: Origins - it's hard for me to tell how much was writing and how much was actually from voice acting, music, and seeing my choices bearing fruit but the ending to this game was really impressive. Some reasonably interesting characters and some tricky moral dilemmas helped, too.
(Honorable mention: Bioshock. Best plot twist evah!)
Combat
The Last Remnant - when you first play it seems like it's almost totally random but, as you learn the system and advance your (many) characters, you learn how to but weights on the dice to get things to roll your way. Mostly. You've also got a lot of flexibility in your union ("squad" would be a better word) make up. Changing from three large squads and a small squad to three medium and two small squads can really change how a battle plays out. A pity the game designers didn't make some of the mechanics more clear but now we've got a Wikia page to give us the straight scoop.
City of Heroes - this would actually get pretty dull in real small groups for the first years of the game but, once they added difficulty sliders, it really took off. You've got a great variety of powers, a great deal of customization over those powers, and a great variety of enemy powers. They also did a particularly good job with knockback/knockdown powers. Ah, running around with my grav/storm controller throwing people all over the place and teleporting them a quarter mile into the sky… it was like fighting in one of those air-pop popcorn machines only the popcorn couldn't escape!
XCom: Enemy Unknown - hey, it won an award so it's got to be an RPG, right?
(Honorable mention: the recent Batman games with their procedural melee fighting. Fun to play and fun to watch! A pity they aren't RPGs. Mount & Blade deserves something, too.)
Atmosphere
Elder Scrolls: Skyrim - climbing up the Throat of the World was just epic. The end scenes were epic. The entire western mountain range was epic. The dragons were epic. Blackreach is ridiculously epic. The aspen around Riften are… well, mostly those are real pretty. It might have been getting a lot of help from 3D Vision but it sure bowled me over. Extra sound mods probably helped, too.
Secret World - This would have been one awesome action RPG if it hadn't been released as an MMO. Even with other players running around, it's still got a really spooky, Lovecraftish atmosphere.
Vampire: The Masquerade - Bloodlines - I only just played this a year or two ago and the atmosphere was great. It must have been incredible when it first came out!
(Honorable mention: Bioshock 1 & 2. Not enough RPGness for me to count on the list but incredible atmosphere!)