hyperlink email challenge
 

I currently have a workbook that I use to build a new part within my
business. It contains about 12 worksheets and each work sheet has 3 email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in all. and
in each subject line of the hyperlink I have the part number listed.

However, when I use this workbook as a template, I want the subject line to
of the hyperlink emails to reflect a new part number for the new workbook. I
would love to do a mass find and replace, but it doesn't work in the
hyperlink - what can I do?

thanks for any help.

Brendan

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part within my
business. It contains about 12 worksheets and each work sheet has 3 email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in all.
and
in each subject line of the hyperlink I have the part number listed.

However, when I use this workbook as a template, I want the subject line
to
of the hyperlink emails to reflect a new part number for the new workbook.
I
would love to do a mass find and replace, but it doesn't work in the
hyperlink - what can I do?

thanks for any help.

Brendan

Ed thank you, but I am now getting an error with the line "hLink.email......"

what's going on? I am not big on VBA so if there are variables that I need
to modify, please help!

thanks!

"Ed Ferrero" wrote:

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part within my
business. It contains about 12 worksheets and each work sheet has 3 email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in all.
and
in each subject line of the hyperlink I have the part number listed.

However, when I use this workbook as a template, I want the subject line
to
of the hyperlink emails to reflect a new part number for the new workbook.
I
would love to do a mass find and replace, but it doesn't work in the
hyperlink - what can I do?

thanks for any help.

Brendan

Hi brendan,

What error are you getting? What version of Excel do you use?
You can email the workbook to me if you like and I will try to sort it out.

Ed Ferrero
http://edferrero.m6.net/

Ed thank you, but I am now getting an error with the line
"hLink.email......"

what's going on? I am not big on VBA so if there are variables that I
need
to modify, please help!

thanks!

"Ed Ferrero" wrote:

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part within my
business. It contains about 12 worksheets and each work sheet has 3
email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in all.
and
in each subject line of the hyperlink I have the part number listed.

However, when I use this workbook as a template, I want the subject
line
to
of the hyperlink emails to reflect a new part number for the new
workbook.
I
would love to do a mass find and replace, but it doesn't work in the
hyperlink - what can I do?

thanks for any help.

Brendan

Ed, I took you up on your offer and sent you my workbook. Any help would be
greatly appreciated!

thanks!

Brendan

"Ed Ferrero" wrote:

Hi brendan,

What error are you getting? What version of Excel do you use?
You can email the workbook to me if you like and I will try to sort it out.

Ed Ferrero
http://edferrero.m6.net/

Ed thank you, but I am now getting an error with the line
"hLink.email......"

what's going on? I am not big on VBA so if there are variables that I
need
to modify, please help!

thanks!

"Ed Ferrero" wrote:

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part within my
business. It contains about 12 worksheets and each work sheet has 3
email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in all.
and
in each subject line of the hyperlink I have the part number listed.

However, when I use this workbook as a template, I want the subject
line
to
of the hyperlink emails to reflect a new part number for the new
workbook.
I
would love to do a mass find and replace, but it doesn't work in the
hyperlink - what can I do?

thanks for any help.

Brendan

hyperlink email challenge
 

Have replied separately. The OP had a complex workbook that included both
email hyperlinks and other hyperlinks. So the code needed to be slightly
modified so that it only changed email hyperlinks.

Also, this code checks for the existance of a Part No in the email subject
and changes it if required whilst leaving the rest of the subject unchanged.

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet
Dim sSubjectOld As String
Dim sMsg As String

sSpace = " "
sMsg = " - Part No"

' obtain part number and description from document
sPart = Worksheets("Document Control").Range("PartNo")
sDescr = Worksheets("Document Control").Range("PartDescr")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
If Left(hLink.Address, 6) = "mailto" Then
sSubjectOld = StripOldPartNo(hLink.EmailSubject, sMsg)
hLink.EmailSubject = sSubjectOld & sMsg & sSpace & sPart & sSpace &
sDescr
End If
Next
Next
End Sub

