Of course he isn't really scary anymore. This isn't the 20's, and we've sort of gotten over the idea of our own insignificance.
I still read his stories, though. While I'm not really frightened by them, they're still entertaining. And honestly, isn't it a good sign of mankind's progression, that if a Shoggoth or some other Lovecraftian horror was found, we wouldn't be going mad by the mere unusualness and alien nature of it? Hell, we'd be all over the poor thing with instruments and cameras and whatnot. It'd be on a famous talkshow, explaining its motivations and feelings of humanity by the end of the week!
The fear of the unknown and the alien isn't nearly as all-present as it was back in Lovecraft's time, and the idea that we weren't under the ever-caring attention of some divine, well-meaning being in the very centre of the universe must have been harrowing in a way that is difficult for modern readers to recognize back when Lovecraft lived.
And lest we forget, he was also a massive racist. No wonder his main theme is fear for the unknown, when he couldn't even speak to a black person without breaking out in cold sweat by the way he goes on about them in his stories. If it isn't some scheeming jew behind it all, it's some primitive, spear-chucking tribe in the south seas, sacrificing people to the Deep Ones at the drop of a hat.
Again, I don't find Lovecraft very scary, either. I read a few of his stories once in a creeky old house on the countryside with late-summer pitch black outside (Complete with scary winds), and slept like a baby as soon as I put out the candle. But I still read them, even if they don't meet my personal horror-criteria.
For instance, one of my favorite LC-stories is one about a WW1-era German submarine captain, who finds himself adrift and alone inside his sub, at the very deepest depths of the ocean. It's still tickling my imagination. What could be there? Who built those ruins? To what purpose? And what could dwell within?
While his favorite methods of frightening me doesn't work anymore (Creatures so unfathomably alien that they defy description, going mad by a glimpse of the same, us humans being like mould in a petry dish) because I live in a different century (and doesn't have a mind built out of wafers as opposed to every Lovecraft protagonist), he still sparks my imagination, and that is really all that I ask of a good book.