6 speed autos are just starting to come into their own, but that's definitely the direction the auto industry is headed. I've driven several vehicles with 5 and 6 speed autos ('07 Escalade, '06 STS, '05 XLR, '06 IS250, '06 530ix and '06 ML500). It makes a big difference in acceleration and enables the engine to stay in a much tighter RPM band right around the peak of the power curve. Driven normally, the new trannys will help MPG. Driven in a spirited way, acceleration is much better. My understanding is that they're much more efficient at delivering power, too, reducing parasitic losses typically associated with autos to manual-like levels. I think most of this is accomplished with a torque converter lockup in every gear.
Having said all this, I can't speak for the Camry specifically other than to say I don't like the styling, although cars will often grow on me after a while.