Prince Andrew was left "tearful" in a meeting where King Charles informed him that he would never return to royal duties.