Sunday, January 26, 2014

Send HTML email with embedded Images

The following article explains how to send an email in VBA with embedded images in the body. The code works with all Microsoft Office Suite (including Office 2013).

Let's consider an Excel file with a 'Dashboard' sheet as this screenshot :




The code detailed after automatically creates a 'ready to send' email in outlook :



First we create an outlook mail object, then, we write the mail body (in html). Please note following points :
    - embedded images must be save on your computer (as a jpg file or png file) ;
    - Since outlook 2013, embedded images must be attached to the email as well.

Sub sendMail()
        Application.Calculation = xlManual
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Dim TempFilePath As String
        
        'Create a new Microsoft Outlook session
        Set appOutlook = CreateObject("outlook.application")
        'create a new message
        Set Message = appOutlook.CreateItem(olMailItem)
          
        
        With Message
            .Subject = "My mail auto Object"
    
            .HTMLBody = "<span LANG=EN>" _
                & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
                & "Hello,<br ><br >The weekly dashboard is available " _
                & "<br>Find below an overview :<BR>"
               
            'first we create the image as a JPG file
            Call createJpg("Dashboard", "B8:H9", "DashboardFile")
            'we attached the embedded image with a Position at 0 (makes the attachment hidden)
            TempFilePath = Environ$("temp") & "\"
            .Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue, 0
               
            'Then we add an html <img src=''> link to this image
            'Note than you can customize width and height - not mandatory
               
            .HTMLBody = .HTMLBody & "<br><B>WEEKLY REPPORT:</B><br>" _
                & "<img src='cid:DashboardFile.jpg'" & "width='814' height='33'><br>" _
                & "<br>Best Regards,<br>Ed</font></span>"
                
            .To = "contact1@email.com; contact2@email.com"
            .Cc = "contact3@email.com"
                
            .Display
            '.Send
        End With
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        Application.Calculation = xlCalculationAutomatic
    End Sub

You need to create createJpg function which transform a range into a jpg file.

Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)
    ThisWorkbook.Activate
    Worksheets(Namesheet).Activate
    Set Plage = ThisWorkbook.Worksheets(Namesheet).Range(nameRange)
    Plage.CopyPicture
    With ThisWorkbook.Worksheets(Namesheet).ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)
        .Activate
        .Chart.Paste
        .Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
    End With
    Worksheets(Namesheet).ChartObjects(Worksheets(Namesheet).ChartObjects.Count).Delete
Set Plage = Nothing
End Sub

You can download my template file here (untick Use our download manager and get recommended downloads)