Function StripOldPartNo(ByVal s As String, ByVal sPart As String) As String
Dim i As Integer
i = InStr(1, s, sPart) - 1
If i < 0 Then i = Len(s)
StripOldPartNo = Left(s, i)
End Function

Finally, we can use a Worksheet Change event to run the code whenever the
Part No is changed by the user.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then ' part no has changed
Call EditHyperlinks
End If
End Sub

Ed Ferrero
http://edferrero.m6.net/

"brendan" wrote in message
...
Ed, I took you up on your offer and sent you my workbook. Any help would
be
greatly appreciated!

thanks!

Brendan

"Ed Ferrero" wrote:

Hi brendan,

What error are you getting? What version of Excel do you use?
You can email the workbook to me if you like and I will try to sort it
out.

Ed Ferrero
http://edferrero.m6.net/

Ed thank you, but I am now getting an error with the line
"hLink.email......"

what's going on? I am not big on VBA so if there are variables that I
need
to modify, please help!

thanks!

"Ed Ferrero" wrote:

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part within my
business. It contains about 12 worksheets and each work sheet has 3
email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in
all.
and
in each subject line of the hyperlink I have the part number listed.

However, when I use this workbook as a template, I want the subject
line
to
of the hyperlink emails to reflect a new part number for the new
workbook.
I
would love to do a mass find and replace, but it doesn't work in the
hyperlink - what can I do?

thanks for any help.

Brendan

hyperlink email challenge
 

Ed,

I greatly appreciate your help, but I think that I am doing something wrong.
Being that I am a novice at this, pardon the ignorant questions.

First, I took the macro you wrote, and copied and pasted it into the Macro,
from the line "sub EditHyperlinks()" to the line "End Function" - and when I
run this macro it comes up with the following error: compile error: syntax
error - what's going on ?

second - the the worksheet change event that you have listed below - I love
the idea, but where do I place the code you wrote?

thanks agian for all of the help!

Brendan

"Ed Ferrero" wrote:

Have replied separately. The OP had a complex workbook that included both
email hyperlinks and other hyperlinks. So the code needed to be slightly
modified so that it only changed email hyperlinks.

Also, this code checks for the existance of a Part No in the email subject
and changes it if required whilst leaving the rest of the subject unchanged.

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet
Dim sSubjectOld As String
Dim sMsg As String

sSpace = " "
sMsg = " - Part No"

' obtain part number and description from document
sPart = Worksheets("Document Control").Range("PartNo")
sDescr = Worksheets("Document Control").Range("PartDescr")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
If Left(hLink.Address, 6) = "mailto" Then
sSubjectOld = StripOldPartNo(hLink.EmailSubject, sMsg)
hLink.EmailSubject = sSubjectOld & sMsg & sSpace & sPart & sSpace &
sDescr
End If
Next
Next
End Sub

Function StripOldPartNo(ByVal s As String, ByVal sPart As String) As String
Dim i As Integer
i = InStr(1, s, sPart) - 1
If i < 0 Then i = Len(s)
StripOldPartNo = Left(s, i)
End Function

Finally, we can use a Worksheet Change event to run the code whenever the
Part No is changed by the user.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then ' part no has changed
Call EditHyperlinks
End If
End Sub

Ed Ferrero
http://edferrero.m6.net/

"brendan" wrote in message
...
Ed, I took you up on your offer and sent you my workbook. Any help would
be
greatly appreciated!

thanks!

Brendan

"Ed Ferrero" wrote:

Hi brendan,

What error are you getting? What version of Excel do you use?
You can email the workbook to me if you like and I will try to sort it
out.

Ed Ferrero
http://edferrero.m6.net/

Ed thank you, but I am now getting an error with the line
"hLink.email......"

what's going on? I am not big on VBA so if there are variables that I
need
to modify, please help!

thanks!

"Ed Ferrero" wrote:

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part within my
business. It contains about 12 worksheets and each work sheet has 3
email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in
all.
and
in each subject line of the hyperlink I have the part number listed.

