Good question! I need this too, is it possible and how ?

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 :)