I think a long term view is it’s the basis for building heavy industry in space as it has a lot of natural resources that can be exploited industrially and escape orbit velocities are much less from the moon than earth surface. This eventually leads to a general space infrastructure. If you believe the end of humanity is on earth then this probably isn’t convincing. Folks like myself believe we are inexorably driven to spread life as a function of what life is and we have no meaningful choice but to keep going.
But as long as some subset of humanity believes in this humanity will keep investing in it. Not everyone has to be aligned and we can have many priorities at once, not the least of which is robotic science which I only see as mutually exclusive as long as there’s not plentiful private investment, which there is at the moment. I don’t see robotic exploration as suffering in the build out of extremely low cost launch capability and a general space infrastructure including moon infrastructure. I see it benefiting enormously as the costs and risks drop significantly.
In theory it's possible to make a carbon-neutral methane rocket based on atmospheric CO2, though that depends on how completely the methane can be burned.
There are a million viable (and often quite fun) answers here, but one is really kind of funny. What do you get when you mix oxygen and hydrogen? Water? No, of course not! You get rocket fuel! Seriously. Liquid oxygen + liquid hydrogen is a common, and highly effective, fuel that's been used for various engines such as on the Space Shuttle Main Engine.
Rockets can also be carbon negative in another way. A rocket that uses less than 50% of its fuel getting to orbit would be carbon negative, because it's spending less than 'x/2' fuel to go burn at least 'x/2' fuel away from Earth. Factor in some of the fuel coming from carbon neutral sources, and it quickly becomes quite easy for a rocket to be carbon negative.
The flow of mass would be the other way around. Lunar regolith has every element embedded in it and there’s little need to bring anything but bootstrapping -to- the moon. Return to earth from the moon wouldn’t be CO2 producing anywhere but the moon.
But as long as some subset of humanity believes in this humanity will keep investing in it. Not everyone has to be aligned and we can have many priorities at once, not the least of which is robotic science which I only see as mutually exclusive as long as there’s not plentiful private investment, which there is at the moment. I don’t see robotic exploration as suffering in the build out of extremely low cost launch capability and a general space infrastructure including moon infrastructure. I see it benefiting enormously as the costs and risks drop significantly.