If you want a definitive listing of all your options, you can check out my fan campaign listing.
That list is very overwhelming and intimidating though, I understand, so here's some recommendations:The Antioch Chronicles
is a great starting point. Often seen as Blizzard-quality classics, this trilogy was unfortunately never completed, but the first two campaigns are more than worth a gander.Echoes
is a personal favourite of mine. It has a very rich and moody atmosphere to augment a very classical storytelling formula. I also dig the dark jazz soundtrack in many of the missions.
If you're interested in some comedy (and don't mind my shameless plugging,) my own campaign series including StarCraft: Gas
and it's interquel Ashen's Quest
might be worth a gander.
There's also plenty of Campaign Creations campaigns that are worth a look (such as Life of a Marine, Flame Knives, the Celestilol Stories, Vision of the Future, Legacy of the Confederation, and the Bob Levels etc) but with the CC site down you'll probably have to dig through this master download
for links for now.