I give up.

It seems like SSIS is OK with dynamic Excel files when they're a data source but when it comes to a destination it stomps its little foot and refuses to play nicely.

I'm going to send the email I need with embedded HTML (hello scripting since the Send Mail task doesn't like HTML) and let it go since the core data is in a table.