I would love to just reap the karma for acting like I wrote this, but instead here's a good, short article on the subject.

It doesn't name many names, but the one I found interesting was former President Eisenhower - who objected on grounds of it being too great a tax burden. And the politicians who did object did so mostly based on a common believe in the scientific community that the Apollo program was "inefficient" - i.e. Apollo was too expensive given the scientific progress expected. They proposed that we could send probes for a fraction of the expense, or achieve far more missions with the budget dedicated to a manned landing. The article notes that congressional approval was approx 6 to 1 in favor. One such example was former GOP Presidential Candidate Barry Goldwater, who said in the June 1964 Popular Mechanics:

[the Kennedy Administration has...] “retarded our own military development in near and outer space, while the enemy's development moves on.” [Goldwater proposed] “a re-planning of the present space program to provide for a more orderly, yet aggressively pursued, step-by-step development, remaining alert to the danger of over-diversion of skilled personnel in critical shortage from other vital areas such as health, industry, education and science.” AND “We are spending entirely too much money on the manned moon program, when a carefully plotted program using unmanned lunar landing equipment could steadily build up a solid body of scientific knowledge…. All manned space research should be directed by the military, with national security and control of the access to space as primary goals….”

Also, here's an old reddit post asking if there were objections on religious grounds - and there certainly were. And here's another fun article about how opposition has mostly been hushed-up since the program was so popular and successful. Interestingly, it seems many Civil Rights leaders objected based on the expense of the program - they believed funds were far more desperately needed to support the struggling African American community still battling the end of the Jim Crow era.

As a side note, were there any public figures who opposed going to the moon specifically on religious grounds?

While not a politician and not having as much world influence as they have now, the head of the Mormon church said that we can't and should go to the moon. Brigham Young prophesied that there was a race of little men living on the moon.

What about the opportunity cost? Meaning, what other programs had to be given up in order to finance the moon landing? Politicians love to point out what is seen, the supposed benefits of a big government program. Economists point to the unseen, what has not been achieved because the money was spent on that program. Who are the losers? What other good could have been done? Who paid the bill? Every Dollar can only be spent once.

There's a program on CBC (Canadian Broadcasting Corporation) radio called Under the Influence that talks about advertising. They recently did a two-part show on how Kennedy advertised and marketed going to the Moon. There are write-ups on the CBC radio website, and the second episode also has the audio online (the first one has audio available for purchase on iTunes). Personally I thought it was very interesting but don't feel confident enough in my memory of it to summarize for you!

First episode writeup

Second episode writeup (with audio)