none
Emailing an image of a worksheet

    Question

  • Hello,

    I once saw a team producing emails that contained what looked like an image of a worksheet. The Excel report used VBA behind the scenes to generate the image (in HTML?) + email the image.

     

    The beauty of this techniques is that the recipient does not need to open the spreadsheet to view the report.

    Does anyone know how this is done and what I need to search for so I can replicate the "image" generation?

    Thanks
    Amir

    Tuesday, November 08, 2016 4:10 PM

Answers

  • Hi amir tohidi,

    I had check and produces the same result like you with my first code if range is large.

    so I tested with my second code and I find that it is working correctly and produces a desire result with large range.

    the data are exactly copied and you can select and copy , paste from that data.

    I tested with very large range. you can see the output below.

    I have very long range and you can see the content is displayed properly. you can see that there is a link in contents so I can visit the links and also select the data , copy , paste all that you want. you can see a scroll bar there to view all the data properly.

    here I repost the same code that you can use.

    Sub EmailRange()
    Dim WorkRng As Range
    Dim xTitleId As String
    On Error Resume Next
    xTitleId = "DemoforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Application.ScreenUpdating = False
    WorkRng.Select
    ActiveWorkbook.EnvelopeVisible = True
    With ActiveSheet.MailEnvelope
        .Introduction = "Please read this email."
        .Item.To = "your mail.com"
        .Item.Subject = "information of workbook"
        .Item.Send
    End With
    Application.ScreenUpdating = True
    End Sub

    just select the range in sheet and run the code. inputbox will be displayed with selected range and click ok.

    hope this will solve your issue.

    Note:- please zoom the image, you can see that image is quite large. so it is looks like this here. but if zoom it you can see the data properly.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, November 11, 2016 1:57 AM
    Moderator
  • Here you go... this may work as well as the one which Deepak has provided the code below should work:

    Sub SendHTML_And_Image_As_Body_UsingOutlook()
    
        Dim olApp As Object
        Dim NewMail As Object
        Dim ChartName As String
        Dim imgPath As String
        
        On Error GoTo err
        
        Set olApp = CreateObject("Outlook.Application")
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        'define a temp path for your image
        tmpImageName = VBA.Environ$("temp") & "\tempo.jpg"
        
        'Range to save as an image
        Set RangeToSend = Worksheets("Sheet1").Range("A3:M27")
        ' Now copy that range as a picture
        RangeToSend.CopyPicture Appearance:=xlScreen, Format:=xlPicture
        
        ' To save this as an Image we need to do a workaround
        ' First add a temporary sheet and add a Chart there
        ' Resize the chart same as the size of the range
        ' Make the Chart border as Zero
        ' Later once we export that chart as an image
        ' and save it in the above temporary path
        ' will delete this temp sheet
        
        Set sht = Sheets.Add
        sht.Shapes.AddChart
        sht.Shapes.Item(1).Select
        Set objChart = ActiveChart
    
        With objChart
            .ChartArea.Height = RangeToSend.Height
            .ChartArea.Width = RangeToSend.Width
            .ChartArea.Fill.Visible = msoFalse
            .ChartArea.Border.LineStyle = xlLineStyleNone
            .Paste
            .Export Filename:=tmpImageName, FilterName:="JPG"
        End With
        
        'Now delete that temporary sheet
        sht.Delete
        
       ' Create a new mail message item.
        Set NewMail = olApp.CreateItem(0)
        
        With NewMail
            .Subject = "Your Subject here"
            .To = "abc@email.com"
            
    '       **************************************************
    '       You can desing your HTML body for this email.
    '       below HTML code will display the image in
    '       Body of the email. It will not go in attachment.
    '       **************************************************
            .HTMLBody = "<body><img src=" & "'" & tmpImageName & "'/> </body>"
            .send
        End With
    
    err:
    
        'Release memory.
         'Kill tmpImageName
        Set olApp = Nothing
        Set NewMail = Nothing
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    End Sub


    Vish Mishra


    • Edited by Vishwamitra Mishra Tuesday, November 15, 2016 1:39 PM change the image file type
    • Marked as answer by amir tohidi Tuesday, November 15, 2016 2:55 PM
    Tuesday, November 15, 2016 1:39 PM

