How to send ActiveWorkbook.Path as link in email in excel VBA?

Question

I'm trying to use ActiveWorkbook.Path and ActiveWorkbook.Name to send the excel workbook's file path to someone through email. I got it to work when I input the file path as a string, but for some reason it doesn't recognize the full path when I do it this way. I don't want to use a hard-coded file address in case someone moves it to a different folder. This is what I'm using in the .HTMLBody part of the code:

.HTMLBody = emailtext & "<p>&nbsp;</p>" & "<p><strong>Click this link to go to task verification worksheet: <A href=" & _
        "" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & "" & ">Verification Workbook</A></strong></p>"

The full path should be D:\Control Verification\WorkbookName.xlsm but when I try to click the link it's trying to access D:\Control. Thanks in advance for any help!

score 3 · Answer 1 · answered Jul 19 '18 at 15:30

3

You can simplify your code by using ThisWorkbook.FullName which returns the entire path instead of ActiveWorkbook.Path & "\" & ActiveWorkbook.Name.

Also, if the workbook you want to send is the one executing the code, it's best to use ThisWorkbook instead of ActiveWorkbook, since ActiveWorkbook can be changed by many events and ThisWorkbook always returns the workbook executing the code.

answered Jul 19 '18 at 15:30

FrankVIII

118
8

To be clear, there should be few issues using ThisWorkbook? – N. Whyter Jul 19 '18 at 16:19
There could be issues using ThisWorkbook as well as using ActiveWorkbook. The issue with ActiveWorkbook is that the reference can get updated by VBA without explicitly telling it to do so. ThisWorkbook always references the workbook in which the VBA is being executed, regardless of user actions or code execution – FrankVIII Jul 19 '18 at 16:33
I will use ThisWorkbook in this case then. Thanks. – N. Whyter Jul 19 '18 at 16:43

JosephC · Accepted Answer · 2018-07-19T15:51:58.717

1

"<p>&nbsp;</p>" & "<p><strong>Click this link to go to task verification worksheet: <A href=" & _
    """" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & """" & ">Verification Workbook</A></strong></p>"

Adds quotations around the href, as the path: D:\Control Verification\WorkbookName.xlsm contains a space in it.

edited Jul 19 '18 at 15:51

answered Jul 19 '18 at 15:39

JosephC

917
4
12

One more set of quotes on each end seems to have done the trick. Thanks. – N. Whyter Jul 19 '18 at 16:04
Your welcome. I would heed the advice given in the other answers a regarding the use of ActiveWorkbook, as well. – JosephC Jul 19 '18 at 16:17
Yes I will try to implement their suggestions as well. – N. Whyter Jul 19 '18 at 16:18

score 0 · Answer 3 · answered Jul 19 '18 at 15:28

I guess the problem is the ActiveWorkbook is not saved...

Save it somewhere and try this:

Public Sub TestMe()

    ActiveWorkbook.Save

    MsgBox "<p>&nbsp;</p>" & "<p><strong>Click this link: <A href=" & _
           "" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & "" & _
           ">Verification Workbook</A></strong></p>"

End Sub

In general, try to be a bit careful around "Active" things in VBA ActiveWorkbook, ActiveCell, ActiveSheet - sometimes they can be something else and not the awaited ones - How to avoid using Select in Excel VBA.

How to send ActiveWorkbook.Path as link in email in excel VBA?

3 Answers3