I make exceptions for DSG gearboxes
Right. If a car is OFFERED with a manual transmission, that should be the transmission of choice. Always.
The only exception is when a manual isn't an option, such as an Audi RS6 or a DSG/Twin Clutch gearbox. These cars are designed with a lot of power in mind, and a manual trans with 500 HP would require a clutch that is simply impractical to use daily. It will either be uncomfortable or high maintenance, so twin disc is the way to go.
If you are comfy with a 200-400HP vehicle, which 95% of you should be, a manual trans with a nice, forgiving clutch is what you should get. Not because its fun (although it is) but because its more reliable and safer than an automatic. Clutches are cheap compared to any sort of auto trans work, and manual gearboxes are virtually maintenance free (maybe 30 bucks for an oil change in the sucker every 100-120k.)
The safety aspect of a manual speaks for itself. You have more control in all weather conditions, especially snow (antilock brakes wouldn't have even been invented if it weren't for the automatic!) because engine braking is far more effective than ABS/Traction Control.
Basic Pro's of a manual:
It is easier to get your car unstuck from snow.
It is easier/possible to regain control in a horizontal hydroplane in the rain.
You can pushstart any manual transmission vehicle relatively easily, even by yourself.
It saves vehicle weight.
If driven properly, it will definately save you maintenance costs.
It is safer, giving more control of the vehicle.
Wider selection of gear ratio's, increasing fuel economy (although this is changing)
No such thing as a 'fluid exchange' or 'trans flush' just a basic oil change every 100k or so
Keeps you awake.
Difficult to drive when under the influence/reminds you not to be driving
Friends can't drive (usually a good thing) and wont ask to borrow/drive your car.
Impress girls.
Impress guys.
Light excercise

Sometimes preferable in traffic since the rolling idle speed is lower (such as with trucks)
Engine braking reduces brake wear/moving foot from gas to brake constantly.
Lower initial cost of investment (automatics usually cost $1000+ more)
Better for the engine (redline is often higher with manual trans ECU's, allows better engine wear, high-rpm engine braking creates 'vacuum' to suck crap out, etc etc etc)
Often less likely to be stolen (theives, and even valet's, sometimes cant drive one!)
...I could go on forever.
Basic con's of a manual:
Not everyone can drive one.
Can be a bummer in traffic when tiresome shifting is called upon!
Sometimes lower resell value.
Easier to abuse.
Sometimes calls for one handed steering (while right hand shifts)
Yea...not a whole lot wrong with a manual lol!