I know I'm late to the party here, but I've successfully used the following article to implement query-driven HTML email from Snowflake - https://community.snowflake.com/s/article/How-to-include-query-results-in-Snowflake-email-alert-notifications.

And @nickw... without a link to the documentation, I can't verify your statement at time of posting, but I'm able to send text/html MIME type emails from SYSTEM$SEND_EMAIL. The MIME type is the last argument expected in the Function Call - https://docs.snowflake.com/en/sql-reference/stored-procedures/system_send_email.

Regards, Johnny