Add Hyperlink with an Alias

[RomeERome]

Hello,

I have a spreadsheet that sends an email after the file has been uploaded to a SharePoint sight. The email has a hyperlink to the SharePoint site address to where the file was uploaded.

I want to give that hyperlink an alias so that the email will look alot neater than it does now. My code works, but the syntax is what I need to create the alias.

I know that you can use code similar to what's below.

Hyperlinks.Add Anchor:=" ", Address:=" ", TexttoDisplay:=" "

The code above doesn't work in my code. Am I missing a reference or what?

Here is a sample of my code:

    strHyper = "[URL unfurl="true"]http://s.corporate.s.local/sites/team/S/Forms/AllItems.aspx"[/URL]
    
    ESubject = "Daily Report for " & strPeriod
    EFrom = " "
    ETo = "Name"
    'Use below for multiple names 
    'ETo = "Names"
    'ECc = "Names"
    EBcc = " "
           
    If Format(Time, "hhmm") < 1200 Then
        EBody = "Good Morning All," & vbCrLf & vbCrLf & _
        "A refreshed version of your report for " & strPeriod & " day " & strPerDay & " has been posted to the link below:" & _
        vbCrLf & vbCrLf & _
        strHyper & vbCrLf & vbCrLf & _
        "Automated Reporting System" & vbCrLf & _
        "[URL unfurl="true"]www.s.com"[/URL]
    Else
        EBody = "Good Afternoon All," & vbCrLf & vbCrLf & _
        "A refreshed version of your report for " & strPeriod & " day " & strPerDay & " has been posted to the link below:" & _
        vbCrLf & vbCrLf & _
        strHyper & vbCrLf & vbCrLf & _
        "Automated Reporting System" & vbCrLf & _
        "[URL unfurl="true"]www.s.com"[/URL] 
    End If

    On Error GoTo debugs
    Set MObj = CreateObject("Outlook.Application")
    Set MSingle = MObj.CreateItem(0)
    With MSingle
        .Subject = ESubject
        .To = ETo
        .cc = ECc
        .BCC = EBcc
        .Body = EBody
        .send
    End With

Anyone's help is greatly appreciated.

 

[TonyJollans]

The Anchor parameter needs to be a Range. You can't just anchor your hyperlink to an arbitrary space.


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[RomeERome]

Tony,

Thanks for your reply, but I'm not trying to add a hyperlink to an excel spreadsheet, but I am trying to just make my hyperlink display other text rather than the full address to the SharePoint site that my file has been posted to.

 

[Andrzejek]

Just a guess here, but check the format of the Body. It may be just simple text, so you may want to change it to HTML format or something else that will allow you to play with hyperlinks.

And just a side note, consider:

If Format(Time, "hhmm") < 1200 Then
    EBody = "Good Morning All," 
Else
    EBody = "Good Afternoon All," 
End If

EBody = EBody & vbCrLf & vbCrLf & _
    "A refreshed version of your report for " & strPeriod & " day " & strPerDay & " has been posted to the link below:" & _
    vbCrLf & vbCrLf & _
    strHyper & vbCrLf & vbCrLf & _
    "Automated Reporting System" & vbCrLf & _
    "[URL unfurl="true"]www.s.com"[/URL]

It is just me, but I hate the same code in more than one place.

Have fun.

---- Andy

 

[TonyJollans]

I'm not trying to add a hyperlink ...

Yet the title of your question is "Add Hyperlink with an Alias"

If you want to change an existing hyperlink, you need to identify it first.

It seems to me (an I'm not an Outlook person) that you are just adding the text and something else - after your code has finished, probably - is converting it into a hyperlink. If you want to control the hyperlink, you must create it yourself.

You don't say what version of Outlook are you using. If it is 2007 or 2010 you should be able to use the Word editor to create the content. Whatever version, it should be possible to use HTML format and use the HTMLBody instead of the Body and put the hyperlink in HTML syntax.



Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[RomeERome]

Thanks Andrzejek, I took your advise about the if statement.

Tony,

Check out my code again. The hyperlink is the very first line of the code that I displayed, and it has been assigned to the strHyper variable. I'm using OutLook 2010.

I'll look around for the HTMLBody syntax. Will I have to change my objects in order for the HTML code to work. I've never used this, but it looks interesting to try.

Thanks again Tony.

 

[TonyJollans]

I think you'd be better off writing out the e-mail using the power of the Word Object Model. Somewhere inside the 'With MSingle' block, add

[tt][blue] Set EMailBody = MSingle.GetInspector.WordEditor[/blue][/tt]

This gives you EMailBody as a Word Document Object, so you can add your text to the Range and use Hyperlinks.Add, etc. - instead of setting the body up as a string, something like ...

[blue]With EMailBody.Range
    .InsertAfter "Good Afternoon ..."
    .InsertParagraphAfter
    .InsertAfter "A refreshed version ..."
    .Hyperlinks.Add EMailBody.Range(EMailBody.Range.End - 1, EMailBody.Range.End), strhyper, , , "Your text to display"
    .InsertParagraphAfter
    .InsertAfter "Automated ..."
End With[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website