Automatic E-mail Notification

Hi,

I have a spreadsheet where I have to select a macro button to run a
macro that will automatically update the data in the spreadsheet from a
text file that is being updated every 15 minutes by my company's MRP
system.

Two questions:

1) - Can someone give a novice some detail directions as to how I can
get this to update the file automatically when the file is opened and
perform a save? Then I wouldn't need the button.

2) - The harder question. I've been searching the Google groups for a
way of having an automatic E-mail sent out to an E-mail address or even
up to 5 recipients whenever a cell with a formula changes to a certain
response, e.g. when cell V168 goes to "Actg to Roll Cost", send an
E-mail to someone in Accounting instructing them that they have an
action to perform. I want the information in a adjacent cell in that
row to appear in the E-mail along with the information in cell V168. I
know that if I click on an E-mail address that's typed into a cell, our
Lotus Notes E-mail program will automatically address an E-mail to that
person. However, I want this to go out without my having to even know
that something has changed this value. I'm looking at several columns
of data where I want to act on any cell change in those columns
accordingly. I hope I've explained this right. I have seen links to
"www.rondebruin.nl/mail/change.htm" that probably explain this, however
I'm not quite savy enough to understand how to make that work for my
situation....

Regards,

Steve

Hi Steve

> Lotus Notes E-mail program
If you not use Outlook or OE See first if CDO is working for you Steve
http://www.rondebruin.nl/cdo.htm

You can use the CDO example then on this page
http://www.rondebruin.nl/mail/change.htm

If you need more help post back


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


"Steve" <sspatriots@yahoo.com> wrote in message news:1146677394.244322.319670@i39g2000cwa.googlegroups.com...
> Hi,
>
> I have a spreadsheet where I have to select a macro button to run a
> macro that will automatically update the data in the spreadsheet from a
> text file that is being updated every 15 minutes by my company's MRP
> system.
>
> Two questions:
>
> 1) - Can someone give a novice some detail directions as to how I can
> get this to update the file automatically when the file is opened and
> perform a save? Then I wouldn't need the button.
>
> 2) - The harder question. I've been searching the Google groups for a
> way of having an automatic E-mail sent out to an E-mail address or even
> up to 5 recipients whenever a cell with a formula changes to a certain
> response, e.g. when cell V168 goes to "Actg to Roll Cost", send an
> E-mail to someone in Accounting instructing them that they have an
> action to perform. I want the information in a adjacent cell in that
> row to appear in the E-mail along with the information in cell V168. I
> know that if I click on an E-mail address that's typed into a cell, our
> Lotus Notes E-mail program will automatically address an E-mail to that
> person. However, I want this to go out without my having to even know
> that something has changed this value. I'm looking at several columns
> of data where I want to act on any cell change in those columns
> accordingly. I hope I've explained this right. I have seen links to
> "www.rondebruin.nl/mail/change.htm" that probably explain this, however
> I'm not quite savy enough to understand how to make that work for my
> situation....
>
> Regards,
>
> Steve
>

Ron,

Ok, I managed to get something to work with the CDO using a value,
however when I tried it using the macro for a formula it wouldn't work.
Also, I need to somehow make it look for a particular phrase in a
column of cells and E-mail the phrase as well as information in an
adjacent cell in the same row. That is, have it perform a search under
column "W' for "Actg to Roll Cost". Then for example: if "Actg to
Roll Cost" shows up in cell W57, then I need the E-mail to say
something like "Actg to Roll Cost for Part Number "102-A0002-S2".
Where cell B57 = "102-A0002-S2".

Sorry I wasn't that clear in the original posting. Anyway, do you have
any idea how I can make this work?

Please advise,

Steve

Ron,

I'm still fiddling with this thing... Can you tell me how I can change
the View Code below so that if the word "Test" appears in any cell in
column 1 (other than cell A1) the change event macro will be triggered?
Column 1 is full of formulas that can change automatically when I
update my spreadsheet, so the word "Test" could appear in let's say
cell A12....


