Well, there are full conversion mods, mods that add extra creatures or items, and mods that are designed to make your gameplay experience more fun or more realistic. If you're looking for a full conversion, you might try Putnam's Fallout mod, perhaps. I think Deon's Genesis mod is pretty good, too, but I can't remember if it's for Adventure or Fortress Mode. If you're looking for an extreme challenge, you might try Fortress Defense, but make sure you batten down the hatches.
If you're wanting a single new creature type, your best bet is to either mod it in yourself, get someone else to make it for you (I'd do that, just ask), or download a larger mod and take single creatures from it. I have one creature in particular that I've spent a lot of time on called a Scythod... I should probably re-upload it.
Finally, if you want to mod texture packs, there are a lot of different good ones you might try. Personally, I like Phoebus, when someone asks:
Keep in mind that more in-depth texture packs like Phoebus make your savegames unlikely to work well if you send them to someone else, especially if you've custom-modded the game. If you're looking for a regular tileset, those are easy to come by, too.
There are also utilities... I have one that made random procedural creatures, but I still need to update it to the next version. I also have another that makes "radiation zones" that do bizarre things to your dwarves, which you might potentially find interesting as well, as long as you don't mind blood, vomit, pus and miasma all over the place.