Well I gave this some thought.
McCartney first solo album only one song comes to mind: Man We Was Lonely
Ram has a couple good ones: Smile Away and Eat At Home
Wings Wildlife, I would be tempted to say Mumbo just because I like that bumpy start and the "Take it, Tony!" part. Other than that there's no other that rises to the occasion.
Red Rose Speedway, My Love, with that long sustained note of violins, which is pretty cool, but I'm not sure it measures up. (On a related note, when I had this rinky dink little keyboard sampler many moons ago, I sampled that beginning sustained note of violins, and when I played it back and looped it, it sounded almost exactly like a Hammond B3 organ. But the trick was to begin recording not at the very beginning because the volume increases slowly and you don't want a variation so it had to be like the last half of that). Moving on I think maybe Get On the Right Thing has a good intro. I regret to say that none of the medley at the end really has a good intro that measures up to this thread -- with one exception maybe, and that would be the intro of the bridges the end of Lazy Dynamite with Hands of Love. Even though it's very brief, I just love those guitar chords, the sound of them, the punch, and the rhythm. Of course all the intros in my opinion of Paul's songs that I like we're good. But we're talking about an intro that is special and not merely good.
Band on the Run, the title track even though other people like it I'd say no, not quite. Jet I think just barely qualifies. And Let Me Roll It is one of those that's almost there but not quite. The only one that I'm enthusiastically endorsing is Helen Wheels.
Venus and Mars, now here we have a special case, where a song itself constitutes the great intro and I speak of course of the title track Venus and Mars and the way it segueways into Rock Show. I also have to nominate Listen To What the Man Said.
I'm trying to think of what other later songs from the list of albums I don't really like much, although here and there there are a few gems lying quietly among them. So just randomly as it comes to mind, I'll be out of order chronologically: I would nominate That Was Me, Dance Tonight, and Take It Away.
My interpretation of the meaning of a great intro for this thread, is that it's not just a good snatch of music, and it's not just a memorable part of a song we all love, it has to rise above that to be a superlative way of introducing the song. There are many fine songs out there that don't have a special intro, but that's all right, we don't fault them for that, it's just extra icing on the cake if it can be accomplished.