Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Replace hyperlink

Posted on 2009-07-10
11
Medium Priority
?
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Mailbox Overload?
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

636 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