All replies

  • Hi

    You can try the below code. Here you can design your HTML email with images etc. and once you send it, it will go as a HTML email with all the images embedded in the email and not as an attachment.

    Sub SendHTML_And_Image_As_Body_UsingOutlook()
    
        Dim olApp As Object
        Dim NewMail As Object
        Dim ChartName As String
        Dim imgPath As String
        
        On Error GoTo err
        
        Set olApp = CreateObject("Outlook.Application")
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        'define a temp path for your image
        tmpImageName = VBA.Environ$("temp") & "\tempo.gif"
        
        'Range to save as an image
        Set RangeToSend = Worksheets("Sheet1").Range("A3:M27")
        ' Now copy that range as a picture
        RangeToSend.CopyPicture Appearance:=xlScreen, Format:=xlPicture
        
        ' To save this as an Image we need to do a workaround
        ' First add a temporary sheet and add a Chart there
        ' Resize the chart same as the size of the range
        ' Make the Chart border as Zero
        ' Later once we export that chart as an image
        ' and save it in the above temporary path
        ' will delete this temp sheet
        
        Set sht = Sheets.Add
        sht.Shapes.AddChart
        sht.Shapes.Item(1).Select
        Set objChart = ActiveChart
    
        With objChart
            .ChartArea.Height = RangeToSend.Height
            .ChartArea.Width = RangeToSend.Width
            .ChartArea.Fill.Visible = msoFalse
            .ChartArea.Border.LineStyle = xlLineStyleNone
            .Paste
            .Export Filename:=tmpImageName, FilterName:="GIF"
        End With
        
        'Now delete that temporary sheet
        sht.Delete
        
       ' Create a new mail message item.
        Set NewMail = olApp.CreateItem(0)
        
        With NewMail
            .Subject = "Your Subject here"
            .To = "abc@email.com"
            
    '       **************************************************
    '       You can desing your HTML body for this email.
    '       below HTML code will display the image in
    '       Body of the email. It will not go in attachment.
    '       **************************************************
            .HTMLBody = "<body><img src=" & "'" & tmpImageName & "'/> </body>"
            .send
        End With
    
    err:
    
        'Release memory.
         Kill tmpImageName
        Set olApp = Nothing
        Set NewMail = Nothing
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    End Sub

    For more tutorials on send email you can view this URL : http://www.learnexcelmacro.com/wp/?s=email



    • Edited by Vishwamitra Mishra Tuesday, November 08, 2016 10:18 PM forgot to add how to save range as image
    Tuesday, November 08, 2016 4:42 PM
  • Hi amir tohidi,

    From the description of the thread I understand that you want to take the snapshot (image) of the active sheet and want to mail it.

    Follow the steps mentioned below.

    below is the data from the sheet that I want to send as Image.

    Code:

    Sub demo()
    Dim oApp As Outlook.Application
    Dim oMail As MailItem
    Dim wrdEdit
    Set oApp = GetObject(, "Outlook.Application")
    Set oMail = oApp.CreateItem(olMailItem)
    oMail.Subject = "Demo Mail with Excel Image Snapshot..."
    oMail.To = "ABC@xyz.com" ' change the mail address here. you can take dynamically from user
    oMail.Display
    oMail.BodyFormat = olFormatHTML
    Set wrdEdit = oApp.ActiveInspector.WordEditor
    Range("A1:H10").CopyPicture xlPrinter, xlPicture 'pass the range here. you can also take range dynamically from user.
    wrdEdit.Application.Selection.Paste
    oMail.Send
    Set wrdEdit = Nothing
    Set oMail = Nothing
    Set oApp = Nothing
    MsgBox ("Mail Sent Successfully...")
    End Sub
    

    make sure you add the reference to "Microsoft Outlook 16.0 Object Library" like below.

    when you run code. it will display the new mail window for just less then 1 second and send the mail.

    then you will see the message box "Mail Sent Successfully..."

    Note: Change the mail address and range in above mentioned code.

    when you receive the mail looks like below.

    you can see that it is an image not a actual sheet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 09, 2016 2:46 AM
    Moderator
  • Thanks guys but I have tried both techniques and neither produces images that are usable because of the very small size of the images in both cases.

    These two techniques rely on making an image of the worksheet. The example I had seen pasted HTML into the email which meant it was possible to copy and paste the data from the email.

    Thinking about it, what I need is the equivalent of copy form Excel and paste into email.

    Wednesday, November 09, 2016 3:51 PM
  • Dear Amir, If it was an image then it is not possible to copy the data from email. This means it was pasted with the source formatting in the email. Can you tell you want some other text as well with the sheet data?

    Vish Mishra

    Wednesday, November 09, 2016 5:21 PM
  • Hi amir tohidi,

    you had mentioned that,"I have tried both techniques and neither produces images that are usable because of the very small size of the images in both cases. "

    (1) what do you mean by usable here?

    (2) "image is very small" in the appearance or in the size on the disk?

    image is exact the same size of Excel. not small not large.

    you had mentioned that,"These two techniques rely on making an image of the worksheet. The example I had seen pasted HTML into the email which meant it was possible to copy and paste the data from the email."

    in the original post you had clearly mentioned that you want image of worksheet.

    now you had mentioned that you want to copy and paste the data from it.

    we can't copy paste and modify data using image.

    for that we need to send the range.

    please look the example mentioned below. from which you can select , copy and paste data to anywhere you want.

    Sub EmailRange()
    Dim WorkRng As Range
    Dim xTitleId As String
    On Error Resume Next
    xTitleId = "DemoforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Application.ScreenUpdating = False
    WorkRng.Select
    ActiveWorkbook.EnvelopeVisible = True
    With ActiveSheet.MailEnvelope
        .Introduction = "Please read this email."
        .Item.To = "yourmail@mail.com"
        .Item.Subject = "information of workbook"
        .Item.Send
    End With
    Application.ScreenUpdating = True
    End Sub
    

    to run this code first select the range in sheet using mouse.

    then run this code.

    range will be displayed in inputbox.

    click ok.

    if this is not what you want then show us the picture of your desire output so that we can get exact idea about that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 10, 2016 4:27 AM
    Moderator
  • I have attached images of what the two pieces of code produce.

    I have also attached the test Excel file with both techniques in its VBA.

    Deepak's version produces a very small image.

    Vish's version puts a red X

    Here is the test worksheet

    Thursday, November 10, 2016 3:51 PM
  • I am afraid I can't find a way of uploading the test XLSX file.

    Thursday, November 10, 2016 3:57 PM
  • Hi amir tohidi,

    I had check and produces the same result like you with my first code if range is large.

    so I tested with my second code and I find that it is working correctly and produces a desire result with large range.

    the data are exactly copied and you can select and copy , paste from that data.

    I tested with very large range. you can see the output below.

    I have very long range and you can see the content is displayed properly. you can see that there is a link in contents so I can visit the links and also select the data , copy , paste all that you want. you can see a scroll bar there to view all the data properly.

    here I repost the same code that you can use.

    Sub EmailRange()
    Dim WorkRng As Range
    Dim xTitleId As String
    On Error Resume Next
    xTitleId = "DemoforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Application.ScreenUpdating = False
    WorkRng.Select
    ActiveWorkbook.EnvelopeVisible = True
    With ActiveSheet.MailEnvelope
        .Introduction = "Please read this email."
        .Item.To = "your mail.com"
        .Item.Subject = "information of workbook"
        .Item.Send
    End With
    Application.ScreenUpdating = True
    End Sub

    just select the range in sheet and run the code. inputbox will be displayed with selected range and click ok.

    hope this will solve your issue.

    Note:- please zoom the image, you can see that image is quite large. so it is looks like this here. but if zoom it you can see the data properly.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, November 11, 2016 1:57 AM
    Moderator
  • Here you go... this may work as well as the one which Deepak has provided the code below should work:

    Sub SendHTML_And_Image_As_Body_UsingOutlook()
    
        Dim olApp As Object
        Dim NewMail As Object
        Dim ChartName As String
        Dim imgPath As String
        
        On Error GoTo err
        
        Set olApp = CreateObject("Outlook.Application")
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        'define a temp path for your image
        tmpImageName = VBA.Environ$("temp") & "\tempo.jpg"
        
        'Range to save as an image
        Set RangeToSend = Worksheets("Sheet1").Range("A3:M27")
        ' Now copy that range as a picture
        RangeToSend.CopyPicture Appearance:=xlScreen, Format:=xlPicture
        
        ' To save this as an Image we need to do a workaround
        ' First add a temporary sheet and add a Chart there
        ' Resize the chart same as the size of the range
        ' Make the Chart border as Zero
        ' Later once we export that chart as an image
        ' and save it in the above temporary path
        ' will delete this temp sheet
        
        Set sht = Sheets.Add
        sht.Shapes.AddChart
        sht.Shapes.Item(1).Select
        Set objChart = ActiveChart
    
        With objChart
            .ChartArea.Height = RangeToSend.Height
            .ChartArea.Width = RangeToSend.Width
            .ChartArea.Fill.Visible = msoFalse
            .ChartArea.Border.LineStyle = xlLineStyleNone
            .Paste
            .Export Filename:=tmpImageName, FilterName:="JPG"
        End With
        
        'Now delete that temporary sheet
        sht.Delete
        
       ' Create a new mail message item.
        Set NewMail = olApp.CreateItem(0)
        
        With NewMail
            .Subject = "Your Subject here"
            .To = "abc@email.com"
            
    '       **************************************************
    '       You can desing your HTML body for this email.
    '       below HTML code will display the image in
    '       Body of the email. It will not go in attachment.
    '       **************************************************
            .HTMLBody = "<body><img src=" & "'" & tmpImageName & "'/> </body>"
            .send
        End With
    
    err:
    
        'Release memory.
         'Kill tmpImageName
        Set olApp = Nothing
        Set NewMail = Nothing
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    End Sub


    Vish Mishra


    • Edited by Vishwamitra Mishra Tuesday, November 15, 2016 1:39 PM change the image file type
    • Marked as answer by amir tohidi Tuesday, November 15, 2016 2:55 PM
    Tuesday, November 15, 2016 1:39 PM
  • Both versions now produce what I needed. Thanks Deepak + Vish.

    Tuesday, November 15, 2016 2:54 PM