TL;DR: Jesus was not disappointed and St. John the Baptist did not have doubt. Some Church Fathers interpreted the passage to mean that either
John the Baptist was sending the disciples for their sake (so v.6 was directed at the disciples), or
the reference "the one who will come" meant John was asking "whether Jesus also would come into the underworld so that John could proclaim him as the forerunner there also after his death." (see Hermeneia commentary below and catena aurea on Matt 11).
The rest of this answer is based on Volume 2 of the Hermeneia commentary on the Gospel of Matthew, which is a commentary emphasizing technical exegesis and history of interpretations rather than theological, so I believe it can apply for the Catholic point of view as well, as it covers the view of the Church Fathers.
Verse 11:6 is treated under "The Baptist's Inquiry (11:2-6). I'm skipping the Analysis portion (Origin, Redaction, Source) which discusses the authenticity of the pericope text before final redaction and the verse by verse Interpretation.
History of Interpretation (verse 3)
Why does the Baptist, who—for both the church and the evangelist!—had proclaimed Jesus as the coming one, suddenly become a doubter? The question angers Luther. “Most of what I can find written about this gospel deals with the question of whether St. John did not know that Jesus is the rightful Christ; but that is an unnecessary question, and it is of no great consequence.”26 In the ancient church Tertullian, among others, dared to say that John had doubted Jesus’ messiahship27, but he encountered unanimous and indignant protest. The usual answer was that John had asked Jesus not for his own sake but for that of his disciples.28 Beginning with Origen a number of interpreters have understood “the one who will come” with a future meaning and have interpreted John’s question just before his own death as asking whether Jesus also would come into the underworld so that John could proclaim him as the forerunner there also after his death.29 Since the Enlightenment, Protestant exegesis has again taken seriously the possibility that John genuinely doubted. Depending on one’s theological position, the interpretations have gone in different directions. Some found it to be understandable that even brave men would experience all kinds of doubts and anxieties in a prison cell.30 Another was confident that doubt is part of a prophet’s “religious condition.”31 Widespread is the thesis of the Baptist’s messianic impatience whose doubts came simply from the fact that Jesus delayed his messianic revelation too long.32 It was said that such impatience would also be understandable in a prison cell! At the same time, it was said, there are obvious pedagogical reasons why Jesus delayed. He wanted first to win the hearts of the people.33 Knabenbauer announces, not without pride, that in his time “practically all” Protestants believed that John doubted Jesus’ messiahship, while the Catholics attempted to remove from him any shadow of doubt.34
Joseph Knabenbauer, Commentarius in Evangelium secundum Matthaeum, 2 vols. (Cursus scripturae sacrae 3/1–2; 3d ed.; Paris: Lithielleeux, 1922).
29 Since Origen Hom. in 1 Reg. 28.3–25 = GCS Origenes 4.290.30–32. In the West this interpretation was spread through Jerome (77–78); idem Ep. 121.1 ad Algasiam = CSEL 56.5 on the basis of the translation in the Vulgate “qui venturus es” (future). Cf. Daniel Sheerin, “St. John the Baptist in the Lower World,” 1–22, 7–17; later material in Simonetti, “Praecursor” 372–82.
Manlio Simonetti, “Praecursor ad inferos: Una nota sull’ interpretazione patristica di Matteo 11, 3, ” Augustinianum 20 (1980) 367–82.
3 There is no indication that Matthew would have seen John’s doubt as a problem. However, since in his entire gospel he strongly Christianizes the Baptist,36 precisely in his gospel John’s question is somewhat strange. Do we have here simply traditional material passed along unthinkingly? Or is the basic aspect of John’s question important for Matthew? Are people to approach Jesus as openly as John (and his disciples!) in order to give Jesus an occasion for talking about his activity? In that case the skeptical crowds are compared with John’s disciples after they have left (vv. 7–19*). Then John and his disciples would be important not so much as individual figures but as representatives of that part of Israel that was favorable toward Jesus. However, none of that is stated explicitly.
6 Only in the concluding macarism of v. 6* is the person of Jesus mentioned explicitly. Σκανδαλίζω, a late Jewish and Christian word, means “to set a trap,” “to erect an obstacle,” then more generally “to give offense,” “to lead to ruin,” “to seduce to sin.” Ἑν designates the person or thing through which the offense comes. In Matthew (and Mark), the word is used of the final abandoning of Jesus in the passion (26:31*, 33*) and in the end-time (24:10*). Our text looks ahead to these texts and to 13:57*; 15:12*. The general formulation in the third person shows that more is involved than a warning to John’s disciples.47 Instead, here at the conclusion is the christological-parenetic point of our text that is fundamental for the evangelist. He is concerned not that one should have the right knowledge about Jesus but that one not reject the experiences of salvation to which Jesus extends an invitation. These experiences of salvation make a claim; they require a decision for or against Jesus. It is for just this reason that the evangelist, after Jesus’ healing ministry in Israel (chaps. 8–9), had Jesus give the disciples the task of confronting Israel with a decision (chap. 10)
Summary [for 11:2-6]
First of all, the meaning of our text must be determined on the basis of Matthew’s entire story. After the programmatic proclamation and the miracles of Israel’s Messiah, Israel must respond to the question of who Jesus truly is. It can respond not by formulating a correct messianic answer but only by actually becoming part of the story of Jesus and by letting the story bring it to a life decision about Jesus. Our text is a model of how Matthew takes christological titles and concepts into his story of Jesus and subordinates them to it. It is a model of what is called narrative Christology and of how it lays a claim on people. Thus Matthew uses the Baptist and his disciples to show the way of understanding “on which the disciples also had to go and on which the people should go.”49 What is important is that one become part of the story, the deeds of the Christ. No “abstract” christological answer can replace this participation in the story of the Christ. The Baptist and his disciples demonstrate positively the possibility of salvation for Israel.50 The continuation of the story will then clarify, in the form of prophetic warnings whose central figure is once again John the Baptist, the negative possibility—Israel’s no.