76 comments :

  1. Brilliant! Saved me tons of time.

    ReplyDelete
  2. Great! this example is good for me. Thank you

    ReplyDelete
  3. Excellent! Thank you... best I've seen!

    Is there a way to auto-scale the picture rather than defining width and height?

    ReplyDelete
    Replies
    1. Thx :)

      As far as I know, there is no other way to auto scale the picture.

      You can erase the part

      'width='814' height='33'

      But outlook will auto scale the picture randomly (it's linked to the zoom Excel file but there is no rule, I don't advice).

      Delete
    2. Got it. I played around with the scaling and decided to just remove that piece and I'm happy with how it looks.

      Thanks so much and, again, awesome post!

      Delete
  4. Is there a way to create an image of a different workbook to send in this email?

    ReplyDelete
    Replies
    1. Hi Matt,

      There is a way to create an image of a different workbook. You need to open the other workbook (you can make it invisible) and manipulate it as if I were your current workbook. Just use pieces of code below to make it this way :

      http://vba-useful.blogspot.fr/2013/12/how-do-i-retrieve-data-from-another.html

      Delete
  5. Hi Edouard,

    the code is great but somehow the recipient doesn´t see the image in the body

    Any Ideas

    ReplyDelete
  6. Hi Dave, it's suppose to work this all mail services. Maybe something wrong the spam filter of your recipient (auto image blocker).

    ReplyDelete
  7. Unfortunately even if i send the mail to myself as recipient, "Outlook 2010" then even i don´t see the Image which is rather irritating

    ReplyDelete
    Replies
    1. uncool, did you try on another computer ? maybe there's something wrong with your MS office suite. good luck

      Delete
  8. Hi there, this code is awsome. And I thanks you for that. But I have a little problme and I would like to know if you can help me. the sharpness of the image is not the best one, and I really need a way to improve it. Do you know how to do it?

    ReplyDelete
  9. Hey Tomas,

    Try to play with the Zoom. I have the feeling that Zoomed In Excel sheet (85% or below) gives a better screenshoot. Keep me posted.

    ReplyDelete
  10. Edouard, I try changing the zoom but it doesn't change. What make a signifcant change is that I errase the code that defines the height and widht. That make a really good improve. The only things thats still steal my dreams is that when it takes the picture of the ranges, I don't know why,but when it paste it in the e-mail suddenly some cells appears underline, others not. And some cells that are merge appears unmerge.
    Do you have any idea whay?
    Besides that the code is perfecto!!!

    Thanks again.

    ReplyDelete
  11. "Some cells appear underline" That's weird. Which Office version are you using? Did you download The template Excel file (link at the bottom of the article?).

    BR
    Ed

    ReplyDelete
  12. I can't download cause I'm using the computer of the company where I work and they don't let us download any document from internet.
    Beside that what I mean is that the format of the cells of the image is not the same as the format of the excel. Like the thickness of the edges change( I have thick and thin edges, and the picture that is paste it on the e mail doesn't respect that).

    Thank for the quick reply.

    BR

    ReplyDelete
  13. Give me your email address, I will send you the file.

    Ed

    ReplyDelete
    Replies
    1. can you send to me as well? I cannot get it to download silvia_suttle@yahoo.com

      Delete
    2. Hello Edouard,

      How do you I include a image that is on my spreadsheet instead of coverting the range into image?

      Thanks,

      Delete
    3. Hello Edouard,

      How do you I include a image that is on my spreadsheet instead of coverting the range into image?

      Thanks,

      Delete
  14. Thanks a lot Ed!
    I spent an entire day looking for a code that could attend my needs... this one was perfect !
    Diego

    ReplyDelete
  15. Good time of day, Ed!

    Yours is a wanderful example of code, thank you very much. With it's help I solved one interesting question. It could be a pleasure for me if you will take a look at my example here: http://www.excelworld.ru/forum/3-11679-1 Forum is Russian, but all comments inside module are on English. The file name is Day_Report. Just cutted unused parts and slightly redesigned structure for more efficiency.

    This code can send charts inside body of letter. Pretty useful for quick reporting.


    Best Regards,
    Roman.

    ReplyDelete
  16. I am getting an image error "The linked image cannot be displayed. The file may have moved, renamed, or deleted. Verify that the link points to the correct file and location." I am using this in a macro in the hidden PERSONAL macro workbook, will this make a difference? Please help

    ReplyDelete
    Replies
    1. Answer for sb who have problem like this

      Check maybe if name of your file contain . In my situation I have exactly the same problem but, when i changed name file on without space it works, or you can use '' to put name with space.

      Delete
  17. is jpeg the best format? and what other formats are available? great post!! tks

    ReplyDelete
  18. sorry but can thsi work for pivot charts?

    ReplyDelete
  19. Having an issue with this code when i use vbs to run it in the background. Can you please advise? I have posted a question here http://stackoverflow.com/questions/24987294/copypicture-method-of-range-class-failed-using-vbs-script/

    ReplyDelete
  20. It is working fine thank you :) But how can i get rid of the shadow behind the creen? I dont like it..

    ReplyDelete
  21. I am having issues with iphone recients not being able to view the embedded picture. Any thoughts

    ReplyDelete
  22. Image is not being sent in HTML Body, only sending as an attachment!

    ReplyDelete
  23. Hi Ed,

    any idea on how to add below code into Call createJpg("Dashboard", "B8:H9", "DashboardFile") ?

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

    this VBA is to select all active cells with value.

    ReplyDelete
  24. how can i attach screenshot of a website to outlook and send it automatically

    ReplyDelete
  25. How do i insert multiple images and also do not want to show as attachment

    ReplyDelete
  26. Thx a lot. Worked perfectly. Really helpful.

    ReplyDelete
  27. Ed, I am using this code to insert multiple "pictures" into an email. The problem I am having is that each time it pastes the JPG, it has a border around it that I want to get rid of. I believe I need to include a line with the following ".Line.Visible = msoFalse", but can't seem to work out where it goes to clear the border prior to pasting/exporting. Any help would be greatly appreciated.

    ReplyDelete
  28. Hi Ed,
    iPhone recipients only see...

    cid:DashboardFile.jpg

    in message body... any ideas?

    ReplyDelete
  29. Yeah I definitely need a solution for iPhone users as well. Image comes back blank.

    ReplyDelete
  30. Hi Ed,

    This is working perfectly when we open the mail in machines having outlook, but the same is not working when you send to gmail. It is being attached as picture when you send to gmail. Is there any fix for this, so that it shows within the body for any email account.

    Thanks

    ReplyDelete
    Replies
    1. I don't know how to make it work with gmail. This might be completly different, Good luck ;)

      Delete
    2. Thanks so much Ed for the quick response!

      Is there a way we can make the picture quality better, currently it is showing in a poor quality.

      Thanks again for the wonderful article and sharing the knowledge with all ...

      Delete
    3. Try to play with the Zoom. I have the feeling that Zoomed In Excel sheet (85% or below) gives a better screenshoot. Keep me posted.

      Delete
  31. Hello Ed , thank you for the code but it seems not to work properly , perhaps its because i have Microsoft office 2010?
    thank you in advance,
    damienpimouguet@hotmail.com

    ReplyDelete
  32. Hello Ed, Do you know if we can use this to put several range as pictures ? instead of one pic, several in a row

    Thank you have a good one

    ReplyDelete
    Replies
    1. Also, thank you the code is awesome !

      Delete
    2. Apparently just copy/ paste the code works ! I'm going to do it that way ! Thanks anyway for your very clear code!

      Delete
  33. Hi,

    How to paste Excel range(Without picture format)?


    Regards,
    Chandru

    ReplyDelete
  34. Hi Ed,

    Many thanks for the Makro. I have the same Problem as my predecessor. Do you have a soluton for Smartphone users?
    On an iPhone there is olny a Frame with the text "cid:DashboardFile.jpg" in the message body. On some Android Phones, depending on the Mailapp I had the chance to see the attachment. I read that a Outlook.PropertyAccessor should solve this problem but it didn't worked.

    ReplyDelete
  35. Hi Hasan,

    I don't have a solution for Smartphone. If someone have, I would love to know this answer.

    Cheers,

    ReplyDelete
    Replies
    1. Salut Edouard, tu peux jeter un coup d'oeil à mon message plus bas stp ?
      Bonne soirée

      Delete
    2. This comment has been removed by the author.

      Delete
  36. Bonjour Edouard,

    Comme dit plus haut, encore merci, la maccro marche au top, cependant j'ai essayé de l'utliser pour une autre utilisation, et elle ne fonctionne plus vraiment, elle me colle la photo comme si tout les trucs était les uns au dessus des autres, je pense que ca vient du fait que dans le range il y a une pivot table...

    tu sais comment faire pour que la photo soit simplement comme un screen des cell visible dans le range ?

    Merci d'avance et bon week end

    ReplyDelete
    Replies
    1. Voici un screen pour ettayer mes propos, en haut le screen que cela colle dans mon mail, en bas ce que cela devrait être, on a l'impression qu'il met un chart en fond...


      http://imgur.com/a/SX8Uo

      Delete
  37. The issue with this is that the images are overwritten every hour and if you open an email from previous hours to see the historic information then you get the most recent image and not the image that was sent that hour

    ReplyDelete
  38. Any ideas of a way around this?

    ReplyDelete
  39. I have been searching for a while to do something similar, but without converting to a JPG. I am trying to just send a range of cells from one sheet in an outlook message and I keep getting errors for everything I try. Can you help?

    ReplyDelete
  40. Thank you so much. Briliant idea

    ReplyDelete
  41. Hi Edouard, I used the same code for me to paste one Chart for an excel. But it showing as run time error 438.

    This are the code:
    Sub createGraph(nameSheet As String, nameFile As String)
    ThisWorkbook.Activate
    Charts("Daily Cumulative Collection").Activate
    Set Plage = ThisWorkbook.Charts("Daily Cumulative Collection")
    Plage.CopyPicture
    With ThisWorkbook.Worksheets("Namesheet").ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)
    .Activate
    .Chart.Paste
    .Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
    End With
    Worksheets("Namesheet").ChartObjects(Worksheets("Namesheet").ChartObjects.Count).Delete
    Set Plage = Nothing

    End Sub

    Are you able to help me on this?

    ReplyDelete
  42. Hi Edouard,

    The code works perfectly! I even used it to paste a picture from another workbook.

    However, the image that gets pasted has no colour formatting at all! How do I rectify that? Hoping you can help me with this.

    ReplyDelete
  43. Its a great pleasure reading your post.Its full of information I am looking for and I love to post a comment that "The content of your post is awesome" Great work.
    excel vba courses

    ReplyDelete
  44. Hi,

    If the data has more than 1, saying from row 9 to 14 and you just wanted to send the data with filled rate (Col G) which is above 50%. I am just using Filled Rate as an example but it will be helpful if there is any way to add if condition before attaching the data into object in outlook.

    Thank you

    ReplyDelete
  45. Dear Ed,

    Thank you very much for taking time to help everybody here.
    I need to send individual images to individual emails, however the images are sent as attachments, while I need to embed them. The issue is that I do not know how to make your code respond to cells that link to specific locally stored images. Might you explain (or perhaps exemplify) how to combine your code with the code found in the below link?

    http://www.rondebruin.nl/win/s1/outlook/amail6.htm

    Merci!

    ReplyDelete
  46. Hi - this is great, thank you. Is there a way to improve the quality of the image? It is not same quality as a copy and paste as bitmap, is there a way to get that quality as otherwise I get blurred numbers.

    ReplyDelete
    Replies
    1. You can improve your image quality by doing the next:

      Change this part of code:

      .Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"

      by

      .Chart.Export Environ$("temp") & "\" & nameFile & ".bmp", "BMP"


      When launching the macro set your worksheet zoom to 100% and you will get a perfect image inserted in your email.

      Delete
    2. Above didnt work for me : In code You can try this . at 100% zoom level


      "width='1000' height='200'>
      " _

      use above one instead of

      "width='814' height='33'>
      " _


      U ll get more clear image .

      Delete
  47. Hi, thank you for the macro! Is there a way to send the pictures like this not making cells but already is a image?

    ReplyDelete
  48. Can the fixed range be changed to a dynamic range?

    ReplyDelete
  49. This comment has been removed by the author.

    ReplyDelete
  50. compile error
    Set appOutlook = CreateObject("outlook.application")

    ReplyDelete
  51. trying to execute the above file in 2016 version but got this compile error, please help

    ReplyDelete
  52. This comment has been removed by the author.

    ReplyDelete
  53. Hello, thank you for your help!
    I combine letter with chart and tabel as picture and signture from outlook
    It works properly if i run it by button.
    But when I run macro by task scheduler it did 2 letters with same pictures

    VBS:
    all Run_macros

    Sub Run_macros()
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    Op_writ = "\\EES-FILE01\Shared Folders\T150\EE\Analytics\WorkSheduleTask\Personnel Management Report.xlsm"
    objExcel.Workbooks.Open (Op_writ)
    ' objExcel.Workbooks("Personnel Management Report.xlsm").RefreshAll
    ' objExcel.Application.Run "FilterGraph()"
    objExcel.Application.Run "Send_Email()"
    objExcel.Workbooks("Personnel Management Report.xlsm").Save
    objExcel.Workbooks("Personnel Management Report.xlsm").Close (False)
    objExcel.Quit
    End Sub

    ReplyDelete