PDA

View Full Version : Sending email via Excel



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

Bob Phillips
10-23-2006, 10:21 AM
Change



.Display


to



.Send

lcpx
10-24-2006, 01:11 AM
Thanks for your reply, but this is not what I want. If I use objMail.Send, an alert window would come out and ask me to confirm sending the email. To be able to send the message out, I have to press the YES button. That’s why I use

Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"

We are trying to do it automatically in the midnight, no manual intervention. The above code I’d pasted yesterday was working fine, but not all the time. Most of time I have to run it twice to make it work that really confused me.

Appreciate any thought you have on it.

Bob Phillips
10-24-2006, 02:06 AM
Try preceding the Send with

Application.DisplayAlerts = False

and reset afterwards.

lcpx
10-24-2006, 02:25 AM
Tried but didn't solve the problem. Please find the attachment for detail. Thanks

Bob Phillips
10-24-2006, 03:03 AM
ARe you getting a message circa a program trying to send a message, with a progress bar?

lcpx
10-24-2006, 04:20 AM
Yes that's exactly what I got, is there a way to get around it?

Bob Phillips
10-24-2006, 06:12 AM
Yes, a product called Redemption

http://www.dimastr.com/redemption/