I don't have much to add, so I'll just cast another vote for Sanitarium and the Gabriel Knight series. They're dated, but definitely must-plays.
If you're into Lovecraft, you might look for the various lovecraftian games that Infogrames did: Shadow of the Comet (which samIamsad mentioned), Prisoner of Ice (a follow-up to the former) and the first Alone in the Dark.
We also had a similar
thread a few weeks ago; you may find other suggestions there.