Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako poslati e-pošto s kopiranjem in lepljenjem določenega obsega v telo e-pošte v Excelu?

V številnih primerih je lahko v vaši e-poštni komunikaciji uporaben določen obseg vsebine v delovnem listu Excel. V tem članku bomo uvedli način pošiljanja e-pošte s specifičnim lepljenjem palete v telo e-pošte neposredno v Excelu.

Pošlji e-pošto z določenim obsegom, prilepenim v telo e-pošte v Excelu


Pošlji e-pošto z določenim obsegom, prilepenim v telo e-pošte v Excelu


Naslednja VBA koda vam lahko pomaga kopirati obseg in ga prilepiti v telo e-pošte neposredno v Excelu. Prosimo, naredite na naslednji način.

1. V delovnem listu je obseg, ki ga potrebujete za kopiranje, pritisnite druga + F11 tipke hkrati odpreti Microsoft Visual Basic za aplikacije okno.

2. V odprtju Microsoft Visual Basic za aplikacije okno, kliknite Orodja > Reference kot je prikazano spodaj.

3. V Ljubljani Reference - VBAProjekt pogovorno okno, poiščite in preverite Knjižnica predmetov Microsoft Outlook in nato kliknite OK gumb.

4. klik Vstavi > Moduli, nato pa kopirajte in prilepite spodnjo kodo VBA v okno Modul.

VBA koda: Pošlji e-pošto z določenim obsegom, prilepenim v telo e-pošte v Excelu

Sub Send_Email()
    Dim xRg As Range
    Dim I, J As Long
    Dim xAddress As String
    Dim xEmailBody As String
    Dim xMailOut As Outlook.MailItem
    Dim xOutApp As Outlook.Application    
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select range you need to paste into email body", "KuTools For Excel", xAddress, , , , , 8)
If xRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailOut = xOutApp.CreateItem(olMailItem)
    For I = 1 To xRg.Rows.Count
        For J = 1 To xRg.Columns.Count
            xEmailBody = xEmailBody & "  " & xRg.Cells(I, J).value
        Next
        xEmailBody = xEmailBody & vbNewLine
    Next
    xEmailBody = "Hi" & vbLf & vbLf & " body of message you want to add" & vbLf & vbLf & xEmailBody & vbNewLine
    With xMailOut
        .Subject = "Test"
        .To = "happy.xuebi@163.com"
        .Body = xEmailBody
        .Display
        '.Send
    End With
    Set xMailOut = Nothing
    Set xOutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Opombe:

1). Prosimo, spremenite telo e-pošte v vrstico xEmailBody = "Zdravo" & vbLf & vbLf & "telo sporočila, ki ga želite dodati" & vbLf & vbLf & xEmailBody & vbNewLine kot jo potrebujete.

2). Navedite prejemnika e-pošte in predmeta .To = happy.xuebi@163.com in .Subject = "test" vrstice v kodi.

5. Pritisnite F5 ključ za zagon kode. Pri pojavu Kutools za Excel pogovorno okno, prosimo, izberite obseg, ki ga morate prilepiti v telo e-pošte, in kliknite OK gumb. Prikaz slike:

6. Zdaj je ustvarjen e-poštni naslov z določenim prejemnikom, predmetom, telesom in izbranim obsegom Excel, prosimo kliknite Pošlji da pošljete to e-poštno sporočilo. Oglejte si prikaz slike.

Opombe: Kodo VBA deluje le, če uporabljate Outlook kot vaš e-poštni program.


Sorodni članki:


Priporočena orodja za produktivnost za Excel

zavihek kte 201905

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    sachin kumr · 1 months ago
    this is pasting as a text. Kindly suggest how to send the table or the same format which is copied from the excel.
  • To post as a guest, your comment is unpublished.
    miguel · 2 months ago
    Code to send automatically after selecting after ok
  • To post as a guest, your comment is unpublished.
    Dhiraj Mahajan · 4 months ago
    Hi
    This code is vary excellent, by using the code i have completed my 90% of my project.
    I have same issue as mentioned by Anirudh that is table formatting. How can i format the table in email.
    Please help me......
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Good Day,
      The problem can't be solved yet. Sorry for the inconvenience and thank you for your comment.
      • To post as a guest, your comment is unpublished.
        Dhiraj Mahajan · 1 months ago
        Hi, Is there any update on below.......
        • To post as a guest, your comment is unpublished.
          Dhiraj Mahajan · 1 months ago
          Hi, Also I Wanted code for "Filter by Date".
          I am working on project, on that project I wanted to filter the data by the date, actually we have filter/hide the and last 05 days to current date data and we have highlight all other data.
          Please help me to complete this project.
          Your help is very great-full for me.
  • To post as a guest, your comment is unpublished.
    Anirudh · 8 months ago
    This is great. It is working as expected. The only issue is that the format of the table is not maintained in the mail. Can you let me know how do we preserve the format of the table in the mail
  • To post as a guest, your comment is unpublished.
    Michael Ferguson · 9 months ago
    This code is great, but I need to execute with a command button rather than pressing F5 in code view. I'd like my employees to be able to fill out some info, then hit the button and have it copy the range of cells they completed and paste into email. I can get code working for the email button and separately for the copy/paste of the range of cells, but can't seem to combine both functions. Please help!!! Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Michael,
      You just need to create a button (such as a Button (Form Control)) in your worksheet, then assign the macro to the button.