Design Names Anthology, while not a mod, is still quite useful. It adds several hundred new design name files to choose from.
http://dna.spaceempires.net/
Pirates and Nomads is definitely worth a play, even if Suicide Junkie never updated it to take advantage of the new 1.91 features. P&N, along with Devnull Mod and TDM, are 3 of the first of the great mods.
http://www.geocities.com/hohoho611ca/pirates.html
Devnull (lacks a website, so you get this)
http://home.t-online.de/home/320045362204-0001/
If you want an incredibly different game, try Adamant. And I do mean different. [img]/threads/images/graemlins/Laugh.gif[/img]
http://adamant.spaceempires.net/
There are a lot of mods available for download from SpaceEmpires.net, so browse through there at some point.
SpaceEmpires.net Downloads
If you want a few hundred extra shipsets to choose from (never play the same race twice!), you can get the Shipset Archive from:
http://kazharii.no-ip.com:6969
Note that this requires using Bit Torrent, which you can find information on in the stickied "SE4 File Downloads Tracker" thread at the top of this forum.