Unlike many people with whom I work, I almost never email a workbook. Instead, I save it to an appropriate place on the server and email the link. This seems to confuse the hell out of some people, but if I’m going to change the way things are done, it has to start with me and I have to take a hard line. But that’s not the point of this post. The point is that I need to get the path to these workbooks I want to email. I’m sure many of you remember this gem of a post from 2006 about getting the UNC path from the Web toolbar. That technique found its way into this utility:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Sub GetUNCAddress() Dim doClip As DataObject Dim Wb As Workbook Dim sText As String Set doClip = New DataObject 'If there is no activeworkbook, you'll get the last open workbook. 'this makes sure that the last open workbook doesn't have a real 'path so you'll realize the error Application.ScreenUpdating = False Set Wb = Workbooks.Add Wb.Close False Application.ScreenUpdating = True sText = Application.CommandBars("Web").Controls("&Address:").Text doClip.SetText sText doClip.PutInClipboard Set doClip = Nothing End Sub |
That worked like a peach at my old job where mapped drives were the wild west. At my new job, it is guaranteed that everyone has certain mapped drives. In those cases, I don’t want to confuse them further by giving them a UNC path. So I send them the mapped drive path.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub GetMappedAddress() Dim doClip As MSForms.DataObject Set doClip = New MSForms.DataObject doClip.SetText ActiveWorkbook.FullName doClip.PutInClipboard Set doClip = Nothing End Sub |
Works great most of the time. One of our mapped drive is considered an “internet location” by Excel. I really don’t know how are servers work around here, but I do know that when I open something from that particular mapped drive, I have to click the Enable Content banner at the top.
This is called Protected View and it’s a pain in the ass. When a workbook is in Protected View and is active, the ActiveWorkbook object is Nothing. The above code fails. The good news is that Application.ActiveProtectedViewWindow is Nothing if that’s not the case.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub GetMappedAddress() Dim doClip As MSForms.DataObject Set doClip = New MSForms.DataObject If Not Application.ActiveProtectedViewWindow Is Nothing Then doClip.SetText Application.ActiveProtectedViewWindow.Workbook.FullName doClip.PutInClipboard Else doClip.SetText ActiveWorkbook.FullName doClip.PutInClipboard End If Set doClip = Nothing End Sub |
Do you want to know what else doesn’t work when Application.ActiveProtectedView is not Nothing? Application.Wait. That’s right, the Wait method fails with Method 'Wait' of object'_Application' failed. That’s just super. Anybody know what else fails in Protected View?
What does
fullnameURLencoded offer you ?
or
Sub M_snb()
With ThisWorkbook.Sheets(1)
.Hyperlinks.Add .Cells(1, 200), ThisWorkbook.FullName
MsgBox .Hyperlinks(.Hyperlinks.Count).Address
.Cells(1, 200).ClearContents
End With
End Sub
When you put it in the clipboard, it gets converted to UNC notation then ?
I use FileSystemObject instead.
is there a method to convert a known mapped drive path to UNC with Outlook.
In my company users will, from Outlook, insert a hyperlink to a file, which shows in the email message as DriveLetter:\\folder\folder\filename
insted of
\\s1111386\folder\folder\filename
So ideally they would add a button to Outlook bar to convert the hyperlink–this would then work for non-excel file types.
anyone have a code to suggest?
Dave
Thanks for this! I’ve always used the mapped address, but didn’t know the trick for the UNC. That will be useful.
I link these macros to buttons on the quick access toolbar. This allows me to get the address in the clipboard with one button click or keyboard shortcut, and then paste it into an email. I also use it for pasting a link into an email when possible (I agree with your theory), but not everyone is on the same servers at my company. Most of the time I use this to be able to quickly paste the file address in the file browser window for an attachment. This works for any email application or web based email, not just outlook.
I would consider this a really small tool that is a huge time saver over the long haul.
Where I work, different people can have different file shares mapped with the same letter (My “V:\” drive might not be the same as yours), which is why I don’t use or try to get the drive letter via VBA. I just add the Document Location button to the Quick Access Toolbar. With that, when you have the file open, one click on the location in that box on the QAT, CTRL+C to copy, paste as hyperlink in email. Done.