CODEWITHSUNDEEP
vbaexceloutlook
Nevasadie
Nevasadie

Reputation: 3

Add a hyperlink to excel worksheet using outlook VBA

I receive a large number of emails with attachments. The attachments need to be saved, edited to add a reference (programmatically), have that new reference number logged, and the saved attachment forwarded on to various addressees. I've got all this working in outlook vba but, to make auditing easier, I need to add a hyperlink in the cell in the log that contains the newly added reference number, so I can quickly navigate to the newly saved file.

I've searched thoroughly but no joy, and tried:

        With LogObj.Sheets("Log")
        .Hyperlinks.Add Anchor:=LogObj.Sheets("Log").Cells(LastOrderRow, 3), Address:="",   SubAddress:= _
            "SavePath", TextToDisplay:=LogObj.Sheets("Log").Cells(LastOrderRow, 3)
        End With

I've tried:

        LogObj.Sheets("Log").Cells(LastOrderRow, 3) = _
        WorksheetFunction.Hyperlink(savePath, LogObj.Sheets("Log").Cells(LastOrderRow, 3))

In desperation I tried being literal:

        OrderRef = "LogObj.Sheets("Log").Cells(LastOrderRow, 3)"
        LogObj.Sheets("Log").Cells(LastOrderRow, 3) = _
        WorksheetFunction.Hyperlink(""" & SavePath & """, OrderRef)

and many variations within the above, including adding application.worksheetfunction.

If anybody knows the correct syntax to get outlook vba to do this in excel, I'd be very grateful if they'd share it.

Many thanks.

Neva

Upvotes: 0

Views: 476

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33175

The path to the file should go in the Address. This worked for me. Make sure you're path is valid.

With LogObj.Sheets("Log")
    Set rNext = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    rNext.Value = lReference
    .Hyperlinks.Add Anchor:=rNext, Address:=sSavePath, TextToDisplay:=CStr(lReference)
End With

Upvotes: 1

Related Questions