Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

Как вставить диапазон ячеек в тело сообщения как изображение в Excel?

Если вам нужно скопировать диапазон ячеек и вставить его как изображение в тело сообщения при отправке электронного письма из Excel. Как бы вы справились с этой задачей?

Вставьте диапазон ячеек в тело письма как изображение с кодом VBA в Excel


Вставьте диапазон ячеек в тело письма как изображение с кодом VBA в Excel


Возможно, для вас нет другого хорошего способа решить эту задачу, код VBA в этой статье может вам помочь. Пожалуйста, сделайте так:

1. Включите лист, который вы хотите скопировать, и вставьте ячейки как изображение, удерживая нажатой кнопку ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модульи вставьте следующий код в Модуль Окно.

Код VBA: вставьте диапазон ячеек в тело письма как изображение:

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

Внимание: В приведенном выше коде вы можете изменить основной текст и адрес электронной почты по своему усмотрению.

3. После ввода кода нажмите F5 нажмите клавишу для запуска этого кода, появится диалоговое окно, напоминающее вам о выборе диапазона данных, который вы хотите вставить в тело письма как изображение, см. снимок экрана:

4. Затем нажмите OK и Сообщение отображается окно, выбранный диапазон данных был вставлен в тело как изображение, см. снимок экрана:

Внимание: В Сообщение В окне «Кому» и «Копия» вы также можете изменить текст сообщения и адреса электронной почты в полях «Кому» и «Копия».

5, Наконец, нажмите Отправить кнопку, чтобы отправить это письмо.


Внимание: Если вам нужно вставить несколько диапазонов из разных листов, приведенный ниже код VBA может оказать вам услугу:

Во-первых, вы должны выбрать несколько диапазонов, которые вы хотите вставить в тело письма как изображения, а затем применить следующий код:

Код VBA: вставьте несколько диапазонов ячеек в тело письма как изображение:

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

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Damian · 1 months ago
    Hi, Where I should paste my range for correct function this code.
    I have still the same range.
    Thanks for help - Damian.
    PS. This is´ great
  • To post as a guest, your comment is unpublished.
    Sachin Negi · 4 months ago
    This method is perfect but I'm unable to take email ids from range. Is there any specific reason ?
  • To post as a guest, your comment is unpublished.
    Chuck · 5 months ago
    Suggestions for including the default email signature?
  • To post as a guest, your comment is unpublished.
    User · 6 months ago
    Hi, the macro keep pasting the old image and not the new one, bow can i fix it? If i past Manually it works thanks

  • To post as a guest, your comment is unpublished.
    Jordan · 6 months ago
    Is there a way to change the name of the JPG file from DashboardFile?
  • To post as a guest, your comment is unpublished.
    Lukas · 6 months ago
    Thank You, it works great :-)
  • To post as a guest, your comment is unpublished.
    Tom · 1 years ago
    Hi, I also have the error that the generated file stays in the temp and isn't being overwritten...
  • To post as a guest, your comment is unpublished.
    Gaetan · 1 years ago
    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
  • To post as a guest, your comment is unpublished.
    DRitan · 1 years ago
    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
  • To post as a guest, your comment is unpublished.
    Jake · 1 years ago
    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"
  • To post as a guest, your comment is unpublished.
    msb.mp6@gmail.com · 1 years ago
    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
  • To post as a guest, your comment is unpublished.
    Daniel H · 1 years ago
    Thanks a lot for your code! Is it possible to add text between the images posted on the mail?
  • To post as a guest, your comment is unpublished.
    cabral1500 · 2 years ago
    Como fazer para inserir minha assinatura Outlook usando esse código?

    How do I insert my Outlook signature using this code?
    • To post as a guest, your comment is unpublished.
      smokanap · 1 years ago
      First save your signature in signature tab ,

      then insert this following code in the code


      .htmlbody = xHTMLBody & .htmlbody
      • To post as a guest, your comment is unpublished.
        CoMax · 1 years ago
        This doesn't work for me, is there anything else that we can do to get the signature?
  • To post as a guest, your comment is unpublished.
    windyying · 2 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Carlos · 2 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Breaking bad · 2 years ago
    Join the club, even I am facing the same issue ,with borders around the image .
    .
    .
    Waiting for a fix.

    Fingers crossed !!!!
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Breaking,
      The code in this article has been updated, please try, hope it can help you!
      Thank you!
  • To post as a guest, your comment is unpublished.
    srilatha.aithal@gmail.com · 2 years ago
    Hi
    This works great. But it has a border around the image. Is there a way to take this off.


    Thank you
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, srilatha,
      The code in this article has been updated, please try, hope it can help you!
      Thank you!
      • To post as a guest, your comment is unpublished.
        srilatha.aithal@gmail.com · 2 years ago
        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!!
  • To post as a guest, your comment is unpublished.
    Saisri · 2 years ago
    Hi,
    This works great, but has a border. Is there a way to remove the border
  • To post as a guest, your comment is unpublished.
    NoMadMax59 · 2 years ago
    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?
  • To post as a guest, your comment is unpublished.
    jackielqj · 2 years ago
    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?
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      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!
      • To post as a guest, your comment is unpublished.
        jackielqj · 2 years ago
        Sorry, never mind. It's my mistake on my part. One more question - how can I add a space in between the images?
        • To post as a guest, your comment is unpublished.
          skyyang · 2 years ago
          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.
      • To post as a guest, your comment is unpublished.
        jackielqj · 2 years ago
        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?
  • To post as a guest, your comment is unpublished.
    jotamasters · 2 years ago
    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?
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      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!
  • To post as a guest, your comment is unpublished.
    Jose Gonçalves · 2 years ago
    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.
  • To post as a guest, your comment is unpublished.
    dadd · 2 years ago
    buongiorno… potreste dirmi cosa devo inserire al posto di quelle stringhe del codice in blu?
    • To post as a guest, your comment is unpublished.
      jotamasters · 2 years ago
      Queste linee blu indicano "text ", è probabilmente una formattazione dell'editor utilizzato per creare il códice.
  • To post as a guest, your comment is unpublished.
    Mohammad · 2 years ago
    Thanks dears,, Could you please tell me how to do this but without asking for range (predefined range)?
    • To post as a guest, your comment is unpublished.
      Carter · 2 years ago
      Did anyone ever reply to you?
      • To post as a guest, your comment is unpublished.
        Jay · 2 years ago
        Set xRg = Range("A1:J10")

        Just set xRg to whatever range you want/need it to be.
  • To post as a guest, your comment is unpublished.
    taxicabconfessor@gmail.com · 2 years ago
    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!
    • To post as a guest, your comment is unpublished.
      Ian Wildman · 2 years ago
      I'd love to know how to paste without generating a border as well. This code is awesome, super intuitive and straightforward. Thank you!
  • To post as a guest, your comment is unpublished.
    Piotrek · 3 years ago
    wyrzuca mi błąd w linijce "Set xOutMail = xOutApp.CreateItem(olMailItem)" olMailItem - nie zdefiniowana
    oraz ".Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue" olByValue - nie zdefiniowana