Emailing Links Instead of Files

The “Send To” menu is one of the things I like best about Microsoft Windows. It enables me to select one or more files and send them somewhere. An example would be selecting a file and using the “Desktop” option to have Windows create a shortcut to the file on my desktop. If you aren’t familiar with the “Send To” menu, then you can read more about it at this Microsoft page.

What’s even better is that I can customize the “Send To” menu by adding my own items. All I need do is create a shortcut and drop it in the “Send To” folder. Say I need to copy files to a certain folder on a regular basis. I can create a shortcut to the target folder and drop it in the “Send To” folder. Now when I right-click a file and select “Send To” I’ll see that folder as one of the options. That’s pretty handy, but there’s another way I can modify my “Send To” menu. I can drop a VBScript file in it. This allows me to do a lot more than just copy files to a folder or open them in a program.

Here’s how it works. When I select a script from the “Send To” menu Windows runs the script and passes the paths of the files I have selected to the script as parameters. Inside the script I can retrieve those parameters via the script’s Arguments collection. Each argument is the path to a file. The script can then perform any operation I want on each of those files.

Here’s a practical example. One of the options on the “Send To” menu is to send an email. Choosing this option creates an email, attaches the files to it, fills in the subject and the body, then displays the message on screen for you to finalize and send. This is fantastic if you want to send the files, but in a corporate environment sending the file may not be the best option. I know it’s not the best choice at my office. Instead of sending the files, which clogs up the email system (especially if the files are large) and eats into your mailbox’s space limit, it’s better to send a link to the file. Of course the files have to be on the network for a link to work. If the files are on my computer, then the link won’t work because the other staff don’t have access to my computer. In most corporate environment the files are already on the network anyway, so sending a link is a great alternative to sending the actual file.

The script below does the same thing that the “Send To” menu’s built-in “Mail recipient” option does, only it sends links instead of the file. Personally I don’t like the subject line or the message text that the “Mail recipient” uses so I left them out. I prefer to enter my own subject line and message. Another difference is that this script uses my default signature while the “Mail recipient” option doesn’t use any signature. Finally, this solution sends messages in HTML format whereas the “Mail recipient” option uses plain text.

Usage. Once the script is in place here’s how to use it.

1. With Windows Explorer open select one or more files and/or folders.
2. Right-click and select Send To.
3. Select
4. Link to mail recipient.
5. An Outlook email should appear on screen with hyperlinks to each selected file/folder in the message body.
6. Address the message, add a subject and a message, and click Send.

Instructions. Follow these instructions to add the script to the “Send To” menu.

1. Open Notepad
2. Copy the code and paste it into Notepad
3. Save the file with as “Link to mail recipient.vbs”
4. Close Notepad
5. Launch Windows Explore
6. Navigate to the folder you saved the script in
7. Click the Window button and enter shell:sendto in either the Run or Search field (depending on your version of Windows)
8. A second Windows Explorer window should open showing your “Send To” menu folder
9. Drag and drop the file you saved in step #3 from the first Windows Explorer window to the “Send To” folder in the second window
10. An alternative to the last step is to create a shortcut to the script and move/copy it to the “Send To” folder. You can change thee icon used by a shortcut. If instead you copy the .vbs file, then Windows will use the standard VBScript icon which isn’t as attractive.

'Define some variables
Dim objFSO, varArg, varPath, varMsg, olkApp, olkMsg

'Initialize variables
Set objFSO = CreateObject("Scripting.FileSystemObject")
varMsg = "<br><br>"

'Process the passed files
For Each varArg In WScript.Arguments
    varPath = Replace(varArg, " ", "%20")
    varMsg = varMsg & "<a href=""file://" & objFSO.GetAbsolutePathName(varPath) & """>" &  objFSO.GetBaseName(varArg) & "</a><br>"
Next

'Connect to Outlook, create and display the message
Set olkApp = CreateObject("Outlook.Application")
Set olkMsg = olkApp.CreateItem(0)
With olkMsg
    .Display
    .HTMLbody = varMsg & .HTMLBody
End With

'Clean-up
Set objFSO = Nothing
Set olkApp = Nothing
Set olkMsg = Nothing
WScript.Quit

Exporting Outlook Task Counts to Excel

Richard Paine commented on my Exporting Outlook Message Counts to Excel post asking if it would be possible to gather some statistics about tasks in Outlook. What Richard would like is a macro that counts the number of active tasks, the number of tasks created that day (i.e. the day the macro is run on), and the number of tasks completed that day. Richard clarified that he would like the macro to write the data to an existing sheet in an existing Excel workbook.

To accomplish this the macro needs to loop through all the items in a tasks folder (I’m assuming Richard’s tasks are in the default folder, but can modify the code to accommodate any task folder) checking each one to see if it is active, was created on the same day the macro is being run, was completed on the same day the macro is being run. Having collected the counts, the macro then checks to see if the spreadsheet exists, creates it if it doesn’t, opens it if it does, and writes the counts to it. Finally, the code saves the workbook, cleans up the objects to avoid memory leaks, and displays a dialog-box to let Richard know that it’s finished. All very straightforward.

Sen – Custom Excel Export

Here’s another twist on exporting items from Outlook to Excel. Senthilblr Kumar (Sen) commented on the original Exporting Outlook Messages to Excel post asking if I could modify it by limiting the export to messages where he is on the “To” line. He doesn’t want to export those where he was copied (CC) or blind copied (BCC). Sen also wants the process to export the starting folder and all sub-folders under it.