wow, for an idiot like me that's alot, but i tried to follow it and after
compiling it got this error message=

for=
Dim myWKSChange As Class1

"compile error:
User-defined type not defined

"Dave Peterson" wrote in message
...
I think that if you can't get to the project, then you're only choice is
to have
an application event lurking in the background checking every change you
make to
every cell in every worksheet.

If it sees a change to a cell that's not in the correct workbook, or not
on the
correct worksheet or not in A10, then it just quits.

If you think you like this idea, then
create a new workbook
alt-F11 to get to the VBE.
find your new workbook/project and select it.
then Insert|Class module from the menubar.

Paste this in that class module code window:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If LCase(Sh.Parent.FullName) _
< LCase("C:\mystuff\LottoStatisticsXLp.xls") Then Exit Sub
If LCase(Sh.Name) < LCase("output") Then Exit Sub

If Intersect(Target, Range("A10")) Is Nothing Then Exit Sub

If Target.Value = 10000# _
And Range("B2").Value < "Contacted" Then
Call Mail_Text_in_Body
Application.EnableEvents = False
Range("B2").Value = "Contacted"
Application.EnableEvents = True
End If

End Sub

Note that it's pretty specific about the name/location of the workbook.
If you
wanted you could just look at the .name (and toss the drive/folder bits)
like:

if lcase(sh.parent.name) < lcase("lottostatisticsxlp.xls") then exit sub


Now doubleclick on the ThisWorkbook object. Paste this into the code
window:

Option Explicit
Dim myWKSChange As Class1
Private Sub Workbook_Open()
Set myWKSChange = New Class1
Set myWKSChange.xlApp = Application
End Sub
Private Sub workbook_close()
Set myWKSChange.xlApp = Nothing
End Sub

This tells excel to look for changes in every worksheet.

Then add your code for mail_text_in_body to a General module.

Save this workbook, close it and reopen it (or just run the workbook_open
code).

When I tested, I commented out the part that really sent the message:
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%s", True


If you want to learn more about application events, then Chip Pearson has
some
nice notes at:
http://www.cpearson.com/excel/AppEvent.htm


======
Another alternative would be to go back to the developer and bribing
him/her
with coffee(?) to unprotect the project so that you could add your code.

harry wrote:

Hi,

i have 2 workbooks, 1 is password protected so i can't make other
macro's on
it. Using excel 2002 btw + winxp all sp's etc.

This is want i want to do:

1. Password protected workbook has changing cell values when macros in
it
are running. Call this workbook ="test.xls", it's in my "c:\mystuff"
directory
2. I want to be able to do this when cell A10 in "test.xls" in a
worksheet
called "output" is = 10000 then
3. I want to automatically email once, using outlook express, certain
cell
values from "test.xls" in the "output" worksheet.
4. since the workbook is locked i can't add other macro's to "test.xls",
so
i thought ok i'll just make another workbook called "mail.xls" also in
my
"c:\mystuff" directory and transfer the "test.xls\output" cell reference
A10
and whatever other ones i need to email to the new workbook
"mail.xls\sheet1".

5. How i can i do this, what is the exact code that i can use to make it
happen?

6. This is what i've tried, the mail part works however when the value
changes to 10000 in test.xls and then also in mail.xls it doesn't work,
ie
if i actually go into the mail.xls\sheet1 and do a change like delete or
add
something then the macro will fire, otherwise it won't do anything; is
Worksheet_Change the right 1 to use to fire the macro when it's changed
automatically by a cell reference to another workbook test.xls?????

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A10").Value = 10000# And Range("B2").Value < "Contacted" Then
Call Mail_Text_in_Body
Range("B2").Value = "Contacted"
End If
End Sub

Sub Mail_Text_in_Body()
'In 2002 I can go to 600-700 characters
Dim Msg As String, URL As String
Dim Recipient As String, Subj As String, HLink As String

Recipient = "
Subj = "mail test"
Msg = Range("a6")

HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s", True
End Sub

