Can you name one diplomat? Maybe Chris Stevens, the Ambassador in Libya who was killed on September 11, 2012?
Right. Obviously fame should not be driving your decision to pursue the diplomatic life. You may have a certain amount of fame within the small area of the capital city of the country you're based in, but that's not fame exactly; it's more like… recognition. And with recognition comes responsibility, meaning your sense of "fame" shouldn't flood your sense of self so much that you turn you into a Britney Spears displaying all at a gas station, or Charlie Sheen taking his freak show act on the road (your "goddesses" in tow).
In other words, no: Unless you become a very high ranking diplomat (Hillary Clinton, Kofi Annan, Benjamin Franklin), you probably won't find fame as a diplomat.