Will you be playing on a server or a LAN? Some mods don't work for servers.
There's always millenaire. That's the one that adds villages, which give you quests to help them, which builds up your reputation, which lets you do more things, etc. There may be others like that as well, but that one has been around for ages. Gets tedious after a while, though.
You could try a multiplayer of Agrarian Skies, the thing I'm playing now. (It works as multiplayer, right?) Seemingly neverending quests in this one, loads to do. I'm enjoying it, even though I apparently wasted several hours doing repetitive tasks I didn't need to do... If you try it, read through the helpful tips others have given me over the last few pages of this thread. : )
I'm also a big fan of Mystcraft and Tinker's Construct. Mystcraft lets you make linking books that create new worlds to explore. TC lets you make custom tools and weapons part-by-part, and makes it possible to find lakes of molten metal in Mystcraft worlds.
Or there's Galacticraft, where you can build a rocket and fly to the moon. That one is VERY difficult. You have to make a NASA workbench (very complicated to make) as well as a space suit and oxygen and rocket fuel and everything. Then the moon is literally a whole new world to explore (and if you don't go prepared, you could end up suffocating!). This one works well with Buildcraft and other tech mods, and you can find the whole lot in the Technic launcher (Tekkit pack), which is designed for multiplayer.
You can download the technic launcher (
http://www.technicpack.net/) or the FTB launcher (
http://www.feed-the-beast.com/) and just browse through the different packs until you see something you like. It's very easy that way, and that's how I got my mother to try a modpack with me for the first time (Big Dig). One of us just starts up a single player game and then opens it to LAN. Works flawlessly, and there's no fussing around with mod installations, since the launcher does it all for you.
You can watch some of my videos if you want a quick look at a few different mods (link in my sig), or there's loads of videos from the YogsCast and others which will show you all the different things you can do. Minecraft is basically limited only by your imagination and the dedication of the modding community -- and the modding community is very dedicated!
Once you play with mods, you might never go back to vanilla. Anytime I try to play vanilla now, it's like playing with a bucket of rectangular legos after using the giant robot Technic set. It's just... what's the point? You can't *do* anything!