H
harry
Excel Gurus = want a macro in 1 workbook to get info from another workbook =
Read please
Hi,
i have 2 workbooks, 1 the macro's are 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 = "(e-mail address removed)"
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
Read please
Hi,
i have 2 workbooks, 1 the macro's are 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 = "(e-mail address removed)"
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