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
Solved

Replace hyperlink

Posted on 2009-07-10
11
266 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Outlook 2012 VBA: Object missing 14 41
Outlook 2013 sent view customization 2 26
exchange, outlook 20 44
Importing contacts into Office 365 Outlook 12 50
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
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…

856 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