VBA to Send Outlook Email

**Larry.LeBlanc@O** · 07-31-2009, 02:02 PM

I am using VBA to automatically send emails with Outlook. I am surprised that the VBA takes about 10 seconds for each email.

Exceprts from my code are below. Is there anyway to speed this up?

Sub SendMessage(Recipient, Message, Subject)
Dim MyEmailVariable As Outlook.MailItem
Set MyEmailVariable = MyOutlookVariable.CreateItem(olMailItem) 'Begin new email
' 
MyEmailVariable.To = Recipient                   'Set the person sending to
MyEmailVariable.Subject = Subject              'Set the Subject of the message
MyEmailVariable.Body = Message                 'Set the body of the message
MyEmailVariable.Attachments.Add "C:\Dummy Attachment.XLSX"     MyEmailVariable.Send                         
End Sub

**Paul** · 07-31-2009, 02:21 PM

Hi Larry,

My guess is that at least 5 seconds of the 10 is used by the warning that an application is trying to send e-mail on your behalf. Not sure what the other 5 would be.

I used the following code and it ran in about 6 seconds (including the 5 second warning, for which I have an app that auto-clicks YES after it becomes active).

Sub SendMessage(Recipient, Message, Subject)

Dim MyOutlookVariable As Outlook.Application
Dim MyEmailVariable As Outlook.MailItem
Set MyOutlookVariable = CreateObject("Outlook.Application")
Set MyEmailVariable = MyOutlookVariable.CreateItem(olMailItem) 'Begin new email

MyEmailVariable.To = Recipient                  'Set the person sending to
MyEmailVariable.Subject = Subject               'Set the Subject of the message
MyEmailVariable.Body = Message                  'Set the body of the message
MyEmailVariable.Attachments.Add "C:\Dummy Attachment.XLSX"
MyEmailVariable.Send

Set MyOutlookVariable = Nothing
Set MyEmailVariable = Nothing
End Sub

**DonkeyOte** · 07-31-2009, 02:22 PM

Out of curiosity, how long does the code take without Attachments ?

(I am presuming MyOutlookVariable to be a Global variable somewhere ?)

We might also need to see the code being used to invoke the mail call...