If you are working in-house and the other members of your team can trust you,
create a digital certificate for yourself -- run "Selfcert.exe" which you
will probably find in C:\Program Files\Microsoft Office\Office.

Once you have created your certificate go into the macro editor and select
Tools/Digital Signature from the menu. Select your certificate and resave
your macro.

When you now send it out for others they will be asked on the first occasion
whether the macro should be enabled but there is also a tick box to enable
them to always trust you. If they select that box they will never be troubled
with that opening dialogue again either with this macro or any other macro
that you sign with the same certificate.