If you were married, this would likely be blackmail material.
Occasionally, though, it does work like you say. I think there was some Asian(?) politician that they tried to blackmail after something like this, and he basically said: "Hey, could you send me a copy of the sex tape? She was smokin' hot, and I'd love to have the video."
John McAfee (a known liar, so take it with a grain of salt) claimed in one of his interviews he gained and kept a clearance despite adultery and drug use by being brutally honest publicly about it all, so he was utterly unable to be blackmailed.
Occasionally, though, it does work like you say. I think there was some Asian(?) politician that they tried to blackmail after something like this, and he basically said: "Hey, could you send me a copy of the sex tape? She was smokin' hot, and I'd love to have the video."