I was working on a small project at work using OutlookXP and ExcelXP and whenever I would use the .Send method I got the display window saying "Another application is trying to send email....." I did not want this to appear everytime a user used the application. I did some digging and decided to use SendKeys() and it works great.
The below code runs fine on Win2K using Outlook & ExcelXP. You do not need to use Sendkeys with OL2k. the .SendMethod is enough. It is not tested on any other OS's or Office Versions. I wanted to post it here for anyone else looking to incorporate any type of email automation with their applications.
Hope it is helpful to someone.
Sub Send_Message()
'Declare and set outlook object variables and mail item variables
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
'creates a new mail message
Set objMail = objOL.CreateItem(olMailItem)
With objMail
'Enters the To: information. You can add mulitiple recepients by adding the semicolon ";" between recepients
'you can also add the .Bcc (Blind Carbon Copy) and .CC
.To = "[email protected];[email protected]"
'Fills in the subject line
.Subject = "Automated Mail Response"
'Fills in the body of the message if you want. Can be used great with ranges and string variables
.Body = "This is a test E-mail using Outlook and Excel from code to send and add attachments"
'Add attachments if you want
.Attachments.Add ("C:\ExcelFiles\TestBook.xls")
'to use sendkeys I had to get the message to display
.Display
'I had it wait a few seconds to make sure the new message window was the active window
Application.Wait (10)
'once new mail window is shown use Ctrl + Enter to send the e-mail
'use send keys to enter the shortcut for sending
SendKeys "^{Enter}", 5
End With
'close your objects
Set objMail = Nothing
Set objOL = Nothing
End Sub