Solved

Excel VBA  - sending several emails in Outlook 2010

Posted on 2010-09-01
8
939 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now