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 izpolnjen datum zapadlosti?

Kot je prikazano spodaj prikazano sliko, če je datum zapadlosti v stolpcu C manjši ali enak 7 dnevu (trenutni datum je 2017 / 9 / 13), potem pošljite opomnik za elektronski naslov določenemu prejemniku v stolpcu A z določeno vsebino v stolpcu B. Kako doseči to? V tem članku boste našli metodo VBA, ki bo v njem podrobno obravnavala.

Pošljite e-poštno sporočilo, če je datum izpolnjen s kodo VBA


Pošljite e-poštno sporočilo, če je datum izpolnjen s kodo VBA


Prosimo, naredite na naslednji način, da pošljete opomnik na e-pošto, če je bil v Excelu izpolnjen datum zapadlosti.

1. Pritisnite druga + F11 tipke hkrati odpreti Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Moduli. Nato kopirajte in prilepite spodnjo kodo VBA v okno Modul.

VBA koda: pošlji e-pošto, če je datum zaprta v Excelu

Public Sub CheckAndSendMail()
'Updated by Extendoffice 2018/11/22
    Dim xRgDate As Range
    Dim xRgSend As Range
    Dim xRgText As Range
    Dim xRgDone As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xLastRow As Long
    Dim vbCrLf As String
    Dim xMailBody As String
    Dim xRgDateVal As String
    Dim xRgSendVal As String
    Dim xMailSubject As String
    Dim i As Long
    On Error Resume Next
    Set xRgDate = Application.InputBox("Please select the due date column:", "KuTools For Excel", , , , , , 8)
    If xRgDate Is Nothing Then Exit Sub
    Set xRgSend = Application.InputBox("Please select the recipients?email column:", "KuTools For Excel", , , , , , 8)
    If xRgSend Is Nothing Then Exit Sub
    Set xRgText = Application.InputBox("Select the column with reminded content in your email:", "KuTools For Excel", , , , , , 8)
    If xRgText Is Nothing Then Exit Sub
    xLastRow = xRgDate.Rows.count
    Set xRgDate = xRgDate(1)
    Set xRgSend = xRgSend(1)
    Set xRgText = xRgText(1)
    Set xOutApp = CreateObject("Outlook.Application")
    For i = 1 To xLastRow
        xRgDateVal = ""
        xRgDateVal = xRgDate.Offset(i - 1).Value
        If xRgDateVal <> "" Then
        If CDate(xRgDateVal) - Date <= 7 And CDate(xRgDateVal) - Date > 0 Then
            xRgSendVal = xRgSend.Offset(i - 1).Value
            xMailSubject = xRgText.Offset(i - 1).Value & " on " & xRgDateVal
            vbCrLf = "<br><br>"
            xMailBody = "<HTML><BODY>"
            xMailBody = xMailBody & "Dear " & xRgSendVal & vbCrLf
            xMailBody = xMailBody & "Text : " & xRgText.Offset(i - 1).Value & vbCrLf
            xMailBody = xMailBody & "</BODY></HTML>"
            Set xMailItem = xOutApp.CreateItem(0)
            With xMailItem
                .Subject = xMailSubject
                .To = xRgSendVal
                .HTMLBody = xMailBody
                .Display
                '.Send
            End With
            Set xMailItem = Nothing
        End If
    End If
    Next
    Set xOutApp = Nothing
End Sub

Opombe: Linija Če je CDate (xRgDateVal) - datum <= 7 in CD (xRgDateVal) - Datum> 0 Nato v kodi VBA pomeni, da mora biti datum zapadlosti večji od 1 dnevnega in manj kot ali enak 7 dnevom. Lahko ga spremenite, kot jo potrebujete.

3. Pritisnite o F5 ključ za zagon kode. V prvem pojavljanju Kutools za Excel v pogovornem oknu, prosimo, izberite časovno obdobje koledarja in nato kliknite na OK gumb. Prikaz slike:

4. Potem drugi Kutools za Excel prikaže se pogovorno okno, izberite ustrezen obseg stolpcev, ki vsebuje e-poštne naslove prejemnikov in kliknite na OK gumb. Prikaz slike:

5. V zadnjem Kutools za Excel v pogovornem oknu izberite vsebino, ki jo želite prikazati v telesu e-pošte, in kliknite OK gumb.

Potem bo samodejno ustvarjen e-poštni naslov z navedenim prejemnikom, subjektom in telesom, ki je naveden, če je datum zapadlosti v stolpcu C manjši ali enak 7 dnevu. Prosimo, kliknite na Pošlji gumb za pošiljanje e-pošte.

Opombe:

1. Vsako ustvarjeno e-poštno sporočilo ustreza datumu zapadlosti. Na primer, če obstajajo trije zapadli datumi, izpolnjujejo merila, bodo tri e-poštna sporočila samodejno ustvarjena.

2. Ta koda se ne sproži, če datumi ne izpolnjujejo meril.

