Over at dicks-clicks.com, I have a little write up on automating Outlook via Excel. One of the functions that I present (that I stole, from Jim Rech, I believe) converts an Excel worksheet to html so that it can be put in the HTMLBody property of the MailItem object. If you have Outlook 2000 or newer, you can use this technique to display a worksheet in the body of the email.

One major problem with that method is images. When you save a workbook as html (as that function does), it stores all of the images in a separate folder and points the src argument to that folder. That’s nice, but the person to whom you send the email isn’t likely to have that folder on their system. And even if they did, it probably won’t contain the image file to which your email is supposed to point.

The first step in getting this to work is to put the image file in a publicly accessible place, like the internet. If the image file is on a server somewhere and the recipient of your message is connected to the internet, then theoretically it should be possible to display that image. Tony recently got me interested in this problem again, and this is as close as I’ve ever gotten to solving it. I thought it might be time to throw out what I have to your scrutiny.

First, I inserted a picture into an Excel worksheet. Insert > Picture > From File and I put “/blogpix/3dSum.gif” into the Filename box on the dialog. That brought in the picture nicely. Now that the picture is in Excel, it doesn’t remember from whence it came. In other words, I have no way of tracing that picture back to its internet location.

Where to store its internet location for future reference? I decided that for each picture I would create a hyperlink pointing to the URL of that image. I think that method stinks, so if you can think of a better one, I’m all ears. Anyway, the URL is now stored with the picture.

Now, I modify the SheetToHTML function to look like this:

Once the html string is read into a temporary variable, I do a little string manipulation. Here’s where it gets a little dicey.

First a little background. In SheetToHTML, I rely on the html that Excel generates when you save a worksheet thusly. That html is terrible, and not just because I don’t understand it. However, I understand it enough to note this part:

Excel created a folder called TmpHTML8_files and put the image file in there. Since there’s one image in the file, it’s named image001.gif. God help me if I ever have 100 images in an Excel file such that I need all three number placeholders. This string with the path to the image appears two or three times in the html and all I have to do is replace those with the URL which I’ve stored in a hyperlink.

Note that the deprecated Picture object doesn’t have a hyperlink property, so I have to use the Shapes collection object to access it. When the replacement is complete, the line looks like:

and I can confirm, because I emailed it to myself, that it displays properly in the email.

One big assumption that I make is that Excel names the image files (e.g. image001.gif) in the same order that objects are the in the Pictures collection object. I don’t know if that’s true, but it seems reasonable.

So that’s the long, boring story of images in email. I’m not going to assert that this a good method – even if my function had some rudimentary error checking. Assigning hyperlinks to all the images isn’t what I’d call elegant. If anyone has any good ideas for improvement, be sure to let me know.