My vote also for these earlier suggestions: Everything by Adam MIller
Gets better after start and absolutely brilliant before the end.
He's a genious at showing what can be done with the toolset, also plenty of combat & roleplay & romancing. Everything by Stefan Gagne
Elegia Eternum and Excrucio eternum are dark psychological thrillers, Penultima series are humor to the tune of Terry Pratchett (Rerolled series are much better, but all are neat)
Hex Coda is a beautiful gem of something completely different, humor and grand adventure in a world where Open Source Magic fights Corporate Magic. A Dance with Rogues
The Bastard of Cosigan
Adult content both in the usual sense, but also as in deep involving storylines in a realistic setting that wont appeal to hack&slash fans. My own suggestions:
Modules by Baldecaran http://nwvault.ign.com/View.php?view=User.EntriesListing&id=5693
The Cave of Songs is the most ordinary of these, but has its highlights and serves as a starting point, if you want to take the same character through all his modules.
Honor Among Thieves is probably the best thief module ever, (unless it's A Dance with Rogues) very grimy, dark realistic setting with truly terrifying high magic lurking in the shadows. The Prophet
is the bees knees, maybe the best module series ever made. You can see the future, the world is going to end. Can anything be done? The Wanderer http://nwvault.ign.com/View.php?view=Modules.Detail&id=3989
The best "slow starter" I've yet seen. You know the kind of adventure where you do odd jobs for few GP's, so you can buy that bow, so you can beat those few orcs, so you can search that cave. The world is open and extremely solidly built. You do things in your own order and you always have lots to do, but while you wait the world moves along. A Tangled Web http://nwvault.ign.com/View.php?view=Modules.Detail&id=60987&id=4345
Repeating myself a bit here, but if The Prophet isn't the best Module series, then it's this one. No custom content, just creativity and unbelievably captivating plot. Pretty Good Character Creator http://nwvault.ign.com/View.php?view=Modules.Detail&id=4215
Quick and easy way to whip up the kind of character you need for that particular module. A Drow ranger with keen scimitars, a few potions and a couple of magic rings? You got it! Now off to adventure!