Quentin Tarantino once said a film's opening credit sequence is the only chance you get to set the tone. Interesting coming from a filmmaker who normally uses a crawl, relying only on the music--usually a oldie or something from the classic rock era--to establish mood. Anyway, he's not wrong. Not all movies have a dedicated opening credit sequence, and not all that do are anything special. But there are some films that make an effort, effectively using the opening credits to establish the film's overall tone, while at the same time indulging in stunning visuals and great music.
It should be noted that credit sequences are usually produced by a third-party that has little else to do with the film's production. That helps explain why, sometimes, the credits are out of step with the rest of the film, or as in the case of Lies and Illusions, far superior to the film itself.
Final Destination 5
The explosive 3D opening title sequence of Final Destination 5 blends horror with fun, using the music to offset the violence of the visuals. Also, the credits feature the different ways characters have died in the franchise.
Lord of War
The opening credits in Lord of War is called "Life of a Bullet." It's the only such sequence I know of that has a name, and it's a brilliant bit of filmmaking that documents, in first-person, the life cycle of a 762x39mm bullet.
An animated typography sequence that is much, much better than the film it precedes.
Shaun of the Dead
The opening credit sequence for Shaun of the Dead is a brilliat metaphor for the mindless routine of daily life.
Justice League: Doom
In the opening title sequence from Justice League: Doom, the credits play over a compter screen and we watch as information on each member of the League is accessed and downloaded. The scene is important to the story, providing some background and setting the stage for an explosive conflict. Warner Brothers has produced some excellent animated features from the DC universe, and while Doom isn't necessarily the best one, its opening credits are better integrated into the plot than some of the other WB/DC movies.
Hostage's opening credits uses typography to create a dramatic sequence that sets the scene for the film's opening. The ominous music suggests the story will take a dark turn, and the dynamic camera readies the audience for action.
The title sequence for Delicatessen is a masterwork of set design. The credits are physically present in the cluttered apartment, and each credits reflects the person's role in the making of the film.
Eshewing visual metaphor in favour of visual feast, Zombieland's opening credits combine 3D typography with on-screen action to create an immersive credit sequence. The slow-mo helps emphasize the violence and gore, while the voice over and music set the tone; it's a perfect blend of horror and humour.
Run Lola Run
The opening title sequence to Run Lola Run is a mix of live action and animation that seems at odds with the rest of the movie, although it does prepare you for the stylized nature of what's to come. The introduction is voiced by a character from the film who likens life to a game. The techno soundtrack pumps up the viewer, while the credit sequence introduces the characters or, more suitably, the players in the film.
Any James Bond movie
Seriously, any one will do. I'm partial to Goldeneye.
Historically, James Bond opens with a music video that represents the film's theme. The vidoes also feature dancing girls, which represents another, different theme common to all Bond movies. Die Another Day broke from tradition by moving the story forward a little bit in its video, and the opening to Casino Royale is the only one I know of that features rotoscoped animation (and no girls).