However, when I use this workbook as a template, I want the subject
line
to
of the hyperlink emails to reflect a new part number for the new
workbook.
I
would love to do a mass find and replace, but it doesn't work in the
hyperlink - what can I do?

thanks for any help.

Brendan

hyperlink email challenge
 

Ed,

I tweaked your code just slightly and got it to work! Awesome!!! thanks so
much....but I still don't know where to put the worksheet change function.

Also, did you have an opportunity to view the reporting page?

thanks again!

"Ed Ferrero" wrote:

Have replied separately. The OP had a complex workbook that included both
email hyperlinks and other hyperlinks. So the code needed to be slightly
modified so that it only changed email hyperlinks.

Also, this code checks for the existance of a Part No in the email subject
and changes it if required whilst leaving the rest of the subject unchanged.

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet
Dim sSubjectOld As String
Dim sMsg As String

sSpace = " "
sMsg = " - Part No"

' obtain part number and description from document
sPart = Worksheets("Document Control").Range("PartNo")
sDescr = Worksheets("Document Control").Range("PartDescr")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
If Left(hLink.Address, 6) = "mailto" Then
sSubjectOld = StripOldPartNo(hLink.EmailSubject, sMsg)
hLink.EmailSubject = sSubjectOld & sMsg & sSpace & sPart & sSpace &
sDescr
End If
Next
Next
End Sub

Function StripOldPartNo(ByVal s As String, ByVal sPart As String) As String
Dim i As Integer
i = InStr(1, s, sPart) - 1
If i < 0 Then i = Len(s)
StripOldPartNo = Left(s, i)
End Function

Finally, we can use a Worksheet Change event to run the code whenever the
Part No is changed by the user.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then ' part no has changed
Call EditHyperlinks
End If
End Sub

Ed Ferrero
http://edferrero.m6.net/

"brendan" wrote in message
...
Ed, I took you up on your offer and sent you my workbook. Any help would
be
greatly appreciated!

thanks!

Brendan

"Ed Ferrero" wrote:

Hi brendan,

What error are you getting? What version of Excel do you use?
You can email the workbook to me if you like and I will try to sort it
out.

Ed Ferrero
http://edferrero.m6.net/

Ed thank you, but I am now getting an error with the line
"hLink.email......"

what's going on? I am not big on VBA so if there are variables that I
need
to modify, please help!

thanks!

"Ed Ferrero" wrote:

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part within my
business. It contains about 12 worksheets and each work sheet has 3
email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in
all.
and
in each subject line of the hyperlink I have the part number listed.

However, when I use this workbook as a template, I want the subject
line
to
of the hyperlink emails to reflect a new part number for the new
workbook.
I
would love to do a mass find and replace, but it doesn't work in the
hyperlink - what can I do?

thanks for any help.

Brendan

hyperlink email challenge
 

Hi brendan,

Did you get the files I sent you? They have the code and they do work. I
also changed the reporting page and sent it to you.

The Worksheet Change routine should go in the code pane for the 'Document
Control' sheet. Open the VB Editor, double click the 'Document Control'
sheet and add your code there.

Ed Ferrero
http://edferrero.m6.net/



"brendan" wrote in message
...
Ed,

I tweaked your code just slightly and got it to work! Awesome!!! thanks
so
much....but I still don't know where to put the worksheet change function.

Also, did you have an opportunity to view the reporting page?

thanks again!

"Ed Ferrero" wrote:

Have replied separately. The OP had a complex workbook that included both
email hyperlinks and other hyperlinks. So the code needed to be slightly
modified so that it only changed email hyperlinks.

Also, this code checks for the existance of a Part No in the email
subject
and changes it if required whilst leaving the rest of the subject
unchanged.

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet
Dim sSubjectOld As String
Dim sMsg As String

sSpace = " "
sMsg = " - Part No"

