Modding Guide from the Steam Forums - Very well organized for modding newbies.

(Alternative) Reddit's own modding guide. Many good ideas there.

Oblivion Mod Manager Extended - A must have utility for a newbie modder.

Oblivion Script Extender - Required for many mods.

Better Oblivion Sorting Software ( BOSS) - Pretty much required for newbies to modding.

Unofficial Official Mods Patch - Required for most mods.

Unofficial Oblivion Patch Supplemental - Required for the GotY version.

Unofficial Shivering Isles Patch - Required for most mods if you've got shivering Isles.

DarNified UI - Get it. No questions.

Fast Exit - Very useful file to help you exist Oblivion quickly and avoid crashes

Toggleable Quantity Prompt - To avoid all those annoying quantity dialogs

Menu Escape - Another awesome little script to make manu navigation a breeze

Less Annoying Magic Experience - Pretty much required if you're going to play a spellcaster.

Auto-save and time - For those who tend to die or crash a lot and want more immersion

Martigen's Monster Mod (MMM): Your first stop to major modding oblivion.

MOBS Medieval Oblivion Equipment Balance System - Use this instead of the one built-in Fransensco's.

Francescos leveled creatures item - Second comprehensive stop but more optional.

Super Hotkeys - More than 8 hotkeys. Also weapon cycling and quick casting.

Duke Parick's Combat Archery - Pretty much required if you're going to play an archer.

Alchemy Advanced - Pretty much required if you're going to play an Alchemist.

Deadly Reflex - Pretty much required if you're going melee. Works seamlessly with UV.

Stealth Overhaul - A very good option if you're going to play a sneaky character.

Race Balancing Project - Makes races more distinct.

Realistic Leveling - Makes leveling less of a min-maxing business and more immersive

Progress - Use this and only this if you think levelling is too fast in Vanilla.

All Natural Weather - Apparently the best Weather mod out there

No Psychic Guards - If you've played without it, you already hate them.

Unique Landscapes - Another luxury mod but worth it.

QTP3 Redimized - Better Textures. A luxury mod but worth it if you can handle it. 1.6 Gb download!

Symphony of Violence significantly improves combat sounds

Tamriel NPC Revamped (TNR) - Completely changing the look of all NPCs to be much better

Diverse City Guards - Helps to add character in each city

Streamline - You'll never cry about your FPS' again

Enhanced Daedric Invasion - Haven't used this one yet, but looks promising

Integration - The Stranded Light - An extensive quest mod.

Adash - City of Magic - Extensive Main Quest

Some more mod suggestions if you're looking for good quality

The Golden Crest - Another Major quest mod

A Redditor Suggests more Quest Mods