[Solved] VBA: Emailing multiple recipients

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hi, I'm trying to email up to 700 recipients at once, but appear to be stuck with having to click that I want to allow the email to be sent from outside outlook. Is there a way to send emails frm excel without having to go through this, without setting security to low as I am not at liberty to do that.


    Matt. :o :question: :banghead:

  • Look at some other posts for the method to email. Replace the recipients address with a variable then place the whole lot in a for/next loop with the variable changing for each iteration (referencing a col of addresses or a hardcoded array)

    You'll also need to look at turning off the warning about "do you want to send". There's another post in here somewhere with details about that (It got my app to work!)


  • AFAIK there isn't - else i haven't found it. Perhaps a sludgy Sendkeys solution? else you're gonna have to click away!

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • The following doesn't ask me if it's okay to send (W95/XL97SR2/Outlook97)

    Application.DisplayAlerts = False
    ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:=textSubj
    ActiveWorkbook.Close SaveChanges:=True
    Application.DisplayAlerts = True

    Unless . . . It's a setting in Outlook that's allowing this??


  • Application.DisplayAlerts = False

    This will stop Excel displaying alert messages, but I am not sure whether it will stop Outlook's messages. Basically when Excel tries to send a message using Outlook, Outlook responds with a message to ask permission. This is a security measure that is to prevent unauthorised emails eg by a virus or worm.

  • Thanks for the advice. I seem to find that you can only stop the alerts if you reduce the security settings, which I am unfortunately not allowed to do. As an alternative I am using CDO to send the email using SMTP>

    Thanks again,


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!