Automatic E-mail Notification

S

Steve

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
 
S

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
 
S

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
 
R

Ron de Bruin

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
 
S

Steve

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
 
S

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
 
S

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 = "[email protected]"
.CC = ""
.BCC = ""
.From = """Drawing Office"" <[email protected]>"
.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
 
S

Steve

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
 
S

Steve

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
 
S

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
 
Top