I forgot all about the post in the process:

Superman: The Movie of course:
http://www.youtube.com/watch?v=-Yvd9ipv9o8

Foxy Brown (1974)
http://www.youtube.com/watch?v=zLfykjWNPQ0
These are so awesome, I have not yet finished the movie, from rewinding and RE-rewinding the opening credits. Obviously, this is where the idea for the "Superbad" credits come from.

An American Tail (1986)
(can't find the credits. This is the main title theme: http://www.youtube.com/watch?v=yoxP--U0j7M)
The combination of James Horner's music and the various abstract and literal uses of snow (including having all of the credits blow away int snowflakes) sets up a movie that isn't your average cartoon feature. Most animated films have rather unmemorable credits sequences.

Way Out West (1937)
(Mistakenly believing this film is no longer under copyright, someone has uploaded the whole thing to YouTube. I'm not linking it; I'll use this link instead:
http://www.lordheath.com/modules/gallery/albums/Way_Out_West_credits/img/111226030032_way_out_west__credits_.jpg)
A number of other Laurel & Hardy films could go here instead (Babes in Toyland, for example), even some of their shorts. The Hal Roach films always had elaborate and interesting opening title cards until they standardized them in the late 1930s. The "Way Out West" opening credits aren't elaborate so much as uniquely pleasant. Since the film is a wild-west parody, the credits are drawn up with Old-West scripts and illustrations, accompanied by a great Marvin Hatley tune.


Dreamgirls (2006)
http://www.picturemill.com/Dreamgirls.html
(These are actually closing credits. The film has no opening credits.)
Almost overdone (some credits complained that the credits were damn near "For Your Consideration" adds embedded into the actual film), "curtain call" style credits don't get more elaborate than this. The ones from the 2007 remake of "Hairspray" are less showy, but similar.

Psycho (1960)
Vertigo (1958)
North by Northwest (1959)
http://vimeo.com/album/263376/video/21977929
http://www.youtube.com/watch?v=5qtDCZP4WrQ
http://www.youtube.com/watch?v=jIlqatMQSgI & http://www.youtube.com/watch?v=lAmem5wVOUU

I can't say much more than has already been said about them in this post. While Vertigo's are probably the best, Sal Bass gets credit on the "North by Northwest" titles for his silver-on-green variant of the Leo the Lion MGM logo.

As for Bond films, I'll just link this:
http://www.starpulse.com/news/index.php/2008/11/14/top_10_james_bond_opening_sequences
Apparently this is a controversial choice, but these were my favorite Bond titles:
http://www.youtube.com/watch?v=3JBzxKLs-dY ("Die Another Day")
Though these are great too:
http://www.youtube.com/watch?v=qGPBFvDz_HM ("GoldenEye")
http://www.youtube.com/watch?v=mPiUMNrO2LI ("Goldenfinger")
http://www.youtube.com/watch?v=58Y_U4XZupY&feature=fvwrel ("From Russia with Love")
http://www.youtube.com/watch?v=3LOqHSXMHJo ("Dr. No")
http://www.youtube.com/watch?v=Nfc9GLxlhEw&feature=related ("Casino Royale" - which I saw w/ another OKP when it was out, who leaned over in the cinema and asked - a smidge loudly - "so how long did that take to do in After Effects?")

Honorable mention:
Barbershop 2: Back in Business (2004)
http://www.youtube.com/watch?v=-psRIkZvXw4

I know, I KNOW. But...
The opening credits to "Barbershop 2", though I despise the cheesy-looking CGI main title card, are fun and silly in a way most urban comedies are (oddly) not, showing black hairstyles (and how white folks appropriated many of them) through the years via Ken Burns effects. The Michael Jackson photo montage - from little black MJ to old white MJ - is hysterical.