There are loads of great suggestions here. Not enough people have played Realms Of The Haunting so that would be my personal choice from everything mentioned so far. It’s one of the handful of decent FMV games and more than a little weird but in a good way.
If you want something obscure, The Legacy was an atmospheric Lovecraft inspired first person dungeon crawler and the last game ever put out by Magnetic Scrolls. I really enjoyed it but it seems to be forgotten these days.
Veil of Darkness was a decent SSI RPG featuring vampires that I vaguely recall playing many years back.
Finally, the Call of Cthulu series (Shadow of the Comet + Prisoner of Ice) is well worth a look as far as point and clicks go.