A few more links and then I’ll shut up :).
Sarabande, you wrote
One of the things I really like in our parish is that, although we do use our hymnals, if there is text that really is totally appropriate for the day, but the hymn melody isn’t good or not familiar, the text will be printed in the weekly sheet and set to a hymn melody that the congregation knows.
Have you heard about Hymn Introits for the Liturgical Year? Someone adapted the introits to hymn settings with familiar tunes and published them. The edition from the link above comes with the history and background of the introit but you can get the settings by themselves from WLP here:
Here are more than 100 hymns based on the texts the Church wants us to sing during the Entrance Rite of the Mass, set to tunes your congregation already knows! The hymns are based on introit antiphons and psalms from the Roman Gradual for the Sundays and solemnities of the liturgical year. The beautiful, poetic hymn verses include psalm paraphrases from the finest English-language metrical psalters and new psalm paraphrase texts by Tietze. All are set in metrical form to familiar hymn tunes that have been sung during the last 40 years. Every hymn is presented in harmony, ideal for congregations or choirs who enjoy hymn singing in parts.
Another resource is the American Gradual, a translation of the Roman Gradual to English. You can get it here and though it’s expensive it’s gotten good feeback and it has really generous copyright provisions. There’s a free pdf file online (found here ) but that online version doesn’t cover the entire year yet, it only goes up to Pentecost.