3. Koda VBA deluje le, če uporabljate Outlook kot vaš e-poštni program.


Sorodni članki:



Priporočena orodja za produktivnost za Excel

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.
    Dinesh B S · 2 months ago
    in the body i want the column a,b,c value in a table format, like the expired date once reached in the body column i want to print a,b,c cells details in table format
  • To post as a guest, your comment is unpublished.
    Sounder P · 3 months ago
    Hello Sir,

    This is very much helpful. I need 2 more enhancement in the same codes. I have to click on send button every time, it is fine as long as I have 10 email to be sent, what if I >25 emails to be sent in a single day. So please give me a code to send e-mail automatically upon selecting Due date , Recipient, Subject etc.

    Also please provide me with the code to add " CC" option as well


    Thanks

    Sounder P
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Good day,
      Please try the below VBA code, hope I can help. Thanks for commenting.

      Public Sub CheckAndSendMail()
      'Updated by Extendoffice 2018/11/22
      Dim xRgDate As Range
      Dim xRgSend As Range
      Dim xRgText As Range
      Dim xRgDone As Range
      Dim xOutApp As Object
      Dim xMailItem As Object
      Dim xLastRow As Long
      Dim vbCrLf As String
      Dim xMailBody As String
      Dim xRgDateVal As String
      Dim xRgSendVal As String
      Dim xMailSubject As String
      Dim i As Long
      On Error Resume Next
      Set xRgDate = Application.InputBox("Please select the due date column:", "KuTools For Excel", , , , , , 8)
      If xRgDate Is Nothing Then Exit Sub
      Set xRgSend = Application.InputBox("Please select the recipients?email column:", "KuTools For Excel", , , , , , 8)
      If xRgSend Is Nothing Then Exit Sub
      Set xRgCC = Application.InputBox("Please select the CC recipients?email column:", "KuTools For Excel", , , , , , 8)
      If xRgCC Is Nothing Then Exit Sub
      Set xRgText = Application.InputBox("Select the column with reminded content in your email:", "KuTools For Excel", , , , , , 8)
      If xRgText Is Nothing Then Exit Sub
      xLastRow = xRgDate.Rows.Count
      Set xRgDate = xRgDate(1)
      Set xRgSend = xRgSend(1)
      Set xRgCC = xRgCC(1)
      Set xRgText = xRgText(1)
      Set xOutApp = CreateObject("Outlook.Application")
      For i = 1 To xLastRow
      xRgDateVal = ""
      xRgDateVal = xRgDate.Offset(i - 1).Value
      If xRgDateVal <> "" Then
      If CDate(xRgDateVal) - Date <= 7 And CDate(xRgDateVal) - Date > 0 Then
      xRgSendVal = xRgSend.Offset(i - 1).Value
      xRgCCVal = xRgCC.Offset(i - 1).Value
      xMailSubject = xRgText.Offset(i - 1).Value & " on " & xRgDateVal
      vbCrLf = "

      "
      xMailBody = ""
      xMailBody = xMailBody & "Dear " & xRgSendVal & vbCrLf
      xMailBody = xMailBody & "Text : " & xRgText.Offset(i - 1).Value & vbCrLf
      xMailBody = xMailBody & ""
      Set xMailItem = xOutApp.CreateItem(0)
      With xMailItem
      .Subject = xMailSubject
      .To = xRgSendVal
      .Cc = xRgCCVal
      .HTMLBody = xMailBody
      .Send
      End With
      Set xMailItem = Nothing
      End If
      End If
      Next
      Set xOutApp = Nothing
      End Sub
  • To post as a guest, your comment is unpublished.
    Sam Engel · 4 months ago
    Hi, I am using a modified version of this code, and I am rather new to VBA. I was curious if there was a possibility for when the email were to be sent, that the macro would be able to mark an X, or if someone had Complete, that an email would not send. I hope this all makes sense. Any help is very greatly appreciated. Little background, I am using this tool to run a due date check at 30 days, 60 days, and overdue. I want 1 email to go out at 30 days, 1 at 60 days, and then overdue as well. I am running this off of a VB script I wrote, so that I could automate it daily. Thanks!
  • To post as a guest, your comment is unpublished.
    S. Engel · 4 months ago
    Hi, I am using a modified version of this code, and I am rather new to VBA. I was curious if there was a possibility for when the email were to be sent, that the macro would be able to mark an X, or if someone had Complete, that an email would not send. I hope this all makes sense. Any help is very greatly appreciated.

    Little background, I am using this tool to run a due date check at 30 days, 60 days, and overdue. I want 1 email to go out at 30 days, 1 at 60 days, and then overdue as well. I am running this off of a VB script I wrote, so that I could automate it daily. Thanks!
  • To post as a guest, your comment is unpublished.
    Fabrisquie Borges · 4 months ago
    Correctly, it was not with Mr. Ivan, but with Mr. Austin.
    Thank you.