' obtain part number and description from document
sPart = Worksheets("Document Control").Range("PartNo")
sDescr = Worksheets("Document Control").Range("PartDescr")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
If Left(hLink.Address, 6) = "mailto" Then
sSubjectOld = StripOldPartNo(hLink.EmailSubject, sMsg)
hLink.EmailSubject = sSubjectOld & sMsg & sSpace & sPart & sSpace &
sDescr
End If
Next
Next
End Sub

Function StripOldPartNo(ByVal s As String, ByVal sPart As String) As
String
Dim i As Integer
i = InStr(1, s, sPart) - 1
If i < 0 Then i = Len(s)
StripOldPartNo = Left(s, i)
End Function

Finally, we can use a Worksheet Change event to run the code whenever the
Part No is changed by the user.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then ' part no has changed
Call EditHyperlinks
End If
End Sub

Ed Ferrero
http://edferrero.m6.net/

"brendan" wrote in message
...
Ed, I took you up on your offer and sent you my workbook. Any help
would
be
greatly appreciated!

thanks!

Brendan

"Ed Ferrero" wrote:

Hi brendan,

What error are you getting? What version of Excel do you use?
You can email the workbook to me if you like and I will try to sort it
out.

Ed Ferrero
http://edferrero.m6.net/

Ed thank you, but I am now getting an error with the line
"hLink.email......"

what's going on? I am not big on VBA so if there are variables that
I
need
to modify, please help!

thanks!

"Ed Ferrero" wrote:

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part within
my
business. It contains about 12 worksheets and each work sheet
has 3
email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in
all.
and
in each subject line of the hyperlink I have the part number
listed.

However, when I use this workbook as a template, I want the
subject
line
to
of the hyperlink emails to reflect a new part number for the new
workbook.
I
would love to do a mass find and replace, but it doesn't work in
the
hyperlink - what can I do?

thanks for any help.

Brendan

hyperlink email challenge
 

Ed, again, thanks for the help. No, I never recieved the files.

send them to:

I will follow your advice, and please let me know if there is anything from
our website I can send you for your help!

Brendan

"Ed Ferrero" wrote:

Hi brendan,

Did you get the files I sent you? They have the code and they do work. I
also changed the reporting page and sent it to you.

The Worksheet Change routine should go in the code pane for the 'Document
Control' sheet. Open the VB Editor, double click the 'Document Control'
sheet and add your code there.

Ed Ferrero
http://edferrero.m6.net/



"brendan" wrote in message
...
Ed,

I tweaked your code just slightly and got it to work! Awesome!!! thanks
so
much....but I still don't know where to put the worksheet change function.

Also, did you have an opportunity to view the reporting page?

thanks again!

"Ed Ferrero" wrote:

Have replied separately. The OP had a complex workbook that included both
email hyperlinks and other hyperlinks. So the code needed to be slightly
modified so that it only changed email hyperlinks.

Also, this code checks for the existance of a Part No in the email
subject
and changes it if required whilst leaving the rest of the subject
unchanged.

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet
Dim sSubjectOld As String
Dim sMsg As String

sSpace = " "
sMsg = " - Part No"

' obtain part number and description from document
sPart = Worksheets("Document Control").Range("PartNo")
sDescr = Worksheets("Document Control").Range("PartDescr")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
If Left(hLink.Address, 6) = "mailto" Then
sSubjectOld = StripOldPartNo(hLink.EmailSubject, sMsg)
hLink.EmailSubject = sSubjectOld & sMsg & sSpace & sPart & sSpace &
sDescr
End If
Next
Next
End Sub

Function StripOldPartNo(ByVal s As String, ByVal sPart As String) As
String
Dim i As Integer
i = InStr(1, s, sPart) - 1
If i < 0 Then i = Len(s)
StripOldPartNo = Left(s, i)
End Function

Finally, we can use a Worksheet Change event to run the code whenever the
Part No is changed by the user.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then ' part no has changed
Call EditHyperlinks
End If
End Sub

Ed Ferrero
http://edferrero.m6.net/

