Solved

Email via VBA with image

Posted on 2011-09-16
8
679 Views
Last Modified: 2012-05-12
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
Comment
Question by:MelissaCG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36555458
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 36555586
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
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 36555968

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MelissaCG
ID: 36561333
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
 

Author Comment

by:MelissaCG
ID: 36561359
Here's an example of how it looks;
example.PNG
0
 

Author Comment

by:MelissaCG
ID: 36561666
Well, never mind... I just saved as BMP and it was great!! Thanks very much!!!
0
 

Author Closing Comment

by:MelissaCG
ID: 36561671
Perfect solution!!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36563318
or enhanced metafile might also be an option.

Glad it worked for you!

Dave
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
PHP contact form that lets the user to contact the company through email contact form. A button is fixed at the bottom of site, on clicking a new window will open where a user can send the email.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question