Email via VBA with image

Posted on 2011-09-16
Medium Priority
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.
Question by:MelissaCG
  • 4
  • 3
LVL 42

Expert Comment

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.


LVL 22

Expert Comment

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.
LVL 42

Accepted Solution

dlmille earned 2000 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.Export strPath & "myfile.gif"
 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
 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.


Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

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 :)

Author Comment

ID: 36561359
Here's an example of how it looks;

Author Comment

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

Author Closing Comment

ID: 36561671
Perfect solution!!
LVL 42

Expert Comment

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

Glad it worked for you!


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …

621 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