?
Solved

Replace hyperlink

Posted on 2009-07-10
11
Medium Priority
?
275 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
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…
Suggested Courses

743 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