Basically, this is how it goes:
A CPU fsb at 1066MHz is a quad-pumped motherboard fsb of 266MHz. Since RAM is DDR (double data rate), that would be 533MHz. So you only need DDR2-533 to run your Core2Duo at stock speed. However, if you get DDR2-667 or DDR2-800, you're guaranteed at least some overclocking headroom. If you pick CAS4 or CAS3 memory at a certain spec'd speed, most likely it'll overclock higher than CAS5 memory at the same spec'd speed.
I'd go for the best bang for the buck for moderate overclocking.. DDR2-667 CAS4. I've never had a problem with this memory (Corsair, Crucial, mushkin, Patriot brands) getting to 800MHz at 5-5-5-15 timings. A pair of DDR2-667 CAS5 sticks might get you to 800MHz, but they might not make it past 700-750MHz at 5-5-5-15 timings. In fact, many companies use the exact same modules on the 667C4 and 800C5 sticks - just pre-set at different speed/timings (with the higher speed/higher timing sticks priced $10-$40 higher, even though they're the same).
2x512MB Corsair XMS DDR2-675 CAS4 is $109 at newegg.
There are a few 2x1GB DDR2-667 CAS4 models in the $210-220 range at newegg, and the Corsair model is $240 ($210 with the $30 rebate).