Morrowind Summit has most of the better mods out there.
First get yourself some sort of beautification mod... no offense to Bethesda, but while the rest of the game is great, the face and body models are horrendous. I use Better Faces but you could also use the Astarsis series of face replacers or anything similar.
There are various tweak mods but most of that you could do yourself with only a little practice in the construction kit so I'll list some that actually add new things...
Marksman Mod. The name is simple but the mod changes the way the marksman skill works and adds literally dozens of new weapons throughout the game. It also adds the ability to fletch your own arrows and bolts.
Assassin's Armory. As close as you'll get to a melee equivalent of the Marksman Mod. It adds tons of new weapons throughout the game and allows you to block with another weapon instead of a shield [basically still a shield that looks like a weapon but it's a nice idea].
Lilarcor +3. The talking sword from [forget which game] now comes to Morrowind. You get it at Arrilke's at the start of the game and it grows alongside you, becoming increasingly powerful the more it kills. There are options in the most recent release to make it shut up if you get irritated by it. Probably the most thought out weapon ever made for Morrowind.
Morrowind Comes Alive! This generates random NPCs all over Vvardenfell, but they make sense. For example, you're likely to find random warriors in the fighters' guild, fences in the thieves' guilds and pilgrims by shrines. I never play without this one, basically I no longer know every single NPC in the game.
Private Mobile Base. This is perfect if you have tons of gold to spare and haven't been able to decide on where you want your house/castle/etc. It's a giant flying dwarven construct that can be moved to different cities. Bring your base with you.
Vampire Embrace. This mod not only allows your vampire to talk to NPCs [and thus complete quests] but to actually make fledgelings of them. You can command your fledgelings to do your bidding and create an army of undead to defend your base or destroy your enemies.
The following mods crash my game but could be ok if you have a better computer:
Complete Morrowind 1-4. These mods allow you to make pottery, clothes, cut down trees, cook food and other domestic roleplay enhancements.
Morrowind Enhanced. This puts combat combos in the game that let you do moves in sequence that results in the enemy losing an arm, weapon, head, etc. Very fun but generally crashes me to the desktop after 15 minutes or so.
Naturally there are a lot more. I could list a slew of tweak mods I use but those are more of a personal preference. I never play without NoGlo [self explanatory] or a patch to increase vendor money but some players say they unbalance the game.
Anyway, have fun.