lcpx
10-23-2006, 10:13 AM
Hi all,
We are trying to send email via Excel. I found a piece of code which looks working OK. But the weird thing is each time when I ran the Macro manually I have to do it twice to make it work automatically. The first time it will open a window in Outlook but won?t send the email automatically, then I close the outlook window and run the code again, it works fine. Can anybody let me know what really happened. Why each time I have to run it twice?
Sub Send_Msg()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With objMail
.To = "lcpx@xxxx.co.uk (lcpx@xxxx.co.uk)"
.Subject = "Automated Mail Response"
.Body = "This is an automated message from Excel. " & _
"The cost of the item that you inquired about is: " & _
Format(Range("A1").Value, "$ #,###.#0") & "."
.Display
End With
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Set objMail = Nothing
Set objOL = Nothing
End Sub
Thanks very much for your time and help.
Pete
We are trying to send email via Excel. I found a piece of code which looks working OK. But the weird thing is each time when I ran the Macro manually I have to do it twice to make it work automatically. The first time it will open a window in Outlook but won?t send the email automatically, then I close the outlook window and run the code again, it works fine. Can anybody let me know what really happened. Why each time I have to run it twice?
Sub Send_Msg()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With objMail
.To = "lcpx@xxxx.co.uk (lcpx@xxxx.co.uk)"
.Subject = "Automated Mail Response"
.Body = "This is an automated message from Excel. " & _
"The cost of the item that you inquired about is: " & _
Format(Range("A1").Value, "$ #,###.#0") & "."
.Display
End With
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Set objMail = Nothing
Set objOL = Nothing
End Sub
Thanks very much for your time and help.
Pete