At the time of Morrowind's release, I was not much into using mods. The was also true of Oblivion. In part, this was because all of the really neat mods were fairly complicated to install and keep up to date. Modding in the Elder Scrolls has matured considerably since 2002 and while using mods isn't completely painless, it's certainly in the "take two aspirin" range rather than "I need morphine!" Mod titles link to the page where you can download the mod.
Morrowind Overhaul - Sound and Graphics 3.0: With a clean install of Morrowind and its two expansions, this is the first mod I'd recommend installing. It is a whopping 1.65GB worth of goodness in a RAR package that contains an automated executable installer. Just drop the extracted files into your Morrowind directory, run the executable and follow the instructions.
The mod is compilation of about 55 different things that all go in from a single installer. It's mostly textures, lighting and sound, but does include the Morrowind Code Patch and a few other things. The MCP makes a few minor gameplay changes, but you choose which ones, if any, you want to install. The other goodies include some code optimizers, a 4GB patch and some automated .ini tweaks. Before playing, Steam players MUST disable the in-game overlay or all you'll get is a black screen (from your Steam library, right-click Morrowind, select Properties, uncheck "Enable Steam Community In-Game" on the General tab and click "OK"). You could make a similar change in your general settings on the menu bar, but that would disable the overlay in ALL of your Steam games.
When I first fired up Morrowind (after facepalming myself over the in-game overlay and then fixing it), I couldn't believe how completely awesome the game looked and sounded. All it took was five minutes of running around and just staring to move this to the #1 position on the "must have" list.