Please advise,

Steve

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("A1"), rng) Is Nothing Then
If Range("A1").Text = "Test" Then CDO_Send_ActiveSheet_Body
End If
End If
EndMacro:
End Sub

Use a formula in A1 for example that count the cells with "Test"

=COUNTIF(A2:A65536,"Test")

Then use the event with this line

If Range("A1").Value > 0 Then




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


"Steve" <sspatriots@yahoo.com> wrote in message news:1146842815.913684.198930@y43g2000cwc.googlegroups.com...
> Ron,
>
> I'm still fiddling with this thing... Can you tell me how I can change
> the View Code below so that if the word "Test" appears in any cell in
> column 1 (other than cell A1) the change event macro will be triggered?
> Column 1 is full of formulas that can change automatically when I
> update my spreadsheet, so the word "Test" could appear in let's say
> cell A12....
>
>
> Please advise,
>
> Steve
>
> 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("A1"), rng) Is Nothing Then
> If Range("A1").Text = "Test" Then CDO_Send_ActiveSheet_Body
> End If
> End If
> EndMacro:
> End Sub
>

Thanks Ron,

I'm quite new to VBA, so can you tell me where I should be inserting
the line "If Range("A1").Value > 0 Then" or if it is replacing
something, what does it replace?


Please advise,

Steve

nevermind, I just got it.....thanks for your help...I may still need
more help as I go along here, but just wanted to let you know that I've
got the latest advise you've given me to work....Steve

Ron,

I've managed to get quite a bit out of this and make it work with a
small sample spreadsheet, however, for some reason when I try and apply
what I've made work on a larger scale, the macro wants to create a new
workbook instead of e-mailing the information in the body of an E-mail.
Can you have a look at this and tell me why it's doing this?


Thanks,

Steve


Sub CDO_Send_ActiveSheet_Body()
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") =
"Fill in your SMTP server here"

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

With iMsg
Set .Configuration = iConf
.To = "somebody@yahoo.com"
.CC = ""
.BCC = ""
.From = """Drawing Office"" <myemail@yahoo.com>"
.Subject = "Status"
.HTMLBody = SheetToHTML(Sheet2)
.Send
End With

Set iMsg = Nothing
Set iConf = Nothing
End Sub


Public Function SheetToHTML(sh As Worksheet)

Dim TempFile As String
Dim Nwb As Workbook
Dim myshape As Shape
Dim fso As Object
Dim ts As Object

Selection.AutoFilter Field:=21, Criteria1:= _
"Do Not Order - Orig Item Not RoHS Compl - Need to Try and Use
Up"
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("I:L").Select
Application.CutCopyMode = False
Selection.NumberFormat = "$#,##0.00"
Range("A1").Select
ActiveWindow.SmallScroll ToRight:=9
Columns("O:Q").Select
Selection.Delete Shift:=xlToLeft
Columns("P:AD").Select
Selection.Delete Shift:=xlToLeft
Columns("O:O").Select
Selection.Columns.AutoFit
Range("A1").Select

Sheets("RoHS Conversion Action List").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select

Sheets("Sheet2").Select
Range("A1").Select
Sheets("RoHS Conversion Action List").Select
Range("A1").Select

sh.Copy

Set Nwb = ActiveWorkbook
For Each myshape In Nwb.Sheets(1).Shapes
myshape.Delete
Next
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss")
& ".htm"
Nwb.SaveAs TempFile, xlHtml
Nwb.Close False
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
SheetToHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function

Hi Steve

Do not change the function
Do what you want in the macro


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


"Steve" <sspatriots@yahoo.com> wrote in message news:1146863892.829944.117540@g10g2000cwb.googlegroups.com...
> Ron,
>
> I've managed to get quite a bit out of this and make it work with a
> small sample spreadsheet, however, for some reason when I try and apply
> what I've made work on a larger scale, the macro wants to create a new
> workbook instead of e-mailing the information in the body of an E-mail.
> Can you have a look at this and tell me why it's doing this?
>
>
> Thanks,
>
> Steve
>
>
> Sub CDO_Send_ActiveSheet_Body()
> 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") =
> "Fill in your SMTP server here"
>
> .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
> = 25
> .Update
> End With
>
> With iMsg
> Set .Configuration = iConf
> .To = "somebody@yahoo.com"
> .CC = ""
> .BCC = ""
> .From = """Drawing Office"" <myemail@yahoo.com>"
> .Subject = "Status"
> .HTMLBody = SheetToHTML(Sheet2)
> .Send
> End With
>
> Set iMsg = Nothing
> Set iConf = Nothing
> End Sub
>
>
> Public Function SheetToHTML(sh As Worksheet)
>
> Dim TempFile As String
> Dim Nwb As Workbook
> Dim myshape As Shape
> Dim fso As Object
> Dim ts As Object
>
> Selection.AutoFilter Field:=21, Criteria1:= _
> "Do Not Order - Orig Item Not RoHS Compl - Need to Try and Use
> Up"
> Cells.Select
> Selection.Copy
> Sheets("Sheet2").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Columns("I:L").Select
> Application.CutCopyMode = False
> Selection.NumberFormat = "$#,##0.00"
> Range("A1").Select
> ActiveWindow.SmallScroll ToRight:=9
> Columns("O:Q").Select
> Selection.Delete Shift:=xlToLeft
> Columns("P:AD").Select
> Selection.Delete Shift:=xlToLeft
> Columns("O:O").Select
> Selection.Columns.AutoFit
> Range("A1").Select
>
> Sheets("RoHS Conversion Action List").Select
> Application.CutCopyMode = False
> Selection.AutoFilter
> Range("A1").Select
>
> Sheets("Sheet2").Select
> Range("A1").Select
> Sheets("RoHS Conversion Action List").Select
> Range("A1").Select
>
> sh.Copy
>
> Set Nwb = ActiveWorkbook
> For Each myshape In Nwb.Sheets(1).Shapes
> myshape.Delete
> Next
> TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss")
> & ".htm"
> Nwb.SaveAs TempFile, xlHtml
> Nwb.Close False
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
> SheetToHTML = ts.ReadAll
> ts.Close
> Set ts = Nothing
> Set fso = Nothing
> Set Nwb = Nothing
> Kill TempFile
> End Function
>

Ron,

OK, I went back and made changes only to the macro, however, I'm
getting back an E-mail now with a ".htm" attachment. Any ideas what
may be causing that? Basically at the beginning of the macro, I have
added a Call to the macro which creates what I want to send...(see
below). Is this the wrong approach?


Please advise,

Steve


Sub CDO_Send_ActiveSheet_Body()
Call Status_to_Send
Dim iMsg As Object
Dim iConf As Object

Is this basic example working for you ?
http://www.rondebruin.nl/cdo.htm#body


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


"Steve" <sspatriots@yahoo.com> wrote in message news:1147103396.795837.174840@g10g2000cwb.googlegroups.com...
> Ron,
>
> OK, I went back and made changes only to the macro, however, I'm
> getting back an E-mail now with a ".htm" attachment. Any ideas what
> may be causing that? Basically at the beginning of the macro, I have
> added a Call to the macro which creates what I want to send...(see
> below). Is this the wrong approach?
>
>
> Please advise,
>
> Steve
>
>
> Sub CDO_Send_ActiveSheet_Body()
> Call Status_to_Send
> Dim iMsg As Object
> Dim iConf As Object
>

No, ... I still get an E-mail with an attachment that says "C.htm" as
the file name. I can use the open attachment option and see the
information that I actually want shown in the body of the E-mail, but
again it is only viewable using the open attachment option.....Thanks,
Steve

Ron,

Is it possible to have a macro that updates a spreadsheet from an
external source (e.g. text file imported) thus replaces that cell with
a new value where a formula resides (remains as a formula) to actually
force a change event? If so, how.....


Still quite lost,

Steve