"brendan" wrote in message
...
Ed, I took you up on your offer and sent you my workbook. Any help
would
be
greatly appreciated!

thanks!

Brendan

"Ed Ferrero" wrote:

Hi brendan,

What error are you getting? What version of Excel do you use?
You can email the workbook to me if you like and I will try to sort it
out.

Ed Ferrero
http://edferrero.m6.net/

Ed thank you, but I am now getting an error with the line
"hLink.email......"

what's going on? I am not big on VBA so if there are variables that
I
need
to modify, please help!

thanks!

"Ed Ferrero" wrote:

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part within
my
business. It contains about 12 worksheets and each work sheet
has 3
email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in
all.
and
in each subject line of the hyperlink I have the part number
listed.

However, when I use this workbook as a template, I want the
subject
line
to
of the hyperlink emails to reflect a new part number for the new
workbook.
I
would love to do a mass find and replace, but it doesn't work in
the
hyperlink - what can I do?

thanks for any help.

Brendan

hyperlink email challenge
 

Ed, I put in the worksheet change where you mentioned and it worked AWESOME!!!

"Ed Ferrero" wrote:

Hi brendan,

Did you get the files I sent you? They have the code and they do work. I
also changed the reporting page and sent it to you.

The Worksheet Change routine should go in the code pane for the 'Document
Control' sheet. Open the VB Editor, double click the 'Document Control'
sheet and add your code there.

Ed Ferrero
http://edferrero.m6.net/



"brendan" wrote in message
...
Ed,

I tweaked your code just slightly and got it to work! Awesome!!! thanks
so
much....but I still don't know where to put the worksheet change function.

Also, did you have an opportunity to view the reporting page?

thanks again!

"Ed Ferrero" wrote:

Have replied separately. The OP had a complex workbook that included both
email hyperlinks and other hyperlinks. So the code needed to be slightly
modified so that it only changed email hyperlinks.

Also, this code checks for the existance of a Part No in the email
subject
and changes it if required whilst leaving the rest of the subject
unchanged.

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet
Dim sSubjectOld As String
Dim sMsg As String

sSpace = " "
sMsg = " - Part No"

' obtain part number and description from document
sPart = Worksheets("Document Control").Range("PartNo")
sDescr = Worksheets("Document Control").Range("PartDescr")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
If Left(hLink.Address, 6) = "mailto" Then
sSubjectOld = StripOldPartNo(hLink.EmailSubject, sMsg)
hLink.EmailSubject = sSubjectOld & sMsg & sSpace & sPart & sSpace &
sDescr
End If
Next
Next
End Sub

Function StripOldPartNo(ByVal s As String, ByVal sPart As String) As
String
Dim i As Integer
i = InStr(1, s, sPart) - 1
If i < 0 Then i = Len(s)
StripOldPartNo = Left(s, i)
End Function

Finally, we can use a Worksheet Change event to run the code whenever the
Part No is changed by the user.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then ' part no has changed
Call EditHyperlinks
End If
End Sub

Ed Ferrero
http://edferrero.m6.net/

"brendan" wrote in message
...
Ed, I took you up on your offer and sent you my workbook. Any help
would
be
greatly appreciated!

thanks!

Brendan

"Ed Ferrero" wrote:

Hi brendan,

What error are you getting? What version of Excel do you use?
You can email the workbook to me if you like and I will try to sort it
out.

Ed Ferrero
http://edferrero.m6.net/

Ed thank you, but I am now getting an error with the line
"hLink.email......"

what's going on? I am not big on VBA so if there are variables that
I
need
to modify, please help!

thanks!

"Ed Ferrero" wrote:

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part within
my
business. It contains about 12 worksheets and each work sheet
has 3
email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36 in
all.
and
in each subject line of the hyperlink I have the part number
listed.

However, when I use this workbook as a template, I want the
subject
line
to
of the hyperlink emails to reflect a new part number for the new
workbook.
I
would love to do a mass find and replace, but it doesn't work in
the
hyperlink - what can I do?

