Using MailTo: in a VBA macro

So yesterday I wrote a VBA macro where I wanted the end user to be able to send me some information generated by the tool the macro is a part of. I am pretty good at programming Outlook to do things for me using VBA, but I don't want to assume that's what the end user uses.

The alternative I came up with was using the mailto: command or link or whatever it is. It's the same thing you see in a web page when someone embeds their email address, you click it, and Windows pops up your default email editor with the author specified in the to: line. In a search I came up with this page that shows how you can not only specify the to: line, but also the subject and body. Perfect for what I want to do.

So all I had to do was make a VBA function to build a nice mailto: line based on input, and here's what I came up with:

Public Sub StartEmail(ToAddr As String, Optional Subject As String, Optional Body As String)
Dim URL As String
URL = "Mailto:" & ToAddr
If Subject <> "" Then
URL = URL & "?subject=" & Subject
If Body <> "" Then
URL = URL & "&body=" & Body
End If
ElseIf Body <> "" Then
URL = URL & "?body=" & Body
End If

Navigate URL
End Sub

The function simply takes in a mandatory to: argument, and optional subject and/or body, and builds a mailto: link like this.

The last link in this whole chain is calling the mailto: link so Windows can deal with it. I just call a sub I call navigate, which is really just calling the Windows ShellExecute API function. I just set it up in a separate module in pretty much any project I do because i usually end up using it for something, usually just letting Windows deal with opening a file or link, instead of me having to deal with it.

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal _
lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As _
Long

Private Const SW_SHOW = 1

Public Sub Navigate(ByVal NavTo As String)
Dim hBrowse As Long
hBrowse = ShellExecute(0&, "open", NavTo, "", "", SW_SHOW)
End Sub


So yeah, anyway, now I have a nice easy one-line call to open up an email with whatever I want filled in. To be honest I've only tried it on my work machine which has Outlook as the default mail app, so your mileage may vary with other apps.

Import a folder of pictures to PowerPoint slides

My father has a project he's doing for presentation to a local historical society. Part of this project appears to involve documenting a bunch of photos, and also presenting them. He asked me the other day to give him a tutorial on adding photos to PowerPoint slides, at which point he'd take about 120 photos and manually place them.

Thinking of doing anything manually in MS Office made me cringe a bit. So I offered to build him a VBA macro to place the photos for him, provided that he populate a folder with all the images he wanted, then he could hit a button, pick the folder, and get a PowerPoint presentation with all his pictures placed.

Anyone who's written VBA for PowerPoint could figure out how to do this in a trice, but here I've provided an unlocked PPT file with all the code you need.

It seems to work fine for me, and you can do what you want with it.

You can download it here.

Power Point Picture Synchronization

Some time ago I worked on a project where I was automatically generating/regenerating png graphics of some business diagrams and thought of how someone would be able to use these in a PowerPoint presentation.

Typically users go to Insert->Picture->From File, and select a picture to insert into a presentation. You have two options in PowerPoint for how to store this image: either embed a bitmap in the PPT file or have PowerPoint link to the file, meaning PowerPoint loads the image fresh every time you start PowerPoint.

So if you have constantly changing graphics, it's a great idea to use the linking functionality. Now no matter what you'll always have the latest and greatest version of that graphic in your presentation.

But what if you want to send someone else that file? If you email someone the PPT file with a linked image, PowerPoint will be looking for that image file, so you have to send that picture along and have the recipient store their file in the same location. You could argue that you can put it on a shared drive, but what if your recipient is off the network?

I tackled this problem by building an addin for PowerPoint in VBA. The addin creates a toolbar with buttons that allow you to import a linked image to a presentation. The addin stores the path to the image file, but also stores a local copy of the image. Every time you open the presentation (with the addin enabled), the addin simply checks the modification date of the file versus the date when the image was updated in PowerPoint. If the linked image has been modified, the addin simply swaps out the old image with the new one, maintaining animation options and size.

This software is provided as is for use however you want, with no warranty or guarantee of how it will work.

I've tested the addin with PowerPoint 2003 and 2007. You can download it here. To install it simply copy the PictureSync.ppa file to %appdata%\microsoft\addins

Then open PowerPoint and enable the addin. The toolbar will appear in PowerPoint 2003, and will appear in the Addins ribbon tab in PowerPoint 2007.

If you have any questions of comments let me know here.