Chaconne did an excellent job about defining the measures formulas and how they are very closely related from a math standpoint. If you benchmark or rank models using the same data set those two measures are interchangeable, meaning you will get the exact same ranking of your models whether you use R Square (ranking them high to low) or the RMSE (ranking them low to high).
However, the two measures have a very different meaning and use. R Square is not only a measure of Goodness-of-fit, it is also a measure of how much the model (the set of independent variables you selected) explain the behavior (or the variance) of your dependent variable. So, if your model has an R Square of 0.60, it explains 60% of the behavior of your dependent variable. Now, if you use the Adjusted R Square that essentially penalizes the R Square for the number of variables you use you get a pretty good idea when you should stop adding variables to your model (and eventually just get a model that is overfit). If your Adjusted R Square is 0.60. And, when you add an extra variable it just increases to 0.61. It is probably not worth it adding this extra variable.
Now, turning to RMSE also most commonly referred to as a Standard Error. It has a completely different use than R Square. The Standard Error allows you to build Confidence Intervals around your regression estimate assuming whatever Confidence Level you are interested in (typically 99%, 95%, or 90%). Indeed, the Standard Error is the equivalent of a Z value. So, if you want to build a 95% CI around your regression trendline you multiply the Standard Error by 1.96 and quickly generates a high and low estimate as border of your 95% CI around the regression line.
So, both R Square (and Adjusted R Square) and the Standard Error are extremely useful in assessing the statistical robustness of a model. And, as indicated they have completely different practical application. One measures the explanatory power of the model. The other one allows you to build Confidence Intervals. Both, very useful but different stuff.
Regarding assessing prediction accuracy on data you have not seen, both measures have their limitations as well as most other measures you can think off. On new data that is out-of-sample, the R Square and Standard Error on the history or learning sample of the model will not be of much use. The out-of-sample stuff is just a great test to check whether your model is overfit (great R Square and low Standard Error, but poor performance in out-of-sample) or not. I understand better measures for prospective data (data you have not seen yet) are the information criterion including AIC, BIC, SIC. And, the model with the best information criterion values should handle unseen data better, in other words be more predictive. Those measures are close cousins of the Adjusted R Square concept. However, they are more punitive on adding additional variables than Adjusted R Square is.