Note: The other languages of the website are Google-translated. Back to English

Bagaimana cara menempelkan berbagai sel ke dalam badan pesan sebagai gambar di Excel?

Jika Anda perlu menyalin berbagai sel dan menempelkannya sebagai gambar ke badan pesan saat Anda mengirim email dari Excel. Bagaimana Anda bisa menangani tugas ini?

Tempelkan berbagai sel ke badan email sebagai gambar dengan kode VBA di Excel


Tempelkan berbagai sel ke badan email sebagai gambar dengan kode VBA di Excel

Mungkin tidak ada metode lain yang baik bagi Anda untuk menyelesaikan pekerjaan ini, kode VBA dalam artikel ini dapat membantu Anda. Harap lakukan seperti ini:

1. Aktifkan lembar yang ingin Anda salin dan tempel sel sebagai gambar, tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, dan tempel kode berikut di Modul Jendela.

Kode VBA: tempelkan berbagai sel ke badan email sebagai gambar:

Sub sendMail()
    Dim TempFilePath As String
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xHTMLBody As String
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Application.InputBox("Please select the data range:", "KuTools for Excel", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    With Application
        .Calculation = xlManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set xOutApp = CreateObject("outlook.application")
    Set xOutMail = xOutApp.CreateItem(olMailItem)
    Call createJpg(ActiveSheet.Name, xRg.Address, "DashboardFile")
    TempFilePath = Environ$("temp") & "\"
    xHTMLBody = "<span LANG=EN>" _
            & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
            & "Hello, this is the data range that you want:<br> " _
            & "<br>" _
            & "<img src='cid:DashboardFile.jpg'>" _
            & "<br>Best Regards!</font></span>"
    With xOutMail
        .Subject = ""
        .HTMLBody = xHTMLBody
      .Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue
        .To = " "
        .Cc = " "
        .Display
    End With
End Sub
Sub createJpg(SheetName As String, xRgAddrss As String, nameFile As String)
    Dim xRgPic As Range
    Dim xShape As Shape
    ThisWorkbook.Activate
    Worksheets(SheetName).Activate
    Set xRgPic = ThisWorkbook.Worksheets(SheetName).Range(xRgAddrss)
    xRgPic.CopyPicture
    With ThisWorkbook.Worksheets(SheetName).ChartObjects.Add(xRgPic.Left, xRgPic.Top, xRgPic.Width, xRgPic.Height)
        .Activate
        For Each xShape In ActiveSheet.Shapes
            xShape.Line.Visible = msoFalse
        Next
        .Chart.Paste
        .Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
    End With
   Worksheets(SheetName).ChartObjects(Worksheets(SheetName).ChartObjects.Count).Delete
Set xRgPic = Nothing
End Sub

Note: In the above code, you can change the body content and email address to your need.

3. After inserting the code, press F5 key to run this code, a dialog box is popped out to remind you selecting the data range that you want to insert into the email body as picture, see screenshot:

4. Then click OK button, and a Message window is displayed, the selected data range has been inserted into the body as image, see screenshot:

Note: In the Message window, you can also change the body content and Email addresses in To and Cc fields as you need.

5. At last, click Send button to send this email.


Note: If you need to paste multiple ranges from different worksheets, the below VBA code can do you a favor:

First, you should select the multiple ranges that you want to insert into the email body as pictures, and then apply the following code:

VBA code: paste multiple ranges of cells into email body as image:

Sub sendMail()
    Dim TempFilePath As String
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xHTMLBody As String
    Dim xRg As Range
    Dim xSheet As Worksheet
    Dim xAcSheet As Worksheet
    Dim xFileName As String
    Dim xSrc As String
    On Error Resume Next
    TempFilePath = Environ$("temp") & "\RangePic\"
    If Len(VBA.Dir(TempFilePath, vbDirectory)) = False Then
      VBA.MkDir TempFilePath
    End If
    Set xAcSheet = Application.ActiveSheet
    For Each xSheet In Application.Worksheets
        xSheet.Activate
        Set xRg = xSheet.Application.Selection
        If xRg.Cells.Count > 1 Then
            Call createJpg(xSheet.Name, xRg.Address, "DashboardFile" & VBA.Trim(VBA.Str(xSheet.Index)))
        End If
    Next
    xAcSheet.Activate
    With Application
        .Calculation = xlManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set xOutApp = CreateObject("outlook.application")
    Set xOutMail = xOutApp.CreateItem(olMailItem)
    xSrc = ""
    xFileName = Dir(TempFilePath & "*.*")
    Do While xFileName <> ""
        xSrc = xSrc + VBA.vbCrLf + "<img src='cid:" + xFileName + "'><br>"
        xFileName = Dir
        If xFileName = "" Then Exit Do
    Loop
    xHTMLBody = "<span LANG=EN>" _
                & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
                & "Hello, this is the data range that you want:<br> " _
                & "<br>" _
                & xSrc _
                & "<br>Best Regards!</font></span>"
    With xOutMail
        .Subject = ""
        .HTMLBody = xHTMLBody
        xFileName = Dir(TempFilePath & "*.*")
        Do While xFileName <> ""
            .Attachments.Add TempFilePath & xFileName, olByValue
            xFileName = Dir
        If xFileName = "" Then Exit Do
        Loop
        .To = " "
        .Cc = " "
       .Display
    End With
    If VBA.Dir(TempFilePath & "*.*") <> "" Then
        VBA.Kill TempFilePath & "*.*"
    End If
End Sub
Sub createJpg(SheetName As String, xRgAddrss As String, nameFile As String)
    Dim xRgPic As Range
    ThisWorkbook.Activate
    Worksheets(SheetName).Activate
    Set xRgPic = ThisWorkbook.Worksheets(SheetName).Range(xRgAddrss)
    xRgPic.CopyPicture
    With ThisWorkbook.Worksheets(SheetName).ChartObjects.Add(xRgPic.Left, xRgPic.Top, xRgPic.Width, xRgPic.Height)
        .Activate
        .Chart.Paste
        .Chart.Export Environ$("temp") & "\RangePic\" & nameFile & ".jpg", "JPG"
    End With
    Worksheets(SheetName).ChartObjects(Worksheets(SheetName).ChartObjects.Count).Delete
Set xRgPic = Nothing
End Sub

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by
80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than
    300
    powerful features
    . Supports Office/Excel
    2007-2019 and 365
    . Supports all languages. Easy deploying in your enterprise or organization. Full features
    30
    -day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by
    50%
    , and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (42)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
wyrzuca mi błąd w linijce "Set xOutMail = xOutApp.CreateItem(olMailItem)" olMailItem - nie zdefiniowana
oraz ".Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue" olByValue - nie zdefiniowana
This comment was minimized by the moderator on the site
This is awesome, I love it! Quick question. I see that it is adding a border to the image. Is there a way to generate without a border? Thanks in advance!
This comment was minimized by the moderator on the site
I'd love to know how to paste without generating a border as well. This code is awesome, super intuitive and straightforward. Thank you!
This comment was minimized by the moderator on the site
Thanks dears,, Could you please tell me how to do this but without asking for range (predefined range)?
This comment was minimized by the moderator on the site
Did anyone ever reply to you?
This comment was minimized by the moderator on the site
Set xRg = Range("A1:J10")

Just set xRg to whatever range you want/need it to be.
This comment was minimized by the moderator on the site
buongiorno… potreste dirmi cosa devo inserire al posto di quelle stringhe del codice in blu?
This comment was minimized by the moderator on the site
Queste linee blu indicano "text ", è probabilmente una formattazione dell'editor utilizzato per creare il códice.
This comment was minimized by the moderator on the site
This is awesome!! Can you tell me how I can insert more than one image using this code? I need insert two interval of the same workbook, but they are in diferents sheets.
This comment was minimized by the moderator on the site
This is amazing. One question: How can I send two ranges that are in two differents sheets of the same workbook, each range in a different image?
This comment was minimized by the moderator on the site
Hi, Jose,
The code is somewhat difficult, and it can not insert here, if you want to this code, you can give your email here, and i will send the code to your email.
Thank you!
This comment was minimized by the moderator on the site
Hi Skyyang, can you let me have the code for sending two ranges from two sheets of the same work sheet, each range in a different image?
This comment was minimized by the moderator on the site
Hello, Jackie,
I have updated this article, you can use the code at the end of this article.
Please try, hope it can help you!
This comment was minimized by the moderator on the site
Hi skyyang, thank you very much. It works, for the most part. However, I have different ranges ("F1:N15") from sheet 1, and "H1:N15" from sheet 2. It seems always use the "F1:N15" range from both sheets. How can I make it change 2 different ranges?
This comment was minimized by the moderator on the site
Sorry, never mind. It's my mistake on my part. One more question - how can I add a space in between the images?
This comment was minimized by the moderator on the site
Hi, Jackie,
To insert a blank row between the images, you just need to press Enter key at the end of the image in the email body.
This comment was minimized by the moderator on the site
Buongiorno,
l'esecuzione del codice si ferma a xRgPic = ThisWorkbook.Worksheets(SheetName).Range(xRgAddrss) e torna alla sub sendMail senza creare l'immagine.

Utilizzo Office 2010 e win7

Come posso correggere?
This comment was minimized by the moderator on the site
Hi,
This works great, but has a border. Is there a way to remove the border
This comment was minimized by the moderator on the site
Hi
This works great. But it has a border around the image. Is there a way to take this off.


Thank you
This comment was minimized by the moderator on the site
Hello, srilatha,
The code in this article has been updated, please try, hope it can help you!
Thank you!
This comment was minimized by the moderator on the site
This is awesome. Thanks a heap


I got one last problem, my image appears a little blur and that happens only in one column .Any way to fix that.

Thank you!!
This comment was minimized by the moderator on the site
Join the club, even I am facing the same issue ,with borders around the image .
.
.
Waiting for a fix.

Fingers crossed !!!!
This comment was minimized by the moderator on the site
Hello, Breaking,
The code in this article has been updated, please try, hope it can help you!
Thank you!
This comment was minimized by the moderator on the site
When I try to send a second email with the same rage but diffent info (is a pivot) is showing the 1st image on the second email. How do I delete the image after created or pasted on email?
This comment was minimized by the moderator on the site
The code above works well on PC, while the picture can't be seen from mobile APP. It only showed "cid:DashboardFile.jpg". Is there any way to solve the issue?
This comment was minimized by the moderator on the site
Como fazer para inserir minha assinatura Outlook usando esse código?

How do I insert my Outlook signature using this code?
This comment was minimized by the moderator on the site
First save your signature in signature tab ,

then insert this following code in the code


.htmlbody = xHTMLBody & .htmlbody
This comment was minimized by the moderator on the site
This doesn't work for me, is there anything else that we can do to get the signature?
This comment was minimized by the moderator on the site
Thanks a lot for your code! Is it possible to add text between the images posted on the mail?
This comment was minimized by the moderator on the site
Hi ,
Firstly ,Thank you

Your code really workzz..


I have another requirement , could you please help me in appending code to the existing and resolve my issue ?

Here is my query :
Can the below image table sent with hyperlink enabled(i.e., clickable) ?


Expecting a fix from you . Kindly revert if my question if not clear. happy to make it clear.


Not sure if the image that i have uploaded is visible at your end . As its not visible for me after i posted it .

Please let me know your email id , so that i can send my reference query image to you.


Thanks in advance
This comment was minimized by the moderator on the site
This is great. However, some of the text from a cell is being cut out of the image when pasted into the email. And some of the spacing between letters is off.


For example: Cell says "Something is happening with the words."


In the email it shows as: "e th ing ishapp ening with the wo"
This comment was minimized by the moderator on the site
Hello...I have the problem that the image in @gmail is displayd as attachment and not in the body of the email... because when I send the email pasting my excel range manually to outlook from the source I can see that the image.png has even the: " src="cid:idashboard.png@01D622DC.8B4FCA60 and not just " src="https://download.extendoffice.com/cid:dashboard.jpgg@. I am afraind that must be icluded even the and trying to add the code like: PropertyAccessor.SetProperty etc ect ..can You help me pease ?thank Dritan
This comment was minimized by the moderator on the site
Hello,
First of all thank you for your work, but I have an issue with it. It seems that the Jpg generated named Dashboardfile stays in temp and the macro always use the same jpg in the email.
Maybe i miss something here. Hope you can help me.
Thank you
Gaëtan
This comment was minimized by the moderator on the site
Hi, I also have the error that the generated file stays in the temp and isn't being overwritten...
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL