Emailing range in Outlook- so close! just need a little help

[Keith]

I'm working on some VBA (based on great code by our MVPs and old posts) that
will grab ranges, turn it into a GIF, then email several GIFs within one
email to the target recipient. I have two challenges, which may or may not
be possible to overcome in Excel VBA; I welcome any help you might be able
to offer.

1. When I send test emails to myself (in Outlook, all in Office2003 on
WinXP), the GIFs are embedded as files, rather than visible within the body
of the email itself. I need to be able to have the recipient view the GIF
and type a response to each directly underneath in their reply (basically we
don't want them editing the range itself for several reasons, so I show a
picture of the range and they have to type their desired edits)- how do I
force Outlook to show the GIF instead of embedding it as an attachment?

2. The range is only one row, but is quite long (A:AI). When I click on an
embedded file, it appears that the code (below) doesn't actually include the
full width of the range. Is there a cap on how wide a GIF can be when
exported from Excel? Any suggested workarounds?

Thank you,
Keith

'--------------------------------------------------------------------------------
Re: #1, whether the GIF is embedded as an image or an attachment:

On Error Resume Next
With OutMail
.To = SendToName
.CC = ""
.BCC = ""
.Body = "Please respond to this email, and include comments and
updates directly under each row"
.Subject = "data update request"
For n = 1 To SendFileCount
.Attachments.Add (FName & CStr(n) & FExt)
Next
.Send 'or use .Display
End With
On Error GoTo 0

'-------------------------------------------------------------------------------
Re: #2, the width of a range that can be exported to a GIF

Sub CreateImageFile(TheExportRange As Range, _
TheFileName As String, _
TheFileFormat As String)

TheExportRange.CopyPicture Appearance:=xlScreen, _
Format:=xlPicture

Dim chtobj As ChartObject
Set chtobj = TheExportRange.Parent.ChartObjects.Add(1, 1, 1, 1)

With chtobj
.Width = TheExportRange.Width + 8
.Height = TheExportRange.Height + 8
.Chart.ChartArea.Border.LineStyle = 0
.Chart.Paste
.Chart.Export Filename:=TheFileName & "." & TheFileFormat, _
FilterName:=TheFileFormat
.Delete
End With

Set chtobj = Nothing
End Sub

 

[Keith]

One thought- I might be able to cut down the range size (width) for item 2,
but only if I can create a single GIF file from a discontiguous range
selection. Is there a way to adapt this code to accept a discontiguous
range, or would I be stuck copying everything over to a blank worksheet and
hiding the unnecessary columns there? I don't have the ability to mess with
the original source sheet, so I'd have to do it separately. A way to just
select a discontiguous range and process it into a GIF would be far
preferable.

Thanks!
Keith

 

[Ron de Bruin]

Hi Keith

You can link the ranges to a new sheet
Like I do here in a Print example
http://www.rondebruin.nl/print.htm#non-contiguous

Then you can create the gif.
Remember that the user decide if he see the picture in the mail directly, this is a setting in the mail program.

 

[Keith]

Thanks Ron!
I'll work out the discontiguous ranges on another sheet, since there isn't a
more direct way to do it.
Also, I've checked my own Outlook settings to see if I can force the images
to show up inline, but so far no luck- I'll try a little while longer, then
followup to a basic Outlook group if I can't figure it out.
Many thanks!
Keith

 

[Robin Hammond]

Keith,

There is a way to get the gif to show up inline, but it requires you to run
IIS on your source machine.

There's an example on my site which shows how to get a gif of a chart into
the message body, which could also be applied to any gif.

Have a look here:
http://www.enhanceddatasystems.com/ED/Pages/ExcelSendChart.htm

 

[Ron de Bruin]

The best way is this one I think
http://www.rondebruin.nl/cdo.htm#pictures