Insert dynamic hyperlink from cell reference and place it above a copied range

Question

I'm running a report that gets distributed via email. In the email is a hyperlink to the report and a range of cells copied out of it as a snapshot of the report content. I'm trying to automate and found some VBA, but I'm not a programmer and can't modify it for my needs.

The VBA below gets me most of the way, but for 2 shortcomings:

1) I need the hyperlink to point to the specific file I'm referencing in the email, which changes daily (i.e. a unique workbook is created). The below uses a static hyperlink. I was trying to figure out a way to derive the hyperlink from a cell reference.

2) When copying the hyperlink and range of cells from excel into the email, I need the cells below the hyperlink. The below puts the range above the hyperlink.

I'd like to preserve the approach taken in the below VBA of referencing a worksheet to derive the email. It appears easy to deploy on other reports which get distributed.

Sub CreateMail()

    Dim rngSubject As Range
    Dim rngTo As Range
    Dim rngCc As Range
    Dim rngBody As Range
    Dim objOutlook As Object
    Dim objMail As Object

    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)
        With ActiveSheet
        Set rngTo = .Range("B1")
        Set rngCc = .Range("B3")
        Set rngSubject = .Range("B2")
        Set rngBody = .Range("H6:K22")

    End With
    rngBody.Copy
     With objMail
        .Body = "Please click on the link below..." & vbCrLf & "rngBody.Paste" & vbCrLf & _
"file:\\dbd03\nccode\Router_Proc\04Routing.txt"
    End With
    With objMail
        .To = rngTo
        .Cc = rngCc
        .Subject = rngSubject

        .Display
    End With
    SendKeys "^({v})", True

    Set objOutlook = Nothing
    Set objMail = Nothing

score 1 · Accepted Answer · edited May 23 '17 at 10:27

1

1) To make the file link dynamic, you can just include the reference of the cell, containing the file name, in the file path.

"<file:\\dbd03\nccode\Router_Proc\" & _
        ActiveSheet.Range(<cell address here>) & ">"

Note: You might want to also check to make sure the path exists (like this) before putting it in the email

2) To paste the cells below the hyperlink, you can use another SendKeys combination to simulate the pressing of Ctrl + End, which will place the cursor at the end of the email. Doing this before using SendKeys to simulate the Ctrl + V should paste the range of cells after your body text. Your updated code should be the following:

With objMail
    .To = rngTo
    .Cc = rngCc
    .Subject = rngSubject
    .Display
End With
SendKeys "^({END})", True '<--- Add this line HERE
SendKeys "^({v})", True

Another Note: Also, i don't think you need "rngBody.Paste" in your Body string, as this just pastes that exact text in your email body

edited May 23 '17 at 10:27

Community

1
1

answered Oct 22 '13 at 21:29

Jaycal

2,087
1
13
21

Thank you Jaycal. I'm closer yet. However for part 1 of the answer, the result is only a partial hyperlink. When I add: "File:P:\Reports\Daily Portfolio Tracking\" & ActiveSheet.Range("L5") It results in the hyperlink only applying to "File:P:\Reports\Daily" which is then followed by the balance in regular text. Is there something about having a space in the address? If so, I'm not in the position to rename our company folders. – user2908603 Oct 23 '13 at 00:34
Updated the code; This is the quick fix, but it will put brackets before and after the link. If you need to remove the brackets, then I think you'll have to leverage `.HTMLBody` instead of `.Body` – Jaycal Oct 23 '13 at 01:31
Thanks again, Jaycal! That was all I needed! This solution will be applied across several reports. A lot of time will be saved. – user2908603 Oct 23 '13 at 15:47

Insert dynamic hyperlink from cell reference and place it above a copied range

1 Answers1