Good adventure games excel at this. Riven, Myst IV and Dark Fall were the most atmospheric for me.
Also Ico, S.T.A.L.K.E.R.: Shadow of Chernobyl, and Return to Castle Wolfenstein (I admit the last one might be because it was the first shooter I'd played since Doom II. ...But the crypt...