I'll speak from a very traditional, old school view on the subject. Thus it may differ slightly from the response other Catholics in here give.
"Sex", meaning conjugal relations, are seen as neither being excutiatingly beautiful, or inherently wicked in Catholicism. They are what they are; something motivated by a natural instinct in the flesh, for the obvious purpose of procreation.
That much is obvious; that they are INTENDED for procreation. This is not to say that there are not secondary effects and purposes attached to such relations, for example the strengthening of a couple's affection for one another. However even this end, is actually in support of the primary end; the strengthening of a couple's bond, for the rearing of children, which are procreated via sexual relations.
Does this mean that any sexual union between wedded spouses that does not result in conception is sinful? Of course not; whether one conceives or not is something totally not up to the spouses themselves. What IS immoral, is to do anything to hamper such reproduction, whether it be in the form of contraceptive devices, medications, self mutilating surgeries, etc.
The same would be true then in the case of a married couple who are now beyond child bearing years (or should I say a woman in such couple who is beyond child bearing years; technically a man can sire children until his dying breath, so long as he's not become impotent). That a woman has become infertile is not something that is in her power to change; and since the God given appetite for physical relations still exists, as well as the wedded relationship itself between the spouses, intimate relations are still moral. However it is no coincidence that as the couple ages and child bearing physically becomes impossible, sexual desire in both parties TENDS to decline (and in a few cases, disappear entirely.)
There is however another reality to contend with; and that would be concupicence. This term refers to the out of control carnality we all contend with. It is that unthinking recklessness, which makes a man a glutton, or makes him a lust enslaved person. Catholics believe that as man was created, he is a rational soul, and was given a physical body appropriate to that soul. He was also given the grace, the participation in the ordering and sanctifying life with God, to have full control over that body. The body was very much the servant of the spirit, not vice versa.
However when man defied the divine order (disobeying God), his entire world went out of order. This included his relationship with his body. The body was no longer continually maintained by God (thus it would become feeble and eventually die), and man lost the fullness of God's help to control his passions.
This is why man the rational creature, so often struggles with himself...a fight between that which would gratify him, and that which is noble and good. It's a contradiction, between being a naturally "civilizing animal", yet so prone to selfishness and destruction, which I think nothing save the Christian philosophy can explain.
We are fallen creatures...and concupicence is a "cross" we all commonly bear.
That said, issues of sexuality become very complicated and controverted. For example, people questioning God's ability to provide, will say "but if I have children left right and centre, we will be destitude". Or if people REALLY feel it's prudent to avoid having children (save a cross is sent their way in the form of some ailment by which it would not be currently good for a woman to bear children for a time), actually abstaining from marital relations (which would seem to be the logical choice) becomes almost an obscenity to them. This shows how not in control we are, by default, of our passions. This is the fruit of original sin everyone deals with.
This is why Catholics are given helps, why we believe in penances, praying, and fasting, or the mortification of the senses. These are all helps (above all, beseeching God's grace), to keep the passions (which come in many forms, not just sexual) under check. Otherwise you will become a slave to your own flesh, aside from one who also listens to the temptations of the devil (who is another figure we must contend with).
As a mild condescension to those who are weak and struggling, yet without throwing aside the moral principles and natural law, the Church does allow "natural family planning". Basically it involves holding off on marital relations at certain times during a woman's monthly cycle (so there is still some self control involved here), and only having intimacies when the wife is at her least fertile period. This of course doesn't mean a child could not be conceived; but at the very least the couple is not doing anything with the marital act itself to aggrivate conception. They are showing a respect for God's order. It is not the most perfect solution, but with proper consultation with one's confessor, it may be deemed the better one for some people. However it becomes sinful if used in a manner to totally frustrate reproduction, even when the spouses have adequate means to support children and there are no health issues involved. In other words, if you are simply selfish and cannot be bothered with lots of children, it's not a way out; it would become a betrayal of marriage.
For the very purpose of marriage, is the reproduction and fostering of children. While modern culture has taken romance (which they've narrowed down to only refer to amorous relations between men and women...which is incorrect, as it refers to something much broader than this) and made it the only point to marriage, this is very wrong and places our own whims above the obvious order that God has established.
In the context of marriage, respecting the natural order, physical relations between a husband and wife are not in the least bit shameful or sinful. They in fact are the consumation of their vows to one another, and are at least the first step in fulfilling the very purpose of marriage (the birthing and rearing of children.)
In short, the Catholic view of sexuality is noble, principled, and non-puritanical. It neither makes the error of prescribing free love, nor does it make a loving, physical married relationship seem filthy. "The marriage bed is undefiled" taught St.Paul, and we being his spiritual children, agree of course.
What I think needs to be answered in the realm of things Christian, is not the Catholic Church's reasons for it's principled stand, but what excuse non-Catholic groups (and this means all of them; Eastern Orthodox, Protestant, etc.) have for allowing contraception to their adherants. The allowance of such things makes any claims to moral superiority to the world seem hollow, and only a form of bigotry. Allow me to explain...
While it is a part of the legitimate Christian tradition that homosexual relations are sinful, why is this? If our answer is only "because God says so", it appears that we, and our God, are just bigoted and hung up on aesthetics. However the Catholic Church does not speak of God being so petty; rather God has an order and a purpose, and this lies at the heart of why there is even such an animal as "morality." Homosexual acts are considered sinful because they are disordered. But then again, so are contracepted sexual acts, or any sexual act not open to causing conception. Thus it's rather hypocritical for a Protestant Minister to have his wife on a regimen of contraceptive pills, yet claim from his pulpit that homosexuals are grievous sinners, who shirk God's natural order.
Basically, the untraditional position which MODERN Protestantism has taken on contraception (all Protestants, in fact most secular people, were agreed with the Catholic Church on this matter until about the 1930's), guts any sense that their Christian ethics are based on something rational. Rather it makes it all seem arbitrary...and given that we live in a chaotic world in which so much has the appearance of being arbitrary to begin with, why trade this "arbitrary" existance for the "because we say so" of the Christians?