I've played in two games in which I came near tears. The scenes were:
1) In a Werewolf game, I played a teenaged middle-class punk who was starting to feel like the Werewolves were where he really fit in. Then he was told that he would die if he participated in an upcoming battle -- but if he didn't participate, the tribe would be slaughtered. After two weeks of angsting, he showed up and saved the tribe and died. As the DM described the death scene, I nearly wept myself. Of course, I was IRL leaving for college in a week, and this was the last time I hung out with all my friends before I left. It was a fantastic end to the campaign.
2) In another game, we all played supers who had heretofore led a regular life. One character was 50 years old, married, and had been chasing after Men In Black for about three weeks, hiding his activities from his wife. Once things calmed down, we went for supper at his house. His wife called him into the kitchen, and as she chopped carrots, she told him that she loved him, that she knew he was drifting away, and that she had his blessing to do what he needed to do. I remember the chopping of vegetables very clearly.
As for myself, I try to evoke a range of emotions. Fear is kind of difficult in D&D -- character death is not very emotional if it happens often, but critters aren't that scary if they're regularly smacked down. But I still try for it. I'm not good at RPing romantic love, but I'll do familial love when I can. Humor is always fun -- it's great to play the terrified populace in the aftermath of a cityfight against monsters. Sorrow is easy to pull out if a PC dies, since PC death is so rare IMC. Frustration is a deliberate ploy sometimes: I like to string the bad guys in front of the PCs a bit before they can go in for the kill, and I try to play the bad guys smart and sometimes cautious.
Honestly, though, I have the most fun when I go for the freakout. Like, "Oh my god -- that stew we almost ate, is that a severed finger floating in it?" I really try to show, not tell, why the bad guys are bad.
Daniel