Solved

Replace hyperlink

Posted on 2009-07-10
11
261 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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
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 …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now