"Here come the waterworks!" means "this person's about to cry again!" Melissa's answered the other part of the question - it does sound wrong if you use a pronoun at the end of the phrase, instead of a real name or object.

"Here he comes" is correct. "Here comes he" sounds weird but you can you that when you mention the name of someone. Eg. "Here comes Tracy" but not "Here Tracy comes".