My Aunt had a two-door 1990 Olds Calais that was bought brand new for around $10,000. When she gave it away about 3 years ago it only had 36,000 miles on it - I know because I was there when it was purchased and I used to drive it when I came home to visit. In fact, I put a LOT of those miles on the car driving around MA visiting friends.
Anyway, if this was your grandmother's car (or you otherwise know the car's history) the 40,000 miles on the odometer could be real - in which case I would be VERY skeptical that the transmission is history. And so I would surely make the necessary repairs and keep the car. But like Shorod said - I'd make sure you didn't just blow out a line, or have a leaking cooler (possibly from a rock). The fluid probably landed on the hot engine and started smoking under the hood. As long as you didn't continue to drive the vehicle for very much longer, your tranny might still be OK.
Keep an eye on that cooling system. These cars are notorious for rust and sludge build-up and need to be flushed a couple of times per year. The fact that your freeze plugs have already rusted out is a good indication that the flushes weren't being performed properly (or often enough). There is a plug on the block where the transmission attaches. If THIS plug rusts out it'll cost you $5 for the plug and $500 in labor. So if you DO end up replacing the tranny, I would have this plug replaced at the same time.
But I would bet that tranny is still good and I'd be doing EVERYTHING POSSIBLE to prove that.