Solved

Excel VBA  - sending several emails in Outlook 2010

Posted on 2010-09-01
8
940 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:murbro
  • 3
  • 3
  • 2
8 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33581417
you do not need to recreate the outlook object (olApp) all the time. just do it once then create a new message
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33581440
regarding the asking, that is just the security. there was a freebie tool but cant remember the name exactly
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 500 total points
ID: 33581456
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33582432
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
 

Author Comment

by:murbro
ID: 33584850
I am using Outlook 2010 so even if I reference the Microsoft Outlook 14.0 Object Library that doesn't help.
0
 

Author Comment

by:murbro
ID: 33584916
I believe this might be the answer:
http://www.add-in-express.com/outlook-security/
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33585659
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
 

Author Closing Comment

by:murbro
ID: 33607740
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question