Not using send button on excel e-mail
 

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 = "
.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

Not using send button on excel e-mail
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Richard" wrote in message ...
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 = "
.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

Not using send button on excel e-mail
 

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.

-----Original Message-----
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 = "
.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
.

Not using send button on excel e-mail
 

Hi Pete

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

TIA
-----Original Message-----
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.

-----Original Message-----
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 = "
.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
.

.