Tim Burton's Sleepy Hollow had a nifty title sequence. It played over footage of Depp travelling in a coach across the the countryside, and the title text took on characteristics of the envrionments shown (fading in & out like fog, etc.).
It has been said that once you see the opening titles to a film that Saul Bass has done, you can walk out of the theatre because you know exactly what the film's about: he has shown you the entire thing in the first minute or so.