Seems like I am bringing some games to the table which were not, or hardly mentioned before.
I am not so sure whether games actually became less atmospheric, or it's simply much harder now to impress me and I became more analytic, seeing straight to game mechanics.
It might actually be a mixture of both.
In any way, there are hardly any RPGs in the last year which completely impressed me atmospheric wise.
In addition I have several memories of RPGs of which I have strong atmospheric memories from single parts of the game, while other parts of the game were more mechanical. I guess nostalgica also plays a big role as this helps to blur the lines and a atmospheric scene often becomes an atmospheric game if you wear nostalgic glasses.
Anyways, here are my picks, some of them are just scenes, others are complete games:
One of the oldest games which had a very atmospheric scene was
Realms of Arkania 1 - Blade of Destiny (1992). While the game was cool overall, I found and still find that the intro was done incredibly well. It builds up, explaining all the background, supported by great music and ends in the phantastic perspective of an adventure group, who knows nothing of it, but is eager to travel the world for adventure. It's amazing how some pictures, some text and great music accomplished that atmosphere.
I'll link the German CD Version as it just has the coolest Audio:
The Intro:
https://www.youtube.com/watch?v=7yWxQVgYOEQ
Shortly before that is
Eye of the Beholder 2 (1991). Same thing. The game was quite good, but what I found was incredible was the intro, in particular when in the Mages Chamber.
Again it's a great combination of Graphics, Text and Music. Westwood did a great job generall regarding Audio and Graphics. Love that in all their 90s games.
The Intro:
https://www.youtube.com/watch?v=A0lteK-l7YQ
Also check out this amazing cover of the theme:
https://www.youtube.com/watch?v=g_sLUsxDc1U
Another game which had a great atmosphere of a completely different kind was
Daggerfall (1996). I don't know if you necessarily call it atmospheric in terms of this thread. The atmosphere of Daggerfall was not generated by Story, Graphics or Sound. But by size and freedom of possibilities, similar to Elite 2. You could just venture out and had an unfogiving (and completely bug ridden) world which didn't give you anything without fighting for it. I mean 47 regions, 15000 towns and 750000 NPCs. And this atmosphere is also why all the successors in the Elder Scrolls line were disappointments to me. I hope that Malevolence and Elite Dangerous, both games of a similar scale will bring back the atmosphere when I am going to play them.
Some random gameplay:
https://www.youtube.com/watch?v=l2xa2r-0bTI
Next up is
Albion (1995). This is also a game which did not just have some atmospheric parts, but was a very atmospheric experience as a whole (or at least the first dozen of hours or so where you were in the first area type). The reasons for that are again the graphics, which were kinda exotic as they were "rounder" and you were able to go full 3D in dungeons, the setting (you played some space travelers who crash landed on a planet, waking up in a djungle with exotic catlike habitants) and the sound background (djungle noises). A great experience in a game which is hardly known.
Random Gameplay:
https://www.youtube.com/watch?v=iadPq6KwvYU
Next is one of if not the most atmospheric games I can remember:
Stonekeep (1995). This game I actually took as "consolidation" game instead of Dungeon Keeper at my birthday as Dungeon Keeper was -again- postponed. And while Dungeon Keeper later on was a big disappointment for me, Stonekeep, which I took due to the promising Box Art, however completely grabbed me.
While the intro special effects were really horrible and the graphics in the game were not so good either, almost trashy at some point, the sound environment was one of the best I have experienced.
The intro still gives me goosebumps due to its music (at 1:40 onward at the link below) and the whole game was extremely atmospheric as well. A great mixture of music, sound effects (the footsteps alone are amazing), and a dungeon which actually felt alive for a change.
The Intro:
https://www.youtube.com/watch?v=t0VSop8sJr4
Random Rameplay (with footsteps!):
https://www.youtube.com/watch?v=DSd4QePlOA0
Then there is
Diablo 1 (1996). What I really love about Diablo 1 is the extremely dark vibe it sends out by it's dark graphics, and gloomy sound and music. The atmosphere in Diablo 1 is imho not reached by D2, which receives more love than D1 from everyone I know, or D3.
Random Gameplay:
https://www.youtube.com/watch?v=Fl5I5CUTqh4
Another underdog is
The Fall: Last days of Gaia (2004). While it had a horrible release, it was well patched later on. This post apocalyptic game provided the most atmospheric quest I ever experienced, where I was sent to a journey to a distant place called Eden, where should be Water and Vegetation. I won't spoil what happened, but it was really amazing as far as I remember. The rest of the game was quite atmospheric, and decent.
I think the story and the music provided the biggest part of what made the atmosphere so good. The music is just amazing for a post apocalyptic game.
Official Trailer with Gameplay:
https://www.youtube.com/watch?v=NzGgqiaDzKA
Playlist of Songs which I actually uploaded myself some years ago:
https://www.youtube.com/watch?v=7zyScCU_O4E&list=PL89D631BACCCD1385&index=20
Time for another game with lots of Desert and the only MMO in the list:
Anarchy Online
Anarchy Online was buggy as hell when I played it at release. But what was absolutely stunning was the music, which made it an extremely atmospherical experience when it actually ran.
In addition it had one of the most moving Intro videos imho, which was also a great way to introduce the world.
Intro:
https://www.youtube.com/watch?v=6YKJM0vOroc
Music:
https://www.youtube.com/watch?v=Zg8ryBJPTCg
We are not leaving the Deserts yet. Of course there has to be a fallout title in the mix.
Personally the most atmospheric were actually single parts of
Fallout 3 (2008). While quest and gameplay wise the inferior game to Fallout NV, in my eyes it had the better setting and the quests which were in the game, were more memorable than the ones in NV. But what was most memorable for me were all these little grim sceneries, which made you shiver or go wow!. Like a little room under a bridge where some dude searched shelter and in the end went crazy. Satellite dishes which were misused for a apocalypse party, skelletal remnants of a dead and couple with their child lying in a bed, where they decided to kill themselves rather than live in a post apocalyptic world.
That's the post apocalyptic atmosphere I find really fascinating.
Besides of that Fallout 3 also put TONs of work into tiniest details. Like a house of rubber and pens on some desk. Things like that made the world feel alive (and then there was also the horrible grind...)
Some Gameplay with Eastereggs:
https://www.youtube.com/watch?v=fy-4v43HxmU
The last and most recent game I want to mention provides a very different atmosphere again. An atmosphere which fits perfectly to the setting of Spaniards meeting native Americans in
Expeditions: Conquistador (2013). The Game did overall a great job in representing the game of that time, treating both sides with respect and giving you lots of choices.
What I will link here is probably not the most representative, but my favorite track in the OST:
https://www.youtube.com/watch?v=G-woF5nb7H4&list=PL7n9Lk9WUhM--QO2z4VyhYFU-lBHIPlbq&index=3