My favourites:
1) Midnight (FFG): The core book + all of the region sourcebooks for the line (Hammer and Shadow, Honor and Shadow, Destiny and Shadow, Star and Shadow, Under the Shadow, Heart of Shadow, Forge of Shadow, City of Shadow). Still the most evocative sourcebooks I have ever had the pleasure of feasting my eyes on.
2) Greyhawk: The Living Greyhawk Gazetteer and the From the Ashes boxed set (and add to that the Marklands, Iuz the Evil, and Ivid the Undying sourcebooks). Those books brought so much more historical, political and cultural depth to the setting, however controversial. While the strength of the 1983 boxed set was that it provided a sketch of the world for the GM to fill in, the LGG and FtA brought greater cohesion and a distinctive identity to the setting for those who wanted it (I realize that not everybody appreciated the contributions made by those products). In my opinion, only Birthright is on par with the LGG and FtA when it comes to political and historical depth among D&D settings.
3) While not perfect by any stretch of the imagination, both the original I6 Ravenloft and the 5E Curse of Strahd stand out as adventures that I really want to run.
4) When I first started gaming in the late '80s, The Temple of Elemental Evil, Scourge of the Slavelords and Queen of Spiders were modules that really fired up my imagination... and while in hindsight all of those products have pretty significant flaws and fall short of epitomizing great adventure design, I still want to run adaptations of them. There's just something about those old classics that keeps calling to me (I could add Against the Cult of the Reptile God to that list, which I had a blast running back in the day).
5) The original Dragonlance modules... don't know if they've stood the test of time, but when they first came out, I really wanted to play the War of the Lance.
Truth be told, I think the original Forgotten Realms boxed set, and the Birthright, Planescape, Ravenloft and Dark Sun boxed sets all really made me want to play or run campaigns in those worlds (the cloth map in the Revised Dark Sun campaign set alone was a huge inspiration). TSR had a knack for creating brilliant boxed sets. I have since lost interest in the Forgotten Realms, but still dream of someday getting to play/GM in the other settings... if only my players and I had more free time.
As for horrible modules...
Greyhawk had some real stinkers:
WG7 Castle Greyhawk, WG9 Gargoyle, WG11 Puppets, WGQ1 Patriots of Ulek... how those pieces of crap ever got published is beyond me. There are other terrible Greyhawk modules, but those arguably are among the worst offenders.
Interestingly, while I've probably played and DMed Pathfinder more than anything else, I can't say that the setting or any of its adventures scream "must play" to me. I think Paizo has tried to shoehorn far too much into Golarion (kitchen sink design) for it to really inspire me. I enjoy playing Pathfinder, but I wouldn't call it amazing.