Excel VBA - sending several emails in Outlook 2010

Hi

I need to send about 30 emails to various members of the company as quickly as possible. I have used the code below as a test where the bottom procedure calls the top one 5 times. It takes a long time to send eaxh one and asks if I should allow each email. Is there a way around this and can I speed it up.? Thanks.

Sub oSendEmail(ByVal oTo As String, ByVal oSubject As String, ByVal oBody As String, Optional oCC As String, Optional oBCC As String)

    Dim olApp As Object
    Dim olMsg As Object
   
    Set olApp = CreateObject("Outlook.Application")
    Set olMsg = olApp.CreateItem(0)
    With olMsg
        .To = oTo
        .CC = oCC
        .BCC = oBCC
        .Subject = oSubject
        .Body = oBody
 
        .Send
    End With
   
    Set olMsg = Nothing
    Set olApp = Nothing

End Sub


Sub oTest()
      Call oSendEmail("murbro9@yahoo.com", "test subject", "this is the body", "", "")
      Call oSendEmail("murbro9@yahoo.com", "test subject", "this is the body", "", "")
      Call oSendEmail("murbro9@yahoo.com", "test subject", "this is the body", "", "")
      Call oSendEmail("murbro9@yahoo.com", "test subject", "this is the body", "", "")
      Call oSendEmail("murbro9@yahoo.com", "test subject", "this is the body", "", "")
End Sub
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
you do not need to recreate the outlook object (olApp) all the time. just do it once then create a new message
0
 
rockiroadsCommented:
regarding the asking, that is just the security. there was a freebie tool but cant remember the name exactly
0
 
rockiroadsConnect With a Mentor Commented:
u could try remove security this way http://www.4tops.com/accessmailmergehelp/Send_email_without_outlook_security_warning.htm

and I remembered that tool. something I posted long time back but not sure if it will still work with 2010
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21904604.html
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Jerry PaladinoCommented:
Do you have the "Microsoft Outlook 12.0 Object Library" reference library selected in the VBE?  The method you are using should not display the security warning
Menu - TOOLS / REFERENCES
I ran your code and it sent the 5 emails in less than 5 seconds and it did not display the security warning.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
I am using Outlook 2010 so even if I reference the Microsoft Outlook 14.0 Object Library that doesn't help.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
I believe this might be the answer:
http://www.add-in-express.com/outlook-security/
0
 
Jerry PaladinoCommented:
Look at these three Microsoft article and see if any of the new security changes for 2007/2010 apply to your setup.  (note the antivirus and trust center setting)
Code Security Changes in Outlook 2007 (should also apply to 2010)
http://msdn.microsoft.com/en-us/library/bb226709.aspx 
Using VBA in Excel to Send Workbooks and Ranges Through E-Mail with Outlook (Part 1 of 2)
http://msdn.microsoft.com/en-us/library/ff458119(office.11).aspx    Part-1
http://msdn.microsoft.com/en-us/library/ff519602(office.11).aspx    Part-2
 
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.