Another vote for Starcraft. It's the most fun I've ever had with an RTS (bearing in mind I basically only play single player). It's got REALLY fun units and setting. Three sides that are actually different from each other. And a really great, fun story-all things that most RTS don't have at all.
Unfortunately most of the time when I try to play a new RTS, I find it too hard, and even more often I find myself thinking "boy, I'd have more fun just replaying Starcraft".
I played the original Age of Empires and enjoyed it quite a bit. Played the 360 demo for Battle for Middle Earth II, and thought it looked TERRIBLE, was hard, had incredibly generic gameplay and units, and just wasn't any fun at all. The PC version's probably a lot better though.
I STILL Haven't gotten to Command & Conquer 2 or Warcraft III, that's how far behind I am
Warcraft III is probably really great too. Warcraft II is still solid (and also still looks fine), but the gameplay's worse than Starcraft, the story's WAAAAAAAAAAAY worse than Starcraft, and the settings a lot more generic IMO.