Note: The other languages of the website are Google-translated. Back to English
Logáil isteach  \/ 
x
or
x
Cláraigh  \/ 
x

or

Conas raon cealla a ghreamú i gcorp teachtaireachta mar íomhá in Excel?

Más gá duit raon cealla a chóipeáil agus a ghreamú mar íomhá i gcorp teachtaireachta nuair a sheolann tú ríomhphost ó Excel. Conas a d’fhéadfá déileáil leis an tasc seo?

Greamaigh raon cealla i gcorp ríomhphoist mar íomhá le cód VBA in Excel


Greamaigh raon cealla i gcorp ríomhphoist mar íomhá le cód VBA in Excel


B’fhéidir nach bhfuil aon mhodh maith eile ann chun an post seo a réiteach, is féidir le cód VBA san alt seo cabhrú leat. Déan mar seo le do thoil:

1. Cumasaigh an bhileog a theastaíonn uait na cealla a chóipeáil agus a ghreamú mar íomhá, coinnigh síos an ALT + F11 eochracha a oscailt Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Modúil Fuinneog.

Cód VBA: greamaigh raon cealla i gcorp ríomhphoist mar íomhá:

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

nótaí: Sa chód thuas, is féidir leat ábhar an choirp agus an seoladh ríomhphoist a athrú go do riachtanas.

3. Tar éis duit an cód a chur isteach, brúigh F5 eochair chun an cód seo a rith, tá bosca dialóige popped amach chun a mheabhrú duit an raon sonraí a theastaíonn uait a chur isteach sa chorp ríomhphoist a roghnú mar phictiúr, féach an scáileán:

4. Ansin cliceáil OK cnaipe, agus a Teachtaireacht taispeántar an fhuinneog, tá an raon sonraí roghnaithe curtha isteach sa chorp mar íomhá, féach an scáileán:

nótaí: Sa Teachtaireacht fhuinneog, is féidir leat ábhar an choirp agus seoltaí Ríomhphoist i réimsí To agus Cc a athrú de réir mar is gá duit.

5. Faoi dheireadh, cliceáil Seol cnaipe chun an ríomhphost seo a sheoladh.


nótaí: Más gá duit raonta iolracha a ghreamú ó bhileoga oibre éagsúla, is féidir leis an gcód VBA thíos fabhar a dhéanamh duit:

Ar dtús, ba cheart duit na raonta iolracha is mian leat a chur isteach sa chorp ríomhphoist a roghnú mar phictiúir, agus an cód seo a leanas a chur i bhfeidhm:

Cód VBA: greamaigh iliomad raonta cealla isteach sa chorp ríomhphoist mar íomhá:

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

Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

  • Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
  • Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
  • Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
  • Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
  • Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
  • Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
  • Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
  • Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
  • Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.
cluaisín kte 201905

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
bun officetab
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