Kennedy was looking to either cancel Apollo (due to skyrocketing costs), or try to get USSR onboard for it (which probably would have caused it to stall). In addition to that, Apollo was extremely unpopular for the entirety of its existence, save for a brief period in the middle of 1969.
The space program's history is very different from what most people imagine it to have been.
I don't think this is a fair characterization of the situation, but my knowledge is mostly based on one book, Reaching for the Moon, a Short History of the Space Race[0], that I recently read.
You're right that Kennedy was ambivalent about it, and that working with the USSR was floated, but ultimately he did decide to go ahead with it. And there were people opposed, but "extremely unpopular" doesn't sound right. Like, I think the majority of the population was broadly in favor.
The space program's history is very different from what most people imagine it to have been.