7. so then i thought ok, mabye i should reference the 10000 directly
from
the test.xls sheet but i don't know how to do that part? = this doesn't
work

If Workbooks("C:\mystuff\LottoStatisticsXLp.xls
Output").Range("A10").Value
= 10000# Then

Thanks Gurus

--

Dave Peterson

When you inserted the Class module, what was it called?

(You did insert the class module, right? Not just module, not a userform,...)

If it wasn't named Class1, then click on it and hit F4 and rename it to Class1
in the properties window.

harry wrote:

wow, for an idiot like me that's alot, but i tried to follow it and after
compiling it got this error message=

for=
Dim myWKSChange As Class1

"compile error:
User-defined type not defined

"Dave Peterson" wrote in message
...
I think that if you can't get to the project, then you're only choice is
to have
an application event lurking in the background checking every change you
make to
every cell in every worksheet.

If it sees a change to a cell that's not in the correct workbook, or not
on the
correct worksheet or not in A10, then it just quits.

If you think you like this idea, then
create a new workbook
alt-F11 to get to the VBE.
find your new workbook/project and select it.
then Insert|Class module from the menubar.

Paste this in that class module code window:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If LCase(Sh.Parent.FullName) _
< LCase("C:\mystuff\LottoStatisticsXLp.xls") Then Exit Sub
If LCase(Sh.Name) < LCase("output") Then Exit Sub

If Intersect(Target, Range("A10")) Is Nothing Then Exit Sub

If Target.Value = 10000# _
And Range("B2").Value < "Contacted" Then
Call Mail_Text_in_Body
Application.EnableEvents = False
Range("B2").Value = "Contacted"
Application.EnableEvents = True
End If

End Sub

Note that it's pretty specific about the name/location of the workbook.
If you
wanted you could just look at the .name (and toss the drive/folder bits)
like:

if lcase(sh.parent.name) < lcase("lottostatisticsxlp.xls") then exit sub


Now doubleclick on the ThisWorkbook object. Paste this into the code
window:

Option Explicit
Dim myWKSChange As Class1
Private Sub Workbook_Open()
Set myWKSChange = New Class1
Set myWKSChange.xlApp = Application
End Sub
Private Sub workbook_close()
Set myWKSChange.xlApp = Nothing
End Sub

This tells excel to look for changes in every worksheet.

Then add your code for mail_text_in_body to a General module.

Save this workbook, close it and reopen it (or just run the workbook_open
code).

When I tested, I commented out the part that really sent the message:
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%s", True


If you want to learn more about application events, then Chip Pearson has
some
nice notes at:
http://www.cpearson.com/excel/AppEvent.htm


======
Another alternative would be to go back to the developer and bribing
him/her
with coffee(?) to unprotect the project so that you could add your code.

harry wrote:

Hi,

i have 2 workbooks, 1 is password protected so i can't make other
macro's on
it. Using excel 2002 btw + winxp all sp's etc.

This is want i want to do:

1. Password protected workbook has changing cell values when macros in
it
are running. Call this workbook ="test.xls", it's in my "c:\mystuff"
directory
2. I want to be able to do this when cell A10 in "test.xls" in a
worksheet
called "output" is = 10000 then
3. I want to automatically email once, using outlook express, certain
cell
values from "test.xls" in the "output" worksheet.
4. since the workbook is locked i can't add other macro's to "test.xls",
so
i thought ok i'll just make another workbook called "mail.xls" also in
my
"c:\mystuff" directory and transfer the "test.xls\output" cell reference
A10
and whatever other ones i need to email to the new workbook
"mail.xls\sheet1".

5. How i can i do this, what is the exact code that i can use to make it
happen?

6. This is what i've tried, the mail part works however when the value
changes to 10000 in test.xls and then also in mail.xls it doesn't work,
ie
if i actually go into the mail.xls\sheet1 and do a change like delete or
add
something then the macro will fire, otherwise it won't do anything; is
Worksheet_Change the right 1 to use to fire the macro when it's changed
automatically by a cell reference to another workbook test.xls?????

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A10").Value = 10000# And Range("B2").Value < "Contacted" Then
Call Mail_Text_in_Body
Range("B2").Value = "Contacted"
End If
End Sub

Sub Mail_Text_in_Body()
'In 2002 I can go to 600-700 characters
Dim Msg As String, URL As String
Dim Recipient As String, Subj As String, HLink As String

Recipient = "
Subj = "mail test"
Msg = Range("a6")

HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s", True
End Sub

7. so then i thought ok, mabye i should reference the 10000 directly
from
the test.xls sheet but i don't know how to do that part? = this doesn't
work

If Workbooks("C:\mystuff\LottoStatisticsXLp.xls
Output").Range("A10").Value
= 10000# Then

Thanks Gurus

--

Dave Peterson


--

Dave Peterson

how bout i email you the small excel file and you can see what i did?

"Dave Peterson" wrote in message
...
When you inserted the Class module, what was it called?

(You did insert the class module, right? Not just module, not a
userform,...)

If it wasn't named Class1, then click on it and hit F4 and rename it to
Class1
in the properties window.

harry wrote:

wow, for an idiot like me that's alot, but i tried to follow it and
after
compiling it got this error message=

for=
Dim myWKSChange As Class1

"compile error:
User-defined type not defined

"Dave Peterson" wrote in message
...
I think that if you can't get to the project, then you're only choice
is
to have
an application event lurking in the background checking every change
you
make to
every cell in every worksheet.

If it sees a change to a cell that's not in the correct workbook, or
not
on the
correct worksheet or not in A10, then it just quits.

If you think you like this idea, then
create a new workbook
alt-F11 to get to the VBE.
find your new workbook/project and select it.
then Insert|Class module from the menubar.

Paste this in that class module code window:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

If Target.Cells.Count 1 Then Exit Sub
If LCase(Sh.Parent.FullName) _
< LCase("C:\mystuff\LottoStatisticsXLp.xls") Then Exit
Sub
If LCase(Sh.Name) < LCase("output") Then Exit Sub

If Intersect(Target, Range("A10")) Is Nothing Then Exit Sub

If Target.Value = 10000# _
And Range("B2").Value < "Contacted" Then
Call Mail_Text_in_Body
Application.EnableEvents = False
Range("B2").Value = "Contacted"
Application.EnableEvents = True
End If

End Sub

Note that it's pretty specific about the name/location of the
workbook.
If you
wanted you could just look at the .name (and toss the drive/folder
bits)
like:

if lcase(sh.parent.name) < lcase("lottostatisticsxlp.xls") then exit
sub


Now doubleclick on the ThisWorkbook object. Paste this into the code
window:

Option Explicit
Dim myWKSChange As Class1
Private Sub Workbook_Open()
Set myWKSChange = New Class1
Set myWKSChange.xlApp = Application
End Sub
Private Sub workbook_close()
Set myWKSChange.xlApp = Nothing
End Sub

This tells excel to look for changes in every worksheet.

Then add your code for mail_text_in_body to a General module.

Save this workbook, close it and reopen it (or just run the
workbook_open
code).

When I tested, I commented out the part that really sent the message:
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%s", True


If you want to learn more about application events, then Chip Pearson
has
some
nice notes at:
http://www.cpearson.com/excel/AppEvent.htm


======
Another alternative would be to go back to the developer and bribing
him/her
with coffee(?) to unprotect the project so that you could add your
code.

harry wrote:

Hi,

i have 2 workbooks, 1 is password protected so i can't make other
macro's on
it. Using excel 2002 btw + winxp all sp's etc.

This is want i want to do:

1. Password protected workbook has changing cell values when macros
in
it
are running. Call this workbook ="test.xls", it's in my "c:\mystuff"
directory
2. I want to be able to do this when cell A10 in "test.xls" in a
worksheet
called "output" is = 10000 then
3. I want to automatically email once, using outlook express,
certain
cell
values from "test.xls" in the "output" worksheet.
4. since the workbook is locked i can't add other macro's to
"test.xls",
so
i thought ok i'll just make another workbook called "mail.xls" also
in
my
"c:\mystuff" directory and transfer the "test.xls\output" cell
reference
A10
and whatever other ones i need to email to the new workbook
"mail.xls\sheet1".

5. How i can i do this, what is the exact code that i can use to
make it
happen?

6. This is what i've tried, the mail part works however when the
value
changes to 10000 in test.xls and then also in mail.xls it doesn't
work,
ie
if i actually go into the mail.xls\sheet1 and do a change like
delete or
add
something then the macro will fire, otherwise it won't do anything;
is
Worksheet_Change the right 1 to use to fire the macro when it's
changed
automatically by a cell reference to another workbook test.xls?????

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A10").Value = 10000# And Range("B2").Value < "Contacted"
Then
Call Mail_Text_in_Body
Range("B2").Value = "Contacted"
End If
End Sub

Sub Mail_Text_in_Body()
'In 2002 I can go to 600-700 characters
Dim Msg As String, URL As String
Dim Recipient As String, Subj As String, HLink As String

Recipient = "
Subj = "mail test"
Msg = Range("a6")

HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s", True
End Sub

7. so then i thought ok, mabye i should reference the 10000 directly
from
the test.xls sheet but i don't know how to do that part? = this
doesn't
work

If Workbooks("C:\mystuff\LottoStatisticsXLp.xls
Output").Range("A10").Value
= 10000# Then

Thanks Gurus

--

Dave Peterson


--

Dave Peterson

How about I email you my test workbook.

If that's not ok, then delete it. (It's on it's way.)

harry wrote:

how bout i email you the small excel file and you can see what i did?

"Dave Peterson" wrote in message
...
When you inserted the Class module, what was it called?

(You did insert the class module, right? Not just module, not a
userform,...)

If it wasn't named Class1, then click on it and hit F4 and rename it to
Class1
in the properties window.

harry wrote:

wow, for an idiot like me that's alot, but i tried to follow it and
after
compiling it got this error message=

for=
Dim myWKSChange As Class1

"compile error:
User-defined type not defined

"Dave Peterson" wrote in message
...
I think that if you can't get to the project, then you're only choice
is
to have
an application event lurking in the background checking every change
you
make to
every cell in every worksheet.

If it sees a change to a cell that's not in the correct workbook, or
not
on the
correct worksheet or not in A10, then it just quits.

If you think you like this idea, then
create a new workbook
alt-F11 to get to the VBE.
find your new workbook/project and select it.
then Insert|Class module from the menubar.

Paste this in that class module code window:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

If Target.Cells.Count 1 Then Exit Sub
If LCase(Sh.Parent.FullName) _
< LCase("C:\mystuff\LottoStatisticsXLp.xls") Then Exit
Sub
If LCase(Sh.Name) < LCase("output") Then Exit Sub

If Intersect(Target, Range("A10")) Is Nothing Then Exit Sub

If Target.Value = 10000# _
And Range("B2").Value < "Contacted" Then
Call Mail_Text_in_Body
Application.EnableEvents = False
Range("B2").Value = "Contacted"
Application.EnableEvents = True
End If

End Sub

Note that it's pretty specific about the name/location of the
workbook.
If you
wanted you could just look at the .name (and toss the drive/folder
bits)
like:

if lcase(sh.parent.name) < lcase("lottostatisticsxlp.xls") then exit
sub


Now doubleclick on the ThisWorkbook object. Paste this into the code
window:

Option Explicit
Dim myWKSChange As Class1
Private Sub Workbook_Open()
Set myWKSChange = New Class1
Set myWKSChange.xlApp = Application
End Sub
Private Sub workbook_close()
Set myWKSChange.xlApp = Nothing
End Sub

This tells excel to look for changes in every worksheet.

Then add your code for mail_text_in_body to a General module.

Save this workbook, close it and reopen it (or just run the
workbook_open
code).

When I tested, I commented out the part that really sent the message:
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%s", True


If you want to learn more about application events, then Chip Pearson
has
some
nice notes at:
http://www.cpearson.com/excel/AppEvent.htm


======
Another alternative would be to go back to the developer and bribing
him/her
with coffee(?) to unprotect the project so that you could add your
code.

harry wrote:

Hi,

i have 2 workbooks, 1 is password protected so i can't make other
macro's on
it. Using excel 2002 btw + winxp all sp's etc.

This is want i want to do:

1. Password protected workbook has changing cell values when macros
in
it
are running. Call this workbook ="test.xls", it's in my "c:\mystuff"
directory
2. I want to be able to do this when cell A10 in "test.xls" in a
worksheet
called "output" is = 10000 then
3. I want to automatically email once, using outlook express,
certain
cell
values from "test.xls" in the "output" worksheet.
4. since the workbook is locked i can't add other macro's to
"test.xls",
so
i thought ok i'll just make another workbook called "mail.xls" also
in
my
"c:\mystuff" directory and transfer the "test.xls\output" cell
reference
A10
and whatever other ones i need to email to the new workbook
"mail.xls\sheet1".

5. How i can i do this, what is the exact code that i can use to
make it
happen?

6. This is what i've tried, the mail part works however when the
value
changes to 10000 in test.xls and then also in mail.xls it doesn't
work,
ie
if i actually go into the mail.xls\sheet1 and do a change like
delete or
add
something then the macro will fire, otherwise it won't do anything;
is
Worksheet_Change the right 1 to use to fire the macro when it's
changed
automatically by a cell reference to another workbook test.xls?????

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A10").Value = 10000# And Range("B2").Value < "Contacted"
Then
Call Mail_Text_in_Body
Range("B2").Value = "Contacted"
End If
End Sub

Sub Mail_Text_in_Body()
'In 2002 I can go to 600-700 characters
Dim Msg As String, URL As String
Dim Recipient As String, Subj As String, HLink As String

Recipient = "
Subj = "mail test"
Msg = Range("a6")

HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s", True
End Sub

7. so then i thought ok, mabye i should reference the 10000 directly
from
the test.xls sheet but i don't know how to do that part? = this
doesn't
work

If Workbooks("C:\mystuff\LottoStatisticsXLp.xls
Output").Range("A10").Value
= 10000# Then

Thanks Gurus

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

email it to here =

thanks

"Dave Peterson" wrote in message
...
How about I email you my test workbook.

If that's not ok, then delete it. (It's on it's way.)

harry wrote:

how bout i email you the small excel file and you can see what i did?

"Dave Peterson" wrote in message
...
When you inserted the Class module, what was it called?

(You did insert the class module, right? Not just module, not a
userform,...)

If it wasn't named Class1, then click on it and hit F4 and rename it
to
Class1
in the properties window.

harry wrote:

wow, for an idiot like me that's alot, but i tried to follow it and
after
compiling it got this error message=

for=
Dim myWKSChange As Class1

"compile error:
User-defined type not defined

"Dave Peterson" wrote in message
...
I think that if you can't get to the project, then you're only
choice
is
to have
an application event lurking in the background checking every
change
you
make to
every cell in every worksheet.

If it sees a change to a cell that's not in the correct workbook,
or
not
on the
correct worksheet or not in A10, then it just quits.

If you think you like this idea, then
create a new workbook
alt-F11 to get to the VBE.
find your new workbook/project and select it.
then Insert|Class module from the menubar.

Paste this in that class module code window:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

If Target.Cells.Count 1 Then Exit Sub
If LCase(Sh.Parent.FullName) _
< LCase("C:\mystuff\LottoStatisticsXLp.xls") Then
Exit
Sub
If LCase(Sh.Name) < LCase("output") Then Exit Sub

If Intersect(Target, Range("A10")) Is Nothing Then Exit Sub

If Target.Value = 10000# _
And Range("B2").Value < "Contacted" Then
Call Mail_Text_in_Body
Application.EnableEvents = False
Range("B2").Value = "Contacted"
Application.EnableEvents = True
End If

End Sub

Note that it's pretty specific about the name/location of the
workbook.
If you
wanted you could just look at the .name (and toss the drive/folder
bits)
like:

if lcase(sh.parent.name) < lcase("lottostatisticsxlp.xls") then
exit
sub


Now doubleclick on the ThisWorkbook object. Paste this into the
code
window:

Option Explicit
Dim myWKSChange As Class1
Private Sub Workbook_Open()
Set myWKSChange = New Class1
Set myWKSChange.xlApp = Application
End Sub
Private Sub workbook_close()
Set myWKSChange.xlApp = Nothing
End Sub

This tells excel to look for changes in every worksheet.

Then add your code for mail_text_in_body to a General module.

Save this workbook, close it and reopen it (or just run the
workbook_open
code).

When I tested, I commented out the part that really sent the
message:
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%s", True


If you want to learn more about application events, then Chip
Pearson
has
some
nice notes at:
http://www.cpearson.com/excel/AppEvent.htm


======
Another alternative would be to go back to the developer and
bribing
him/her
with coffee(?) to unprotect the project so that you could add your
code.

harry wrote:

Hi,

i have 2 workbooks, 1 is password protected so i can't make
other
macro's on
it. Using excel 2002 btw + winxp all sp's etc.

This is want i want to do:

1. Password protected workbook has changing cell values when
macros
in
it
are running. Call this workbook ="test.xls", it's in my
"c:\mystuff"
directory
2. I want to be able to do this when cell A10 in "test.xls" in a
worksheet
called "output" is = 10000 then
3. I want to automatically email once, using outlook express,
certain
cell
values from "test.xls" in the "output" worksheet.
4. since the workbook is locked i can't add other macro's to
"test.xls",
so
i thought ok i'll just make another workbook called "mail.xls"
also
in
my
"c:\mystuff" directory and transfer the "test.xls\output" cell
reference
A10
and whatever other ones i need to email to the new workbook
"mail.xls\sheet1".

5. How i can i do this, what is the exact code that i can use to
make it
happen?

6. This is what i've tried, the mail part works however when the
value
changes to 10000 in test.xls and then also in mail.xls it
doesn't
work,
ie
if i actually go into the mail.xls\sheet1 and do a change like
delete or
add
something then the macro will fire, otherwise it won't do
anything;
is
Worksheet_Change the right 1 to use to fire the macro when it's
changed
automatically by a cell reference to another workbook
test.xls?????

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A10").Value = 10000# And Range("B2").Value <
"Contacted"
Then
Call Mail_Text_in_Body
Range("B2").Value = "Contacted"
End If
End Sub

Sub Mail_Text_in_Body()
'In 2002 I can go to 600-700 characters
Dim Msg As String, URL As String
Dim Recipient As String, Subj As String, HLink As String

Recipient = "
Subj = "mail test"
Msg = Range("a6")

HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s", True
End Sub

7. so then i thought ok, mabye i should reference the 10000
directly
from
the test.xls sheet but i don't know how to do that part? = this
doesn't
work

If Workbooks("C:\mystuff\LottoStatisticsXLp.xls
Output").Range("A10").Value
= 10000# Then

Thanks Gurus

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

You've got two on the way, now.

harry wrote:

email it to here =

thanks

"Dave Peterson" wrote in message
...
How about I email you my test workbook.

If that's not ok, then delete it. (It's on it's way.)

harry wrote:

how bout i email you the small excel file and you can see what i did?

"Dave Peterson" wrote in message
...
When you inserted the Class module, what was it called?

(You did insert the class module, right? Not just module, not a
userform,...)

If it wasn't named Class1, then click on it and hit F4 and rename it
to
Class1
in the properties window.

harry wrote:

wow, for an idiot like me that's alot, but i tried to follow it and
after
compiling it got this error message=

for=
Dim myWKSChange As Class1

"compile error:
User-defined type not defined

"Dave Peterson" wrote in message
...
I think that if you can't get to the project, then you're only
choice
is
to have
an application event lurking in the background checking every
change
you
make to
every cell in every worksheet.

If it sees a change to a cell that's not in the correct workbook,
or
not
on the
correct worksheet or not in A10, then it just quits.

If you think you like this idea, then
create a new workbook
alt-F11 to get to the VBE.
find your new workbook/project and select it.
then Insert|Class module from the menubar.

Paste this in that class module code window:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

If Target.Cells.Count 1 Then Exit Sub
If LCase(Sh.Parent.FullName) _
< LCase("C:\mystuff\LottoStatisticsXLp.xls") Then
Exit
Sub
If LCase(Sh.Name) < LCase("output") Then Exit Sub

If Intersect(Target, Range("A10")) Is Nothing Then Exit Sub

If Target.Value = 10000# _
And Range("B2").Value < "Contacted" Then
Call Mail_Text_in_Body
Application.EnableEvents = False
Range("B2").Value = "Contacted"
Application.EnableEvents = True
End If

End Sub

Note that it's pretty specific about the name/location of the
workbook.
If you
wanted you could just look at the .name (and toss the drive/folder
bits)
like:

if lcase(sh.parent.name) < lcase("lottostatisticsxlp.xls") then
exit
sub


Now doubleclick on the ThisWorkbook object. Paste this into the
code
window:

Option Explicit
Dim myWKSChange As Class1
Private Sub Workbook_Open()
Set myWKSChange = New Class1
Set myWKSChange.xlApp = Application
End Sub
Private Sub workbook_close()
Set myWKSChange.xlApp = Nothing
End Sub

This tells excel to look for changes in every worksheet.

Then add your code for mail_text_in_body to a General module.

Save this workbook, close it and reopen it (or just run the
workbook_open
code).

When I tested, I commented out the part that really sent the
message:
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%s", True


If you want to learn more about application events, then Chip
Pearson
has
some
nice notes at:
http://www.cpearson.com/excel/AppEvent.htm


======
Another alternative would be to go back to the developer and
bribing
him/her
with coffee(?) to unprotect the project so that you could add your
code.

harry wrote:

Hi,

i have 2 workbooks, 1 is password protected so i can't make
other
macro's on
it. Using excel 2002 btw + winxp all sp's etc.

This is want i want to do:

1. Password protected workbook has changing cell values when
macros
in
it
are running. Call this workbook ="test.xls", it's in my
"c:\mystuff"
directory
2. I want to be able to do this when cell A10 in "test.xls" in a
worksheet
called "output" is = 10000 then
3. I want to automatically email once, using outlook express,
certain
cell
values from "test.xls" in the "output" worksheet.
4. since the workbook is locked i can't add other macro's to
"test.xls",
so
i thought ok i'll just make another workbook called "mail.xls"
also
in
my
"c:\mystuff" directory and transfer the "test.xls\output" cell
reference
A10
and whatever other ones i need to email to the new workbook
"mail.xls\sheet1".

5. How i can i do this, what is the exact code that i can use to
make it
happen?

6. This is what i've tried, the mail part works however when the
value
changes to 10000 in test.xls and then also in mail.xls it
doesn't
work,
ie
if i actually go into the mail.xls\sheet1 and do a change like
delete or
add
something then the macro will fire, otherwise it won't do
anything;
is
Worksheet_Change the right 1 to use to fire the macro when it's
changed
automatically by a cell reference to another workbook
test.xls?????

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A10").Value = 10000# And Range("B2").Value <
"Contacted"
Then
Call Mail_Text_in_Body
Range("B2").Value = "Contacted"
End If
End Sub

Sub Mail_Text_in_Body()
'In 2002 I can go to 600-700 characters
Dim Msg As String, URL As String
Dim Recipient As String, Subj As String, HLink As String

Recipient = "
Subj = "mail test"
Msg = Range("a6")

HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s", True
End Sub

7. so then i thought ok, mabye i should reference the 10000
directly
from
the test.xls sheet but i don't know how to do that part? = this
doesn't
work

If Workbooks("C:\mystuff\LottoStatisticsXLp.xls
Output").Range("A10").Value
= 10000# Then

Thanks Gurus

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson