Not using send button on excel e-mail

[Richard]

I have put some code into my spreadsheet to send an e-mail
including in the message body a value. I would like the e-
mail to be sent automatically, without user intervention
hitting send. Can i do this? The code i have is:

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 = "(e-mail address removed)"
.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
Set objMail = Nothing
Set objOL = Nothing
End Sub

 

[Ron de Bruin]

Change Display to Send

But you get a security warning then from Outlook
Maybe CDO is a option for you

See this page
http://www.rondebruin.nl/sendmail.htm#Prevent

 

[Pete McCOsh]

Richard,

just add the following line at the end of this section

With objMail
...
.send
End With

Of course, you may still get the security pop-ups if
you've got the Outlook security patch loaded, but that's
another story. If you do get this problem, there is a way
round it in code: post back if you find you need that.

Cheers, Pete.

 

[Edgar]

Hi Pete

I would be interested in the code for getting round the
security promt

TIA