thanks for any help.

Brendan

hyperlink email challenge
 

Hi brendan,

Glad to hear it. I have also re-sent the files to your private address.

Ed, I put in the worksheet change where you mentioned and it worked
AWESOME!!!

"Ed Ferrero" wrote:

Hi brendan,

Did you get the files I sent you? They have the code and they do work. I
also changed the reporting page and sent it to you.

The Worksheet Change routine should go in the code pane for the 'Document
Control' sheet. Open the VB Editor, double click the 'Document Control'
sheet and add your code there.

Ed Ferrero
http://edferrero.m6.net/



"brendan" wrote in message
...
Ed,

I tweaked your code just slightly and got it to work! Awesome!!!
thanks
so
much....but I still don't know where to put the worksheet change
function.

Also, did you have an opportunity to view the reporting page?

thanks again!

"Ed Ferrero" wrote:

Have replied separately. The OP had a complex workbook that included
both
email hyperlinks and other hyperlinks. So the code needed to be
slightly
modified so that it only changed email hyperlinks.

Also, this code checks for the existance of a Part No in the email
subject
and changes it if required whilst leaving the rest of the subject
unchanged.

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet
Dim sSubjectOld As String
Dim sMsg As String

sSpace = " "
sMsg = " - Part No"

' obtain part number and description from document
sPart = Worksheets("Document Control").Range("PartNo")
sDescr = Worksheets("Document Control").Range("PartDescr")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
If Left(hLink.Address, 6) = "mailto" Then
sSubjectOld = StripOldPartNo(hLink.EmailSubject, sMsg)
hLink.EmailSubject = sSubjectOld & sMsg & sSpace & sPart &
sSpace &
sDescr
End If
Next
Next
End Sub

Function StripOldPartNo(ByVal s As String, ByVal sPart As String) As
String
Dim i As Integer
i = InStr(1, s, sPart) - 1
If i < 0 Then i = Len(s)
StripOldPartNo = Left(s, i)
End Function

Finally, we can use a Worksheet Change event to run the code whenever
the
Part No is changed by the user.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then ' part no has changed
Call EditHyperlinks
End If
End Sub

Ed Ferrero
http://edferrero.m6.net/

"brendan" wrote in message
...
Ed, I took you up on your offer and sent you my workbook. Any help
would
be
greatly appreciated!

thanks!

Brendan

"Ed Ferrero" wrote:

Hi brendan,

What error are you getting? What version of Excel do you use?
You can email the workbook to me if you like and I will try to sort
it
out.

Ed Ferrero
http://edferrero.m6.net/

Ed thank you, but I am now getting an error with the line
"hLink.email......"

what's going on? I am not big on VBA so if there are variables
that
I
need
to modify, please help!

thanks!

"Ed Ferrero" wrote:

HI brendan,

Run this macro on your workbook whenever the part number changes

Sub EditHyperlinks()
Dim sSpace As String
Dim sPart As String
Dim hLink As Hyperlink
Dim oSht As Worksheet

sSpace = "%20"

' change this to the range that contains the part number
sPart = ActiveSheet.Range("A1")

For Each oSht In ActiveWorkbook.Worksheets
For Each hLink In oSht.Hyperlinks
hLink.EmailSubject = "Part" & sSpace & "No" & sSpace & sPart
Next
Next
End Sub

Ed Ferrero
http://edferrero.m6.net/

I currently have a workbook that I use to build a new part
within
my
business. It contains about 12 worksheets and each work sheet
has 3
email
hyperlinks connected to it.

Each email hyperlink happens at the end of a specific task, 36
in
all.
and
in each subject line of the hyperlink I have the part number
listed.

However, when I use this workbook as a template, I want the
subject
line
to
of the hyperlink emails to reflect a new part number for the
new
workbook.
I
would love to do a mass find and replace, but it doesn't work
in
the
hyperlink - what can I do?

thanks for any help.

Brendan