Use it to describe strong emotions that impact one's physical state, not just any feeling. Seeing the Grand Canyon for the first time, I instantly went weak at the knees.
Pro Tip 2/3
Non-literal Use
It's a figurative expression; your knees might not actually feel weak. During the intense horror movie, I felt weak at the knees even sitting down.
Pro Tip 3/3
Intensity Marker
Shows an extreme reaction, more than being just nervous or excited. Hearing my song on the radio made me go weak at the knees with excitement.