It is not your job to motivate your son. You have tried. The motivation has to come from him. You dont have to break your brain trying to find just the right motivational words that will make him see the light. And its not your job to keep his spirits up. He has to do that and it will happen when/if he does better.
Love him, as we all do, but realize you can not do anything to make him want or feel better. Take that pressure off of yourself. This is his life, his path, for better or for worse. He will chose if he wants to change. No words you say can do those things for him.
It is hard not to worry about them or to let go of their horrible choices, but we are powerless over their lives by adulthood.
Have you ever gone to al anon or do you see a personal psychologist to help you cope? Either resource can save you. And you can only save one person...you. And you are worth it.
Hang in there. Enjoy your vacation and take many!!