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

'--------------------------------------------------------------------------------
#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

'-------------------------------------------------------------------------------
#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

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

"Keith" wrote in message
...
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

'--------------------------------------------------------------------------------
#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

'-------------------------------------------------------------------------------
#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

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.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Keith" wrote in message ...
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

"Keith" wrote in message
...
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

'--------------------------------------------------------------------------------
#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

'-------------------------------------------------------------------------------
#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

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

"Ron de Bruin" wrote in message
...
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.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Keith" wrote in message
...
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

"Keith" wrote in message
...
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

'--------------------------------------------------------------------------------
#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

'-------------------------------------------------------------------------------
#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,

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 he
http://www.enhanceddatasystems.com/E...lSendChart.htm

--
Robin Hammond
www.enhanceddatasystems.com


"Keith" wrote in message
...
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

"Ron de Bruin" wrote in message
...
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.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Keith" wrote in message
...
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

"Keith" wrote in message
...
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

'--------------------------------------------------------------------------------
#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

'-------------------------------------------------------------------------------
#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

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Robin Hammond" wrote in message ...
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 he
http://www.enhanceddatasystems.com/E...lSendChart.htm

--
Robin Hammond
www.enhanceddatasystems.com


"Keith" wrote in message
...
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

"Ron de Bruin" wrote in message
...
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.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Keith" wrote in message
...
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

"Keith" wrote in message
...
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

'--------------------------------------------------------------------------------
#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

'-------------------------------------------------------------------------------
#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