Is there any point in playing red Opportunity cards?

While most of these cards are dream cards that cause nightmares, even nightmares can be... educational if you manage to follow them all the way to the end of the dream. In general you will find them pretty annoying until you have some way of reliably removing nightmares, then you can make better use of them.

The red border means that they are autofiring - they use up an action simply by viewing them. Many of these are nightmare cards, as you have discovered, but there are some that aren't nightmares.

Seeing as Nightmares are generally detrimental to your overall experience (and can lead to insanity), you might want to avoid the red cards, as there are only a few that aren't nightmare related.

However, if you don't mind "cheating" - there are various wikis that list cards and their effects.

Almost all red-bordered cards in London proper are Dreams, which give you a small amount of progress in a dream-related quality. (Dreams are almost, but not quite, worthless.) There are two red-bordered cards that aren't Dreams, and they will show up very early in the game. (As far as I know, they'll only show up once, or they only show up to early players; I discarded the "Giggling Mandrake" card the first time I saw it, and never saw that card again.)

There is one major advantage to red-bordered cards: if you click on one when you don't have any actions available, they don't cost an action to use. Since the Places of Menace are usually chock-full of red-bordered cards, this will greatly speed your escape from them. (Alas, the Tomb-Colonies were redesigned lately to use white-bordered cards instead of red-bordered cards, so it now takes about 50% more actions to escape.)

You're asking about nightmares, not red border cards in general. In the State of Some Confusion, the Mirror Marches, the River, New Newgate and the Tomb Colonies they're absolutely necessary for escape.

Furthermore, the dreams themselves aren't useless. All Nightmare cards build up change-points in the dream they represent (there are a few exceptions, mind; one of the cards in the Burial of the Dead line just raises your Nightmares, and, worse, cannot be discarded). Some even reduce your Nightmare level. If you wait to use them until you've used up all your Actions, you can use them free of cost--this has been confirmed as an intentional feature, not a bug.

I haven't yet reached the end of any of the Nightmares, but if you ask around I'm sure you'll be able to find someone who has. I'm almost certain that Stormy-eyed, one of the more common Qualities needed to access some stories, is related to the ending of What the Thunder Said.