Solved

Excel VBA  - sending several emails in Outlook 2010

Posted on 2010-09-01
8
942 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

733 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