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.Appl
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.
Call oSendEmail("murbro9@yahoo.
Call oSendEmail("murbro9@yahoo.
Call oSendEmail("murbro9@yahoo.
Call oSendEmail("murbro9@yahoo.
End Sub
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.Appl
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.
Call oSendEmail("murbro9@yahoo.
Call oSendEmail("murbro9@yahoo.
Call oSendEmail("murbro9@yahoo.
Call oSendEmail("murbro9@yahoo.
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
rockiroads
regarding the asking, that is just the security. there was a freebie tool but cant remember the name exactly
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
I am using Outlook 2010 so even if I reference the Microsoft Outlook 14.0 Object Library that doesn't help.
ASKER
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
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
ASKER
thanks