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

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