As far as good contemporary horror games go, shout out to Lone Survivor. It's a 16-bit styled 2D survival horror game. It ends up being not the greatest by the end, but the first half is unlike anything I've played before and genuinely unnerving and upsetting.
As for where RE went wrong, it's a tricky question, because it's so entangled in what made RE4 so good in the first place. It's kind of like how Scream killed slasher movies. And then got totally terrible itself with the sequels.
What I've been thinking lately, especially after playing through Dark Souls, is that someone needs to go really far in the opposite direction from RE5/6. Play it like an actual zombie movie where if you get bit, it's over. It would have to be really hard and unforgiving. Every single monster would have to be as awful as it would be in real life to tangle with one, and you would be better off 99% of time avoiding all fights. I'm thinking a rewind function would go a long way to make this kind of game work, which new console advances could make a reality.