I would inherit the normal sqlprovider and add that send email function. Should only be an hours work or so...
I think the best way to do this is to add a custom activation page/module for admins and in that send the mail
Not clean but other way could be intercept EPiServer.Shell.Security.UIUserProvider abstract class. It has a function called "UpdateUser" and track the "Activated" user into table. And then having scheduled job which reads the table and send email every say X minutes! This is not clean way as UIUserProvider is not documented by episerver. Best way as Daniel said is to override Membership provider.
If you have Identity set up, user info is very easily expandable, so you could add Notified and Active in there, then in a scheduled job fetch all recently updated/not notified ones, send emails out, then set Notified to true.
With traditional membership providers, I'd stick with Daniel's approach.
// Although this looks like an old post, so Identity not supported in early versions of 7, but this should be pretty future proof :)
Users can apply to get a account on a episerver website. When these are created they by default have active set to false. A administrator then activates this manually in the admin-interface. I wonder if it is possible when this checkbox is checked that a automatic mail is sent to the user?
I really appreciate any help someone can provide.