Emailing Pictures in the Mail Body

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.

emailpic1

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:

Public Function SheetToHTML(sh As Worksheet)
 
    Dim TempFile As String
    Dim fso As Object
    Dim ts As Object
    Dim sTemp As String
   
    Randomize
 
    sh.Copy
    TempFile = sh.Parent.Path & “TmpHTML” & Int(Rnd() * 10) & “.htm”
 
    ActiveWorkbook.SaveAs TempFile, xlHtml
    ActiveWorkbook.Close False
 
    Set fso = CreateObject(“Scripting.FileSystemObject”)
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
 
    sTemp = ts.ReadAll
 
    SheetToHTML = ConvertPixToWeb(sTemp, sh) ‘this line is new
   
    ts.Close
    Set ts = Nothing
    Set fso = Nothing
    Kill TempFile
 
End Function

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

Public Function ConvertPixToWeb(sHTML As String, sh As Worksheet) As String
 
    Dim Pic As Picture
    Dim lGif As Long
    Dim lSrcStr As Long
    Dim lSrcEnd As Long
    Dim sUrl As String
   
    Dim i As Long
    For Each Pic In sh.Pictures
        i = i + 1
        lGif = InStr(1, sHTML, Format(i, “000”) & “.gif”)
        lSrcStr = InStrRev(sHTML, Chr$(34), lGif)
        lSrcEnd = lGif + Len(Format(i, “000”) & “.gif”)
        sUrl = Chr$(34) & sh.Shapes(Pic.Name).Hyperlink.Address & Chr$(34)
        sHTML = Replace(sHTML, Mid(sHTML, lSrcStr, lSrcEnd – lSrcStr + 1), sUrl)
    Next Pic
   
    ConvertPixToWeb = sHTML
   
End Function

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:

<v :imagedata src=”./TmpHTML8_files/image001.gif” o:title=”3dSum”/>

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:

<v :imagedata src=””/blogpix/3dSum.gif”” o:title=””3dSum””/>

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.

Posted in Uncategorized

2 thoughts on “Emailing Pictures in the Mail Body

  1. Hi,
    Thanks.

    But when I am running my application and try to send mail ,with abov code ,I got error at following line .

    I have only one image in sheet.previously it was in bitmap.But afterward I converted it in “.Gif”

    lSrcStr = InStrRev(sHTML, Chr$(34), lGif)

    Please help me out.

  2. I am getting the same error as Chandan. I only have one Gif image that I am trying to send in the body of an email.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *