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, če je v Excelu spremenjena določena celica?

Ta članek govori o pošiljanju e-pošte prek Outlooka, če je v Excelu spremenjena katera koli celica v določenem obsegu.

Pošljite e-pošto, če je celica v določenem obsegu spremenjena s kodo VBA


Pošljite e-pošto, če je celica v določenem obsegu spremenjena s kodo VBA


Če morate samodejno ustvariti novo e-pošto z aktivnim delovnim zvezkom, ki je priložen, ko so celice v območju A2: E11 spremenjene na določenem delovnem listu, vam lahko pomaga naslednja koda VBA.

1. Na delovnem listu, ki ga potrebujete za pošiljanje e-pošte na podlagi spremenjene celice v določenem obsegu, z desno miškino tipko kliknite zavihek stanja in nato kliknite Ogled kode iz kontekstnega menija. Prikaz slike:

2. Pri pojavu Microsoft Visual Basic za aplikacije okno, kopirajte in prilepite pod kodo VBA v okno Code.

VBA koda: pošlji e-pošto, če je celica v določenem obsegu spremenjena v Excelu

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 2017/9/12
    Dim xRgSel As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xMailBody As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set xRg = Range("A2:E11")
    Set xRgSel = Intersect(Target, xRg)
    ActiveWorkbook.Save
    If Not xRgSel Is Nothing Then
        Set xOutApp = CreateObject("Outlook.Application")
        Set xMailItem = xOutApp.CreateItem(0)
        xMailBody = "Cell(s) " & xRgSel.Address(False, False) & _
            " in the worksheet '" & Me.Name & "' were modified on " & _
            Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
            " by " & Environ$("username") & "."

        With xMailItem
            .To = "Email Address"
            .Subject = "Worksheet modified in " & ThisWorkbook.FullName
            .Body = xMailBody
            .Attachments.Add (ThisWorkbook.FullName)
            .Display
        End With
        Set xRgSel = Nothing
        Set xOutApp = Nothing
        Set xMailItem = Nothing
    End If
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Opombe:

1). V kodi, A2: E11 je razpon, na katerega boste poslali e-pošto na podlagi.

2). Prosimo, spremenite telo e-pošte, kot ga potrebujete xMailBody vrstico v kodi.

3). Zamenjajte Email naslov s prejemnikom e-poštni naslov v vrstici .To = "E-poštni naslov".

4). Spremenite zadevo e-pošte v vrstico .Subject = "Delovni list spremenjen v" & ThisWorkbook.FullName.

3. Pritisnite druga + Q tipke za hkratno zapiranje Microsoft Visual Basic za aplikacije okno.

Od zdaj naprej se bo katera koli celica v območju A2: E11 spremenila, novo e-poštno sporočilo bo ustvarjeno s priloženo posodobljeno delovno knjigo. In vsa navedena polja, kot so subjekt, prejemnik in telo e-pošte, bodo navedena v e-poštnem sporočilu. Pošljite e-poštno sporočilo.

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.
    Ioana · 3 months ago
    hi; is there a way to change text displayed using information from other cells (from first row and first column)? for example, if I change cell K15, I want to include in the message info on cells A15 and K1? what should I change in the code? thank you very much
  • To post as a guest, your comment is unpublished.
    Jimmy Joseph · 3 months ago
    I have tried above VBA code: Send email if cell in a specified range is modified in Excel. This VBA works for me except sending email. When the data is modified in the given range an email is automatically generated with modified cell details. However, the email is not automatically sending to the recipient and the user has to click send button in the email. What I am looking here is, the email has to send to the recipients automatically when it is generated. Please help me to provide a code for this. Many thanks
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Jimmy Joseph,
      Please replace the line ".Display" with ".Send". Hope I can help. Thanks for commenting.
  • To post as a guest, your comment is unpublished.
    sagar · 5 months ago
    if i have to send that entire row then?
  • To post as a guest, your comment is unpublished.
    Brad · 6 months ago
    Great information.
    Question regarding the information that can be added to the email.
    Using your example above....

    If you had a value in F4, how would you include the F4 Value in the email that was generated when D4 was modified??
  • To post as a guest, your comment is unpublished.
    Sonu · 6 months ago
    If i want to send the cell value instead of the address..then what shall I change in the code?
    • To post as a guest, your comment is unpublished.
      crystal · 4 months ago
      Hi,
      You can try the below VBA code.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRgSel As Range
      Dim xOutApp As Object
      Dim xMailItem As Object
      Dim xMailBody As String
      On Error Resume Next
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      Set xRg = Range("A2:E11")
      Set xRgSel = Intersect(Target, xRg)
      ActiveWorkbook.Save
      If Not xRgSel Is Nothing Then
      Set xOutApp = CreateObject("Outlook.Application")
      Set xMailItem = xOutApp.CreateItem(0)
      xMailBody = "Cell(s) " & xRgSel.Address(False, False) & _
      xRgSel.Value & _
      " in the worksheet '" & Me.Name & "' were modified on " & _
      Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
      " by " & Environ$("username") & "."

      With xMailItem
      .To = "Email Address"
      .Subject = "Worksheet modified in " & ThisWorkbook.FullName
      .Body = xMailBody
      .Attachments.Add (ThisWorkbook.FullName)
      .Display
      End With
      Set xRgSel = Nothing
      Set xOutApp = Nothing
      Set xMailItem = Nothing
      End If
      Application.DisplayAlerts = True
      Application.ScreenUpdating = True
      End Sub