Set 302 to win from 46 overs at Taunton, Middlesex needed eight from the last and Simpson lofted Jim Allenby over long leg to finish 79 not out.
Nick Gubbins contributed 76 and James Fuller smashed 36 from only 18 balls.
Somerset, 348-6 overnight, earlier declared on 446-9 to give Middlesex an invitation they were happy to accept.
The visitors, who began the game with a one-point lead at the top of County Championship Division One, extended it to 14 points because of Warwickshire's draw with Hampshire and Lancashire's week off.
The catalyst for victory came from Fuller, who took three wickets in the morning session then provided acceleration to the run chase.
With 109 needed from nine overs, Fuller, who had survived a loud appeal for caught behind off Tim Groenewald, hit the same bowler for successive sixes over extra cover in an over that cost 22.
His onslaught jolted Simpson into life, with the wicketkeeper taking up the chase after Fuller and Ollie Rayner fell in the same Jack Leach over.
Allenby was given the responsibility of the final over and with six needed from four balls, Simpson swept a leg-stump delivery over the longest boundary on the ground.
It completed a remarkable chase, one that Middlesex looked to have abandoned after Sam Robson, Dawid Malan, Gubbins and George Bailey fell in quick succession, all four to Groenewald, who finished with 5-90.
A sedate passage with Simpson and James Franklin at the crease followed, but Fuller arrived to make things happen, just as he had with the ball in the morning session.
He took all three wickets to fall before lunch, including Peter Trego, who made 138, for figures of 3-125.
Middlesex were held up by last-wicket pair Leach and Groenewald until midway through the afternoon, when Somerset captain Chris Rogers declared.
It led to a thrilling climax, with all four results possible until Simpson, on his 28th birthday, struck the decisive blow.
Middlesex's John Simpson hit a six with two balls remaining to seal a thrilling two-wicket win over Somerset in County Championship Division One.