Solved

Replace hyperlink

Posted on 2009-07-10
11
267 Views
Last Modified: 2012-05-07
Dear BlueDevilFan,

I hope that you will be able to help with this one as well, when the email is sent out, the text from the notes does this to any email address which is in the text
 HYPERLINK "mailto:ed.macey@iptdesign.co.uk" ed.macey@iptdesign.co.uk
are you able to remedy this as quickly as you did with the line breaks?

Thanks Ed.
Private Sub project_Write(Cancel As Boolean)
Dim projectNotes As String
projectNotes = project.Body
If originalprojectNotes <> projectNotes Then
        originalprojectNotes = projectNotes
        Set mail = Application.CreateItem(olMailItem)
        mail.Subject = "" & project.Subject & " notes have been updated"
        mail.HTMLBody = "<font face='arial' colour='#000080' size='2'><p>" & project.Subject & " has been updated. The current notes are:</p><p> " & Replace(projectNotes, vbCrLf, "<br>") & "</p>><p><font size = '1'>V. 1 - /4501/</p>"
        mail.To = "edmacey@iptdesign.co.uk"
        mail.Send
        End If

Open in new window

0
Comment
Question by:edmacey
  • 6
  • 5
11 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 24823119
Hi, edmacey.

I take it that the link is part of the text in the projectNotes variable.  Is that correct?
0
 

Author Comment

by:edmacey
ID: 24823149
Yes, sorry, it is form the projectNotes variable.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24823239
Will the address always be your address?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:edmacey
ID: 24823261
no the notes change so it's just every occasion whenever those notes have an email address in, it does that. the notes are for various different tasks.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24823371
Ok.  Let me see what I can figure out.
0
 

Author Comment

by:edmacey
ID: 24861090
I've thought of a way of fixing this if you can write into the code to delete HYPERLINK and then "this@email.com" then it would get rid of the offending text. Whatever the email address is it is always surrounded by " " so these can be the markers. What do you think?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24867889
So look for the second double quote (i.e. ") after HYPERLINK.  Delete from the beginning of HYPERLINK through the double quote, right?
0
 

Author Comment

by:edmacey
ID: 24867926
yeah exactly that. HYPERLINK "mailto:ed.macey@iptdesign.co.uk" ed.macey@iptdesign.co.uk so that just the second instance of the email address is left, hyperlink and everything contained in the double quotes.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 24871267
Try this, Ed.  Add it to the code you  already have.  Call the code by changing line #8 from

        mail.HTMLBody = "<font face='arial' colour='#000080' size='2'><p>" & project.Subject & " has been updated. The current notes are:</p><p> " & Replace(projectNotes, vbCrLf, "<br>") & "</p>><p><font size = '1'>V. 1 - /4501/</p>"

to

        mail.HTMLBody = "<font face='arial' colour='#000080' size='2'><p>" & project.Subject & " has been updated. The current notes are:</p><p> " & Replace(RemoveHyperlink(projectNotes), vbCrLf, "<br>") & "</p>><p><font size = '1'>V. 1 - /4501/</p>"


   
Function RemoveHyperlink(strValue As String) As String
    Dim lngPos1 As Long, lngPos2 As Long, strTemp As String
    lngPos1 = InStr(1, strValue, "HYPERLINK")
    If lngPos1 > 0 Then
        lngPos2 = InStr(lngPos1, strValue, Chr(34))
        lngPos2 = InStr(lngPos2 + 1, strValue, Chr(34))
        strTemp = Mid(strValue, lngPos1, (lngPos2 - lngPos1) + 1)
        RemoveHyperlink = Replace(strValue, strTemp, "")
    Else
        RemoveHyperlink = strValue
    End If
End Function

Open in new window

0
 

Author Closing Comment

by:edmacey
ID: 31602014
Thanks again, this works fantastically
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24877028
You're welcome, Ed.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question