I have been looking for a C6 vette for a while now. I only like manuals as I drive like an idiot most of the time. Autos are not much fun with my driving style. However, as refined and smooth as the vette is, it's not "light on it's feet" like a miata or GTi (the kind of car a manual is a "must" in). So, while I would typically shun an automatic, they work well in a C6. I don't feel the auto takes away from the vette experience at all. It's a big, wide, smooth, low revving V8 powered car with plenty of performance either way. (no significant difference in 0-60, 1/4 mile or top speed)
Also, the manual has that forced 1 to 4 shift thing. So, unless you disable it, you won't be rowing through the gears. In stock form, it's not much fun at normal speeds. Many owners simply go 1-4-6 or 1-4-5.
I guess what I am saying is that the vette has so much torque down low, you won't rev it up during normal driving. There is no need. So, the high revving, gear rowing fun factor is not there most of the time.