Many years ago (~32 years) I read a short story about aliens trying to contact humans to warn them of the Earth's impending destruction, but they could only psychically link with a guy who stumbled out of a bar, drunk. They told him what was going to happen so he could warn Earth's leaders. He woke up the next day and thought he imagined the whole thing because he was drunk and there was no day after that.
Arthur C. Clarke "No Morning After", included in The Other Side of the Sky. It concludes:
"So much for that hallucination," thought Bill. "I was getting tired of it, anyway. Let's see what the next one's like." As it happened, there wasn't a next one, for five seconds later he passed out cold, just as he was setting the combination of the file cabinet. The next two days were rather vague and bloodshot, and he forgot all about the interview. On the third day something was nagging at the back of his mind: he might have remembered if Brenda hadn't turned up again and kept him busy being forgiving. And there wasn't a fourth day, of course.
"No Morning After" (1954) by Arthur C. Clarke
This is the first of Clarke's comedies since his earliest stories that's actually pretty funny. A benevolent telepathic race finally succeed in making contact with an earthling to warn us of our planet's impending destruction (yet again), but the suicidal drunk doesn't turn out to be our greatest ambassador.
Found with the Google query:
short story earth destruction alien "drunk"