Excel file sending an e-mail, is possible?

[Mukesh]

Is it possible excel send out an e-mail when certain
conditions are met?

 

[Ron de Bruin]

Hi Mukesh

http://www.rondebruin.nl/mail/change.htm

 

[Mukesh]

Thank you Rob....I will test it out and post back if
I encounter any problems.

Thanks.
Mukesh

 

[Mukesh]

Rob,
I am using example 1 from your site....and while running the
macro, I get an error "Can't execute code in break mode",
sorry don't understand, how do I fix it.

Thanks.
Mukesh

 

[Ron de Bruin]

Click on the reset button in the VBA editor and be sure that you change the macro name in the change event

 

[Mukesh]

Rob,

Now I am getting an error " The "SendUsing" configuration value
is invalid ". .....can fix?

Thanks.
Mukesh

 

[Ron de Bruin]

Please read the information on the CDO page if you want to use this macro

 

[Mukesh]

Hi Rob,

It took me nearly 3 hours, and finally figured out what wrong I was
doing....its
working now. Thanks a lot.
On to my project.

Thanks.
Mukesh

 

[Lise]

Hi Mukesh

I too have had the problems you are having and although have read the info
Ron advised am still none the wiser as to what I am doing wrong - how did you
sort this out please?

 

[Mukesh]

Hi Lise,

I just saw your request, hope you have sorted out, if not hereunder is the
code that
works well, the only problem is that I have to run the macro to send e-mail,
whereas
I want e-mail to be sent out automatically once the cell values have changed.
Haven't had time to sort that out, but if you or anyone knows, please advise.

Copy/paste to vb, change e-mail settings/password which I am sure you
know or can find in outlook.


Sub Mail_CDO()
Dim iMsg As Object
Dim iConf As Object
' Dim Flds As Variant
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds

..Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

..Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"smtp.domain.com"

..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

..Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

..Item("http://schemas.microsoft.com/cdo/configuration/sendusername") =
"LoginID"

..Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =
"123456"

.Update
End With
With iMsg
Set .Configuration = iConf
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.From = """Sender's Name"" <[email protected]>"
.Subject = "Your Subject goes here - This is Subject Line"
.TextBody = "This is the main body" & vbNewLine & vbNewLine & _
"Message goes here - Cell A1 value has changed"
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing
End Sub


Thanks.
Mukesh

 

[Ron de Bruin]

See
http://www.rondebruin.nl/mail/change.htm

 

[Mukesh]

Thank you Ron.

I have following code in module 2, what could be wrong as it
doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("A6"), rng) Is Nothing Then
If Range("A6").Value < 100 Then Mail_CDO
End If
End If
EndMacro:
End Sub

Is it possible to have a pop up message to say that the e-mail is
sent/being sent or is not sent due to an error or something like that.

Thanks.
Mukesh

 

[Ron de Bruin]

Read good, not in a normal module

1) Right click on a sheet tab and choose view code
2) Paste one of the events in this module.
3) Alt-Q to go back to Excel

 

[Mukesh]

It worked.

Thank you very much.

Rgds,
Mukesh

Read good, not in a normal module

1) Right click on a sheet tab and choose view code
2) Paste one of the events in this module.
3) Alt-Q to go back to Excel

 

[Mukesh]

Ron,

I have streaming prices in excel and I have set to receive an
e-mail when a certain value on a particular cell is reached,
unfortunately it doesn't work (its a formula cell (adds few cells),
I am using the 2nd option from your site).

However, when I manually input numbers and set to receive an e-mail,
it works!! what could be wrong, is it because of live prices?
is there a way to fix.

Thanks.
Mukesh