Solved

Email via VBA with image

Posted on 2011-09-16
8
628 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Email signatures have numerous marketing benefits. Here are 8 top reasons to turn your email signature into a marketing channel.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 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

14 Experts available now in Live!

Get 1:1 Help Now