The Reds led 26-7 early in the second half of Saturday's match in Auckland, but Moana piled on the next three tries to take the lead as a boilover loomed.
Down by five points and with the clock ticking over to the 79th minute, the Reds made the most of a scrum by swinging the ball wide before cutting it back inside to fullback Campbell for the levelling try.
Ben Volavola made no mistake with the conversion from straight in front, with the Reds holding firm for victory.
"We're really chuffed about how we held our nerve and got the win," Reds coach Les Kiss said.
"But we've got to look at some things to make sure we keep improving.
"I believe in these boys deeply. I believe there's a real core strength of the group that comes up when it matters, and we've just got to make it more consistent throughout the game."
The NSW Waratahs (27 points) and the Western Force (22 points) still have a mathematical chance of pipping the Reds (32 points) for sixth spot.
But the Waratahs would need to score a bonus-point win over the Force in Perth next Saturday night and rely on Queensland losing to the Fijian Drua in Brisbane.
The Force would need to win their final two games with a bonus point in each, plus hope the Reds lose to the Drua.
The Reds (7-6) can finish as high as fourth, but they will need the Crusaders and Brumbies to slip up next week.
Moana have now slumped to 12 straight losses, with their only win this season coming in round one when they defeated Fijian Drua.
On paper, Saturday's match shaped as an easy win for the Reds.
But given it was Moana's last home game if plans to disband them at the end of the season go ahead, Queensland were on red alert for a tough outing.
"Their spirit came out didn't it in the way they played. Direct, skilful. Their tackles were monstrous at times," Kiss said.
"It certainly had you worried for sure. But we found a way."
Moana performed an emotion-charged Tau Moana before the match, with the haka-style performance getting the juices flowing from the outset.
Reds players barely had time to settle before Moana scrumhalf Augustine Pulu crossed for the opening try inside 90 seconds.
The Reds hit back in the 11th minute when Campbell initiated a break and then combined for a series of passes with Tim Ryan to send the winger over the line.
Josh Canham's pure determination led to a second try in the 22nd minute, with the Reds lock barging over in a tackle.
Treyvon Pritchard's 45th-minute try gave the Reds a 26-7 advantage.
But when Semisi Tupou Ta'eiloa hit back four minutes later and then Moana were gifted a penalty try in the 55th minute, the margin was back to just five points, and with Reds flanker Joe Brial in the bin.
Moana hit the lead in the 58th minute when Israel Leota broke through three tackles on the wing to touch down for a dazzling try.
But the home side were left heartbroken when Campbell found a gap to produce the match-winning moment.
