hyperlink email challenge

[Guest]

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 Ferrero]

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/

 

[Guest]

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]

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/

 

[Guest]

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

thanks!

Brendan

 

[Ed Ferrero]

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/

 

[Guest]

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

 

[Guest]

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]

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/

 

[Guest]

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

send them to: (e-mail address removed)

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

 

[Guest]

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

 

[Ed Ferrero]

Hi brendan,

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