View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Keith Keith is offline
external usenet poster
 
Posts: 55
Default Emailing range in Outlook- so close! just need a little help

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