I find vanilla spells and such to be missing something, especially after playing a melee class, what would be some good mods to spice it up a bit?

I use http://www.nexusmods.com/skyrim/mods/34620/ - not a radical overhaul, so it might not be what you're looking for - but it's well-balanced (unlike many other spell mods) and adds some nice and useful variety.

I like http://www.nexusmods.com/skyrim/mods/13166.

wow, that looks awesome ! Didn't want something that overpowered magic too much, but also didn't expect to gain many spells in the process... Thanks !

With that mod , looks like .."There's no going back! "

I use http://www.nexusmods.com/skyrim/mods/4374/? because I think it makes sensible and non-intrusive changes to the magic system in Skyrim.

In addition, I use mods to add in quite a few missing spells:

Leazer's Open Lock: http://www.nexusmods.com/skyrim/mods/36897/?

Mark and Recall: http://www.nexusmods.com/skyrim/mods/8088/?

Water Walking: http://www.nexusmods.com/skyrim/mods/32900/?

Cure Poison & Disease: http://www.nexusmods.com/skyrim/mods/13535/?

Levitate Revamped: http://www.nexusmods.com/skyrim/mods/29600/?

Feather Spells and Tomes: http://www.nexusmods.com/skyrim/mods/10871/?

Divine Intervention: http://www.nexusmods.com/skyrim/mods/18744/?

Note that you'll need to use a bashed patch with all of the spell mods to get the tomes from each to show up in vendor's inventory.