Half the fun of modding skyrim is searching for the mods yourself so that you can tailor your game the way you want it, some resources for that would be http://skyrim.nexusmods.com/ and http://steamcommunity.com/sharedfiles/browse/?appid=72850&browsesort=toprated, there are other fantastic modding websites but for a novice these two are the simplest to use and have the largest database.
Some quick mods to get you started are:
http://www.skyrim.nexusmods.com/mods/13268
http://www.skyrim.nexusmods.com/mods/141
http://www.skyrim.nexusmods.com/mods/24460
http://www.skyrim.nexusmods.com/mods/7654
http://www.skyrim.nexusmods.com/mods/85
There are plenty more mods out there to be found that will really enhance the atmosphere as well as the visuals of your game but these are all must have mods.