It's much more complicated than Vietnam War destroy our space exploration dreams. A lot of other problems show up during space race for their time.

- First, space is unhabitable place to live. There is no air, no water, no magnetic field protecting electronics against solar flares, no ozone layer to protect you against UV radiation... Add to mix various medical problems which astronauts have, short list:

Several consistent medical problems have been encountered by astronauts during space flights. These include vestibular dysfunction, weight loss, increase in height, upward fluid shift, anemia, cardiovascular deconditioning, muscle atrophy, and bone loss. Almost all of these alterations can be attributed to the absence of gravitational force. Most are adaptive in nature and therefore reversible, but readaptation after returning to earth may cause further problems (e.g., in the case of vestibular dysfunction). The most recalcitrant and disturbing of all these problems is the relentless bone loss associated with negative calcium balance. This problem appears to be irreversible, and critical demineralization can occur after two years in a weightless state. Unless its mechanism is elucidated and preventive measures are taken, the bone loss may prove to be the medically limiting factor for the duration of space flight.

Source:
https://www.ncbi.nlm.nih.gov/pubmed/3490786

NASA also have "Human Research Program aimed to better understanding how human body and mind act in space, which as additional problems mention mental health due isolation and not working "natural clock" of human body, spread of infectious illnesses in cofined spaces and stress, 10 times larger radiation doses and many more.

Source:
https://www.nasa.gov/hrp/bodyinspace

- Second, why send human anyway?

Human space mission cost a lot. How much? USA spend on Mercury, Gemini and Apollo programs around 25 billion USD (equivalent to today ~110 billion USD) just to send around ~50 people. Sending something to space cost around 10 000 USD per pound. While gather data and technical progress gained by these missions are invaluable, it's still a crazy money to spent for this. Since '60s Soviet space program and NASA switch from human oriented space missions to sending space probes as simplier, safer and more cost efficient way to gather scientific data.

- What we aim for? Or rather what we don't.

NASA after Apollo planned other ambitious missions as part of Apollo Applications Program which among them was AES (Apollo Extension Series) which had to establish land shelters for astronauts on Moon surface, Venus fly-by and Skylab space station by using what left from costful Apollo program. Problems easily show up, Saturn V was very complicated and costful for such operations like establishing lunar base, Venus fly-by hits technical problems as missions had to take fourteen months and only Skylab missions was done. Finally budget cuts done by Lyndon Johnson administration since 1968 as his "Greater Society" idea and Vietnam War become an visible strain on federal spending and his advisors propose to cut NASA budget. NASA also hit a wall of social pressure, which lost interest with such ambitious programs and want cuts most flashy spending as NASA to improve situation at home. Even some influential politicians as Ted Kennedy call for cutting NASA funding New York Times write short opinion about how NASA lost public support.

In the sense that the Vietnam War resulted in both a massive expense that ultimately resulted in a lot of program cutting, as well as a loss of public enthusiasm for the Space Race, Clarke's judgment is not very wrong. While the space program was never exceptionally popular among the US public (relative to other possible uses of the expenditures), by the time of the Apollo landing there were significant numbers of people who asked what the point of it was. Why put "some clown on the Moon," as satirist Tom Lehrer jokingly referred to it? Why not, say, improve the inner cities of the USA? (LBJ's Great Society anti-poverty program was in part scuttled because of the rising costs of the war.) Why not worry about the many terrestrial problems? And isn't it all just Cold War posturing?

It's impossible to know what would be different about the American political and technical experience if Vietnam had been avoided. But the Vietnam War was both a real budgetary drain as well as an important enthusiasm drain. The distance between the exultations of science and technology in the early 1960s and the grim suspicion of it by the 1970s is a serious one. Vietnam broke the "liberal consensus" that saw things like Cold War space competition as an important good — and replaced it with both a weariness and a cynicism. (One which the Reagan administration attempted to overturn in the early 1980s, and interestingly with its own, weaponized approach to space.) My own personal counterfactual historical dream (inspired by a trip to Japan) is, "what if instead of a war in Vietnam, we had invested in Japanese-style high-speed rail along the eastern corridor?" (The costs are comparable, as was the time of their inception.) The point in such a comment is just to acknowledge that societies make choices and even as rich a country as the US is, it is not infinitely rich — either in resources or policy will.

There are of course other reasons why the space program might have been "tapped out" for its time, though these may be unimaginative. One doesn't have to imagine that Apollo-level budgets were sustainable, but the drop in NASA's budget after the 1960s was a real one and it has never really gotten close to those original numbers (in either raw dollars or as a percentage of the federal budget).

Anyway. There's much one could unpack here. On the general collapse of the "liberal consensus" of the 1960s and the role of Vietnam in that, I'm fond of Perlstein's Nixonland — it's a fun (if depressing) read. On public attitudes towards NASA, see the work of Roger Launius, among other works that talk about this. On the changing attitudes towards science and technology in the 1970s, see esp. Audra Wolfe, Competing with the Soviets: Science, Technology, and the State in Cold War America, and Stuart Leslie, The Cold War and American Science.