The ones I remember recently are:

*The Witcher 1 for more places than I care to list here. ^_^
*LOTRO, especially Gollum's lair. Good lord Turbine hit a bull's eye with this one. Oh, and the boreal auroras over the Forochel are pretty neat too
*Mass Effect 2, especially its last DLC, when you have to find your way on the *Shadowbroker's ship. I actually stopped several times just to watch the amazing scenery. *Mass Effect 1 also did a pretty good job with its free DLC: being on an asteroid on its collision course with a planet, being just next to the gargantuan jet engines that push the asteroid, that was something...

For older games, hmm, let's see...
*Might & Magic VI: you arrive in New Sorpigal and... that's basically all. realizing that there's a whole world to discover beyond that little town was pretty amazing.
*Ultima Underworld 1: it was so amazing back then that the impression lasted until today. And when I launched the GOG version, I was soo happy the impression was still there I had to squee like a girl! ^_^
*Valkyrie Profile on PS1: the only import game I ever bought, I basically had to sell an arm to buy it, but I never regreted it. Everything in that game is perfect.
*Zelda a Link to the past on SNES: do I really need to say why?
*Zelda Wind Waker on GC was beautiful too. I know many people didn't like the cellshading effect, but I just loved it.

I've been known to strafe-walk or fly around vehicles and buildings just to soak in the details.

For some reason, TPP games will give me a touch of vertigo when looking over, or jumping off, cliffs, especially when I make a jump and am not certain that I'll manage to land on that little ledge that I'm aiming for. Oddly enough, I don't get the same feeling when, say, making a parachute drop. I know that's not exactly an art-type thing, but it is a visceral effect I feel with some games.

My issue is most people's conception of video games as art is an incredibly odd one. It's along similar lines as early 19th century aesthetics. Why don't people adopt the Deweyan concept of "art as experience??"

That's because public education in general gives people XIXth century outlook on EVERYTHING ;P. The notable exception might be IT classes, but I'm pretty sure that's out of necessity, rather than preference...
Now go ahead and share a story, Remorseless One.

In "The Sands of Time", I would often stop to stare at the beautiful architecture and decor... still love this bloody game...

And setting off multiple bombs in Mercs 2 the flying above to watch the fire and explosions was always astounding, especially when you think of how crappy the graphics are in general. That was basically the only reason I bought the game :P

From the immersive and emotional but not quite enjoyable experiences:

Shalebridge Cradle - I dislike horror games/movies and only love for Thief kept me through that level. I cannot deny that the design and ambience was brilliant though.

Dark Messiah - I'm kind of an arachnophobe, but somehow almost managed to get past the spider lairs. Almost, for the moment I approached the old well (you have to climb it to exit) I did a hard reboot. Seeing them going down every wall was too much ;_;

Morrowind looked great when it came out (and still does imo). However, what really did it for me was the variety of architecture, how each race and faction had their unique buildings. Very impressive, and made each city look unique and interesting.
I also found WoW beautiful in a similar manner. You can tell at any given time where you are, because the look and feel of each area is unique.
Also found the worlds beautiful in the Gothic series (including Risen and Gothic 3).
Also, despite being a hater, I found the forests beautiful in Oblivion.
Oh and I loved the snow levels in Modern Warfare 2, some of them looked fantastic.

Well folks, I've got to point you to a very interesting video from the extra credits series Extra Credits: Aesthetics Vs Graphics (Link) .

I'd reccomend checking out this and the rest of the series too, especially if you’ve got an interest creating games.

Psychonauts-

Black Velvetopia.
Mind of the Milkman.
the list of aesthetically pleasing parts in that game goes on and on.

Well... not so much with the graphics, I have to say. I tend to be a bit behind with the hardware required to get the best out of my game's visuals. By the time I've gotten the hardware to display a game in all its glory, I've usually played it to exhaustion and begun coveting the next batch of eye-candy my machine can't quite display :-)

Having said that, a while ago I downloaded an Oblivion mod that let you discover and use Aduril, The Flame of the West, forged from the shards of Narsil. And Christ is the sword a thing of beauty...

Aurally, when I played 'System Shock 2', when Shodan reveals herself... Well, let's just say the audio work on her voice was absolutely incredible, as I'm sure anyone who has played the game will attest.

But in terms of overall aesthetics, I agree with swizzle66. 'Psychonauts' is stunning.

I really appreciate good art design over amazing life-like graphics.

The Metal Slug games are great for just admiring the scenery and some of the goofiness that they place in the levels. Another great series for this is Yoshi's Island on the SNES - it is probably one of the best looking games I've ever seen and I can spend ages playing through it just to admire the art design of the game.

I'm mostly an evil console gamer for older games so I don't have much to say about classic computer games. The Witcher 2 is really great to explore though and it is making the game take a loooong time for me to finish.

This.

For my own, "Call of Cthulhu: Dark Corners of the Earth." Much of the art design in that game, particularly when your character Jack Walters stumbles upon enclaves of Cthulhu or Ancient Deep Ones activity, is breathtaking in its weird, otherworldly beauty. Of course I find the whole game to be quite enthralling to visually behold, even the nondescript dilapidated exteriors of the numerous Innsmouth locations, because you never now what new vertiginous "horror" awaits you, lovingly detailed in all its disturbing glory, 'round the next corner.

"Condemened: Criminal Origins": This one because I'm follower of the school of beauty-in-urban-decay. One of my hobbies is urban exploration/spelunking. Though none of the locations in that game one could call conventionally attractive, I found an undercurrent of sinister allure to them.

I thought "Bioshock" would be similarly aesthetic an experience until it revealed its true nature as a garish cross betwixt a shopping mall and Ayn Rand amusement park. So no for me there, unfortunately.

Many of those painstakingly rendered, 2D art-background isometric games (Black Isle/Troika) achieved this aesthetic affect, often in conjunction with music. I'm thinking in particular of the Druid Grove in Baldur's Gate 2, and the outdoors map of Spellhold from the same game, the one just before you enter through the front door.

I'll mention this in the timeliness of its release on GOG, Sid Meier's Alpha Centauri. The main world screen had the Brian Eno-esque ambient music of the specific faction you play as the map of Chiron fills it up, in all its wondrous, mysterious, xeno-fungal mind-worm filled terrible beauty. As the graphics are dated now I imagine that effect is muted on modern gamers, but for those of us how were Civ fans upon SMAC's release, we were quite taken in. It had some of greatest aural "aesthetics" of any game I've played, thanks to those technology and facility blurbs that were spoken when you first discovered or built them.

Half Life 2. The first time you step out of the train station and see the city... beautiful. It literally took my breath away.

I'm not sure I agree. As much as I adore that game, I always felt that the visuals could have used a lot more polish. That - and I hated the slutty costumes of all the female characters. It's too "in your face". Subtlety is the key.