?
Solved

Excel VBA  - sending several emails in Outlook 2010

Posted on 2010-09-01
8
Medium Priority
?
948 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:Murray Brown
  • 3
  • 3
  • 2
8 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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:Murray Brown
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:Murray Brown
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:Murray Brown
ID: 33607740
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

571 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