Dark Messiah of Might and Magic - I don't know why this game got nearly no attention, it's just great.
I, for one, hated it dearly because it was a surprising insult to almost the entire Might and Magic line (1-5 & 6-8, 9 being a mess) that Ubisoft bought the rights to. The fact that the normal game disk includes "professional malware" was yet another insult.
Speaking of Might & Magic 6, I would consider that a fairly atmospheric game, if old, and if only because of the music and writing. Although, now that I think about it from that angle, there are a lot of details small and large that exist entirely for atmosphere; detailed/animated images for small, unexplorable houses and stores, voices and expressions for the party members, seemingly useless places you eventually realize purposes to, NPCs willing to talk about what interests them rather than what the "average player" would be interested in... No where near (modded) Morrowind as far as scale, total effort, and resources go, but they certainly did a good job with what they had.
The game that provided the best non-horror atmosphere for me was Morrowind: it has the backstory, graphics and music that suck you into it. The graphics are fine, with mods they can get even more atmospheric. Don't expect this from Oblivion, though I can't say it doesn't have any atmosphere at all.
I've tried to get into Morrowind again (I used to play it back when it first came out) but the surprising lack of mods compared to other games (Couldn't remove physic guards nor could I find a mod that added hungry/thirst/tiredness) put me off playing it again. I really wish I could combine Oblivion and Morrowind. If I could take just a few things from Oblivion and add it to Morrowind I'd be set for ages.
How long ago were you looking for mods? Because I've stumbled across several mods that fix guards in various ways (it's great to see archers on the battlements taking down things chasing you), and several mods that add life functions of different realism levels (I don't think there's one yet that requires a balanced diet, though). Then, of course, there are many mods made by those coming from Oblivion which tweak the world to be more familiar to such. I could collect a proper list, if you'd like?