Objection 1. It would seem that bodily health is not an effect of this sacrament. For every sacrament is a spiritual remedy. Now a spiritual remedy is ordained to spiritual health, just as a bodily remedy is ordained to health of the body. Therefore bodily health is not an effect of this sacrament.
Objection 2. Further, the sacraments always produce their effect in those who approach them in the proper dispositions. Now sometimes the recipient of this sacrament does not receive bodily health, no matter how devoutly he receives it. Therefore bodily health is not its effect.
Objection 3. Further, the efficacy of this sacrament is notified to us in the fifth chapter of James. Now healing is ascribed there as the effect, not of the anointing, but of the prayer, for he says: "The prayer of faith shall save the sick man." Therefore bodily healing is not an effect of this sacrament.
On the contrary, The operation of the Church is more efficacious since Christ's Passion than before. Now, before the Passion, those whom the apostles anointed with oil were healed (Mark 6:13). Therefore unction has its effect now in healing bodies.
Further, the sacraments produce their effect by signifying it. Now Baptism signifies and effects a spiritual washing, through the bodily washing in which it consists outwardly. Therefore Extreme Unction signifies and causes a spiritual healing through the bodily healing which it effects externally.
I answer that, Just as Baptism causes a spiritual cleansing from spiritual stains by means of a bodily washing, so this sacrament causes an inward healing by means of an outward sacramental healing: and even as the baptismal washing has the effect of a bodily washing, since it effects even a bodily cleansing, so too, Extreme Unction has the effect of a bodily remedy, namely a healing of the body. But there is a difference, for as much as the bodily washing causes a bodily cleansing by a natural property of the bodily element, and consequently always causes it, whereas Extreme Unction causes a bodily healing, not by a natural property of the matter, but by the Divine power which works reasonably. And since reasonable working never produces a secondary effect, except in so far as it is required for the principal effect, it follows that a bodily healing does not always ensue from this sacrament, but only when it is requisite for the spiritual healing: and then it produces it always, provided there be no obstacle on the part of the recipient.
Reply to Objection 1. This objection proves that bodily health is not the principal effect of this sacrament: and this is true. The Reply to the Second Objection is clear from what has been said above (cf. 29, 8).
Reply to Objection 3. This prayer is the form of this sacrament as stated above (29, 8,9). Hence, so far as its form is concerned, this sacrament derives from it its efficacy in healing the body.
Source: Summa Theologica (New Advent)