Need syntax for adding a hyperlink thru VBA

[Drdave1958]

Here's what I got...

code:
Sub TestHyper()
myfile = "Daves.jpg"
mylink = "DavesLink"
Path = "(\\Servername\Foldername\"
ActiveCell.Hyperlink.Add Path & myfile & mylink
End Sub

I've searched the site and the help file but I'm still going in circles. Will someone steer me in the right direction?

Thanks, Dave

 

[TommyGun]

This will create a Hyperlink to another XL workbook, see if you can edit this "skeleton" to your needs...

ActiveCell.Hyperlinks.Add ActiveCell, "C:\VBA Code\Book1.xls"

 

[Drdave1958]

Thanks, that works!

Here's what I used:
ActiveCell.Hyperlinks.Add ActiveCell, MyPath & NewJpg.
(MyPath and MyJpg being the Path and filename.)

The last thing I'd like to do is name this hyperlink with my variable "MyLink" so the Hyperlink doesn't show the full path. How do I do this?


Thanks, Dave

 

[TommyGun]

Try adding a Hyperlink formula instead of a hyperlink object...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> AddHyperlinkFormula()
    <SPAN style="color:#00007F">Dim</SPAN> MyPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, FriendlyName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

    MyPath = "C:\VBA Code\"
    MyFile = "Book11.xls"
    FriendlyName = "GetThis!"

    ActiveCell.Formula = "=HYPERLINK(""" & MyPath & MyFile & """,""" & FriendlyName & """)"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Drdave1958]

Worked Great!
That's what I tried to do in the first place but could'nt get the syntax.

Thanks, Dave

 

[sdk]

I have tried the same but I am having some problem with the syntax:

Sheets("sample").Cells(Eventid, 7).Formula = "HYPERLINK(""[URL]http://finance.yahoo.com/q/hp?s[/URL]=" & Companyid & "&a=00&b=1&c=1998&d=01&e=1&f=2009&g=d"")"

Not sure about the "" and ) etc, it just says HYPERLINK in each cell now.

 

[sdk]

Fixed it:D

 Sheets("sample").Cells(Eventid, 7).Formula = "=HYPERLINK(""[URL]http://finance.yahoo.com/q/hp?s[/URL]=" & Companyid & "&a=00&b=1&c=1998&d=01&e=1&f=2009&g=d"",""" & Companyid & """)"

 

[treygon]

I know this is an old post that's been revived, but how would I apply this concept to using a variable inside of a hyperlink I am trying to add to the body of an email I am sending with Excel.

Basically, I want to insert a hyperlink to a pdf that is on our server into the body of an email, using a variable as part of the file name, and have the link show as "Click here" (rather than the whole file name).

Here is my code:

Dim myOutlook As Object
Dim myMailItem As Object

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)

filename = myreport.pdf

With otlNewMail
.To = emailRecipient
.Subject = "new trade ticket for approval"
.htmlBody = "To view this file, please" & "<# href=""file://\\292.568.8.1\willow4\Documents\reports\quarter\" & filename"">Click here<!--#-->"
.Display

(I change the to link tags to # so the post wouldn't make this an actual link.)

VB gives me a error that looks like this: "Compile Error - Expected: End of Statement"
Hopefully that makes sense. I have been trying to find a solutions for days! Please help!
Thanks.

 

[excelsupernerd]

I added this specifically in Outlook code to access Word, but it's just one layer separated from Excel. Should just have to add the references to Word and Outlook Object Model and make small tweaks.

Sub insertMyLink()
    Const MY_LINK_URL As String = "http://www.youtube.com/excelsupernerd"
    
    Dim inspActive As Inspector
    Dim itmCurrent
    Dim mitmCurr As MailItem
    
    Set inspActive = ThisOutlookSession.ActiveInspector
    Set itmCurrent = inspActive.CurrentItem
    
    ' Check if current item is a MailItem
    If TypeName(itmCurrent) = "MailItem" Then
        Set mitmCurr = itmCurrent
    Else
        MsgBox "The current item is not a MailItem"
        End
    End If
    
    ' Check active inspector's editor
    If inspActive.EditorType = olEditorWord Then
        Dim wrdDoc As Word.Document
        Set wrdDoc = inspActive.WordEditor
        Dim wrdSel As Word.Selection
        Set wrdSel = wrdDoc.Windows(1).Selection
        Dim wrdRang As Range
        
        If mitmCurr.BodyFormat = olFormatHTML _
        Or mitmCurr.BodyFormat = olFormatRichText Then
            ' Insert link
            On Error Resume Next
            'wrdSel.InsertAfter (MY_LINK_URL)
            'wrdSel = wrdDoc.Windows(1).Selection
            wrdSel.Hyperlinks.Add _
                Anchor:=wrdSel.Range, _
                Address:=MY_LINK_URL, _
                ScreenTip:="My YouTube Page", _
                TextToDisplay:="ExcelSupernerd YouTube page"
        Else
            ' Insert link text
            wrdSel.InsertAfter MY_LINK_URL
        End If
    
    ' Other types not supported yet
'    ElseIf inspActive.EditorType = olEditorHTML Then
'    ElseIf inspActive.EditorType = olEditorRTF Then
'    ElseIf inspActive.EditorType = olEditorText Then
    Else
        MsgBox "Editors other than Word (Outlook's default) are not supported yet."
        End
    End If
    
End Sub

 

[thomas398]

I need to set internal links between cells. Is there a "HYPERLINK" type formula that does this?