Fairly typical etiquette among those who tip housekeeping is usually to tip every day the service is provided. It may be done by different people on different days, so providing a tip at the time the work is done makes sense. I'm sure it will be appreciated regardless though.
In any event, I don't think it's intrinsically rude to leave an appropriate tip with coins, but given the low value of coins typically used in the US (25 cent quarters are usually the largest coins in normal use) and the amount of a typical tip for housekeeping, you'd need a pile of at least 8-12+ coins. So a better approach would usually be to tip in $1 bills. The front desk should happily make change for you to facilitate tipping their staff. If I'm traveling internationally and end up with coins that I don't want to take home, I may leave them behind as part of a tip on the last day.
Regardless, you can leave the money on the edge of the desk on top of a note that says "thank you," to make it clear that it's a tip and to express your appreciation.
Unless you left the room in an exceptionally messy state, I don't think you need to apologize for it. They have seen far worse. I wouldn't leave a complicated note explaining that they "can" take the coins as a token of gratitude; just leave a tip, and your gratitude will be apparent.