
If you're playing a “classic” version of D&D, the rules for the spell probably contain a line along the lines of “[a]ny commands given will usually be obeyed, except that orders against its nature (alignment and habits) may be resisted.” That's a direct quote from the Moldvay Basic book, with emphasis added by me. This, ladies and gents, is how we differentiated between two fighters way back when. Whether you played your warrior as a paragon of virtue, who upheld a code of honor while defending the defenseless, or as an amoral brigand who'd gut his own granny for a shaved copper, it made a difference in the game. Commanding the honorable knight to stab his friends in the back is likely to force another saving throw, and won't be obeyed, where the greedy mercenary will do it without hesitation. Both situations can create all sorts of fun around the table, but of a very different sort.
There are some folks who just can't enjoy this sort of thing. Either they can't separate player from character (and, honestly, in this case, it might be best if you just ask them to leave the game, as they will be a constant source of friction), or they're too uptight about issues of control. I used to be one of these people, and learning to relax and roll with it was hard, but I think I got better. Any time you “de-protagonize” or remove more than the usual amount of choice from the PCs, you're treading on thin ice. You need to be cautious with this sort of thing. If the players trust you, and enjoy your game, most will be willing to roll with this sort of thing and that allows you to really push some in-character buttons.
You've all seen the episode in your favorite serial-esque TV show where the characters are pushed out of their comfort zones and forced to behave out of character by some external force. On “Chuck” it's truth serum, and on Star Trek, it's flying too close to an unstable star that makes everyone on the ship behave like they're drunk. This is where the charm spell really shines. Maybe it allows a character to indulge in a part of their personality they've been denying or suppressing. Maybe it's the push to take them through a mental block, a choice they've been avoiding, or a truth they've been denying.
As a player, charm and other mental/emotional manipulations are your chance to go Dark Phoenix/Dark Willow on the game. Yeah, sure, maybe Mister Goody-Two-Shoes can use his moral fortitude to resist the charm. But maybe he chooses not to. Or maybe, when you scratch the surface, that air of reserve and restraint is the armour that keeps the beast within contained. Maybe its his turn to keep the treasure for himself, or to spend it carousing and having fun. Maybe its his turn to torture and murder the bad guys, to look the other way when expedience wins over virtue, or to kick Christian in his cojones, throw Roxane over his shoulder, and ride off to some romantic hide-away with her.

UPDATE: More cool ideas and comments spawned by the charm spell from the Lost Papers of Tsojcanth:
Charming an NPC allows PCs to peek “behind the scenes” and learn tidbits about your setting without going out of character. This is a meta-tool that can be used either to showoff your effort, highlight or foreshadow something important that you want to make sure players notice (possibly because they didn’t get it the fist time around) or to provide closure for some events players have been puzzling about.
Be sure to read the whole thing.
(Updated 3/25/10)
Image credits: John William Waterhouse and Clodion.