Solved

Email via VBA with image

Posted on 2011-09-16
8
637 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
  • 4
  • 3
8 Comments
 
LVL 41

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 41

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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 41

Expert Comment

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

Glad it worked for you!

Dave
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Email attacks are the most efficient and effective way for cyber criminals and hackers to compromise a computer or network. We often find our-self second guessing the authenticity of an email message, for such instances we can follow practical princ…
Ransomware continues to be a growing problem for both personal and business users alike and Antivirus companies are still struggling to find a reliable way to protect you from this dangerous threat.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

912 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now