Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 920
  • Last Modified:

Email via VBA with image

Hello, here is my problem... I have a worksheet that sends an email to some people based on a range... The problem is that when this email is read from a BlackBerry cell phone, the format is lost... So I thought about, instead of pasting the table as HTML, just copy the range as a picture and paste the picture in the email body...

Is it possible??? I've searched for a bit now, but i cant find how to make this work...

Thanks in advance.
0
MelissaCG
Asked:
MelissaCG
  • 4
  • 3
1 Solution
 
dlmilleCommented:
Here's a link to Ron deBruins website, where he has extensive help on using Office with Outlook or other mail systems.  There's a section on creating HTML as the body of an email via VBA here:http://www.rondebruin.nl/mail/folder3/row2.htm.  His main website on the email topic is here:http://www.rondebruin.nl/sendmail.htm

However, Ron notes that the code doesn't work if your data is in an Excel 2003 list or Excel2007+ table.  If you're just trying to email a tabular list or otherwise, the code should work.

Your idea to convert your table to a picture, and email the picture in the email body is intriguing...

This next set of tips look likely candates for you.  http://www.codeforexcelandoutlook.com/excel-vba/send-email-from-excel/  toward the bottom are instructions in how to export a range as an image, then code for embedding the image into the body of your email message.

Let me know if you need further assistance.

Cheers,

Dave
0
 
rspahitzCommented:
There are several possibilities, but Excel doesn't handle any of them very conveniently.
Possibly a better way is to get this controlled by a look like VB.Net
Otherwise you likely need a Window API to do something like a screen capture then save it and attach it to the e-mail, and Windows APIs are very wrought with problems because they are so picky about things.
0
 
dlmilleCommented:

I've used the API from VBA for good effect.  However, the simplicity of the link I posted is worth reviewing.  Exporting a range as an image is a snap with the code and a few slight modifications.

Here's the code:
Sub doItAll()
    Call CopyRangeToGIF
    Call mailTheGif(False) 'true to send, false to just display in outlook
End Sub
'Adapted from Source:http://www.codeforexcelandoutlook.com/blog/2008/11/export-excel-range-to-a-picture-file/
Sub CopyRangeToGIF()
 ' save a range from Excel as a picture
 Dim rng As Excel.Range
 Dim cht As Excel.ChartObject
 
Dim strPath As String

strPath = ActiveWorkbook.Path & "\"
 
Application.ScreenUpdating = False
 
Set rng = Range("A1").CurrentRegion
 
rng.CopyPicture xlScreen, xlPicture
 
Set cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width + 10, rng.Height + 10)
 
cht.Chart.Paste
 
cht.Chart.Export strPath & "myfile.gif"
 
cht.Delete
 
ExitProc:
 Application.ScreenUpdating = True
 Set cht = Nothing
 Set rng = Nothing
End Sub

Sub mailTheGif(sendMail As Boolean)

 Dim olApp As Object ' Outlook.Application
 Dim Msg As Object ' Outlook.MailItem
 Dim myPic As String
 

 Set olApp = CreateObject("Outlook.Application")
 Set Msg = olApp.CreateItem(0)
 
 myPic = ActiveWorkbook.Path & "\myfile.gif"
 
With Msg
 .To = ThisWorkbook.Names("eMailAddress").RefersToRange.Value
 .Body = "Check out this range!"
 .Attachments.Add ActiveWorkbook.Path & "\myfile.gif"
  .HTMLBody = "<BODY><FONT face=Arial color=#000080 size=2></FONT>" & _
             "<IMG alt='' hspace=0 src='cid:myfile.gif' align=baseline 0rder=0>&nbsp;" & _
             "<br><br>Plus add any text you want</BODY>"
             
 If sendMail Then
    .Send
 Else
    .Display
 End If
 
 End With


End Sub

Open in new window


Here's what's in the workbook right now (let's assume the table to be sent is in the current region starting with range A1 - the way the VBA is coded now:
 What's in the workbook
And here's what happens - when you click the "doit" button, it copies the table based on A1's current range to a chart image, saves it, then sends the email (note FALSE makes it display only for testing - change the parameter to TRUE to actually send the email).  The process attaches, then embeds via the HTML the picture into the email...

The next thing that happens is Outlook Displaying the message:
 What's in Outlook - ready to send
I just sent one to myself, checked it on Outlook and its perfect, and while I still have to "open attachment" - as with most anything - on my Blackberry, it looks great!

See the attached workbook for demonstration.

Enjoy!

Dave
sendTableViaEmail-r1.xlsm
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MelissaCGAuthor Commented:
Hey dlmille, your example works just PERFECTLY!!! I have just one little question before i close this question (sorry about the delay, i was out of town in the weekend). When i use it in a range that contains data, but its not actually a table, the image comes with a poor resolution. Is there a way to improve the quality of the image in that case??

Thanks :)
0
 
MelissaCGAuthor Commented:
Here's an example of how it looks;
example.PNG
0
 
MelissaCGAuthor Commented:
Well, never mind... I just saved as BMP and it was great!! Thanks very much!!!
0
 
MelissaCGAuthor Commented:
Perfect solution!!
0
 
dlmilleCommented:
or enhanced metafile might also be an option.

Glad it worked for you!

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now