I guess the answer is "it depends".

But there's one thing I wouldn't do: using sp_send_dbmail directly within a trigger, since the mail sending process would become part of the insert transaction which may have a significant impact on performance (especially if your "primary application" performs row-based inserts).

Do you need immediate email alert or would a scheduled email be enough?

Do you need details per related insert statement or a summary?

Depending on the answer to the questions above I'd either use Service Broker. Or a "message table" filled by insert triggers for the tables in question together with a scheduled job to send me the summary.

Side note: Service Broker is an interesting feature worth to get used to and I don't think it would be "overkill" for that task. It's better to start with a rather easy task to see how it works rather than waiting for a much more complicated scenario...