Copy Selected Range as picture and add to email

[Djblois]

Is this possible. I am trying to figure out how to send the selected range as a picture to an email with code.

and side note what is the best picture type to send to a blackberry? I don't have one but my boss does and I am working on this solution for him.

[lucas]

If you are using Outlook you might try this one that Johnske was messing around with for a while. Be sure to change the path to save the temp. You can delete the temp file when you are done......could probably kill it if this works for you.

[vba]Option Explicit
Sub SendRange()
'Sends a specified range in an Outlook message and retains Excel formatting
'Code written by Daniel Klann 2002
'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime

'Dimension variables
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String

'Select the range to be sent
On Error Resume Next
Set rngeSend = Application.InputBox("Please select range you wish to send.", , , , , , , 8)
If rngeSend Is Nothing Then Exit Sub 'User pressed Cancel
On Error GoTo 0
'Now create the HTML file
ActiveWorkbook.PublishObjects.Add(xlSourceRange, "F:\Temp\deleteMe.htm", rngeSend.Parent.Name, rngeSend.Address, xlHtmlStatic).Publish True

'Create an instance of Outlook (or use existing instance if it already exists
'Set olApp = CreateObject("Outlook.Application")
Set olApp = Outlook.Application
'Create a mail item
Set olMail = olApp.CreateItem(olMailItem)
'Open the HTML file using the FilesystemObject into a TextStream object
Set FSObj = New Scripting.FileSystemObject
Set TStream = FSObj.OpenTextFile("F:\Temp\deleteMe.htm", ForReading)
'Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTMLBody = TStream.ReadAll
olMail.To = "joe@aol.com"
olMail.Subject = "Test of Send Excel in Body of Email"
olMail.HTMLBody = strHTMLBody
olMail.Display
'olMail.Send

End Sub
[/vba]

Edit: changed the email address to a fake one instead of my own.

[Djblois]

Lucas,

Thank you I have found code similar to that in the paste and have it working. However the table formatting will not show on a blackberry and my boss needs a small report sent to him on his blackberry, so I want to send a picture of the range, which will show properly in a blackberry. That is why I am also wondering which is the smallest picture file (gif, jpg, ...)

[lucas]

Well jpg is a compressed file. If you zip a gif file it gets much smaller, if you zip a jpg you don't gain much......my guess is jpg.

[Djblois]

thank you for that info. Also, do you know how I can get the range into a jpg format then?

[lucas]

A couple of disscussion on this from a quick search.

http://www.vbaexpress.com/forum/show...icture+gif+jpg

http://www.andypope.info/vba/gex.htm

[Djblois]

lucas,

Those links are nothing like what I am looking for. I don't have any images in my spreadsheet or want to export them. I want the VBA code to take an image or screenshot of the selected cells and save it as a jpg. So a user can view it on their blackberry.

[lucas]

That's the point of the discussions.....obviously. Why do you think I offered Johnske's method. I know of no other. Maybe someone else has an idea.