I think this topic is highly personal-and thus highly subjective.
Kyle highlighted 17 at Sawgrass and its effects on a player, before,during, and after experiencing it. That specific hole is a great example but it assumes some prior knowledge of it. Even if someone has not played the course, most golfers are aware of it and it may instill anticipation or fear.
I don't believe any golf architect designed a hole or course to arouse purely an "emotional" response, playing-wise. Taking advantage of beautiful vistas seems an easy way to arouse anyone's emotions.
I'm struggling to find an emotional term for it, but "discovery" brings me joy.
Be it a hole, shot, or putt, new and unique thrills me. It's why I try to visit new courses.
As for specific holes and emotional states, I've yet to meet anyone who does NOT HATE the fourth hole at Pinehurst #9. It's in your head from the first tee. It weighs on you while playing it. On the way to the fifth, it's relief/optimism.