Skip i'r prif gynnwys

Sut i anfon nodyn atgoffa neu hysbysiad e-bost os yw'r llyfr gwaith yn cael ei ddiweddaru yn Excel?

Mewn rhai achosion, efallai y bydd angen i chi sbarduno hysbysiad e-bost i dderbynnydd penodol os oes data wedi'i ddiweddaru yn y llyfr gwaith rydych wedi'i anfon. Bydd yr erthygl hon yn dangos dull i chi ei gyflawni.

Anfonwch nodyn atgoffa e-bost neu hysbysiad os yw'r llyfr gwaith yn cael ei ddiweddaru gyda chod VBA

Anfonwch nodyn atgoffa e-bost neu hysbysiad os yw'r llyfr gwaith yn cael ei ddiweddaru gyda chod VBA

Gwnewch fel a ganlyn i anfon nodyn atgoffa e-bost neu hysbysiad os yw'r llyfr gwaith yn cael ei ddiweddaru.

1. Yn y llyfr gwaith, agorwch daflen waith benodol byddwch yn anfon hysbysiad e-bost yn seiliedig ar y data wedi'i ddiweddaru y tu mewn.

2. De-gliciwch y tab dalen, a dewis Gweld y Cod o'r ddewislen clicio ar y dde. Gweler y screenshot:

2. Yn y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, copïo a gludo'r cod canlynol i'r ffenestr cod. Gweler y screenshot:

Cod VBA: Anfon nodyn atgoffa e-bost neu hysbysiad os yw'r llyfr gwaith yn cael ei ddiweddaru

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 2017/9/14
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xName As String
    Dim xYesOrNo As Integer
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailItem = xOutApp.CreateItem(0)
    xYesOrNo = MsgBox("Want to attach updated workbook in email?", vbInformation + vbYesNo, "KuTools For Excel")
    If xYesOrNo = 6 Then ActiveWorkbook.Save
    If xYesOrNo = 6 Then xName = ActiveWorkbook.FullName
    With xMailItem
        .To = "Email Address"
        .cc = ""
        .Subject = "email notification test"
        .Body = "Hi," & Chr(13) & Chr(13) & "File is now updated."
        If xYesOrNo = 6 Then .Attachments.Add xName
        .Display
    End With
    xMailItem = Nothing
    xOutApp = Nothing
End Sub

Nodyn: Amnewid y Cyfeiriad E-bost gyda'r cyfeiriad e-bost derbynnydd yn unol .To = "Cyfeiriad E-bost". A newid y Cc, Pwnc yn ogystal â meysydd corff yn y cod VBA yn ôl yr angen.

3. Gwasgwch y Alt + Q allweddi i gau'r Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

4. Ar ôl golygu cell yn y daflen waith, a Kutools ar gyfer Excel bydd blwch deialog yn ymddangos fel y dangosir isod. Os ydych chi am atodi'r llyfr gwaith wedi'i ddiweddaru yn yr e-bost, cliciwch ar y Ydy botwm. Os na, cliciwch y Na botwm.

5. Yna mae e-bost yn cael ei greu yn awtomatig gyda'r llyfr gwaith wedi'i ddiweddaru neu hebddo. Ac mae'r holl feysydd penodedig hefyd wedi'u rhestru yn yr e-bost. Cliciwch y anfon botwm i'w anfon.

Nodyn: Dim ond pan fyddwch chi'n defnyddio Outlook fel eich rhaglen e-bost y mae'r cod VBA yn gweithio.

Erthyglau cysylltiedig:

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad

Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (20)
No ratings yet. Be the first to rate!
rahul rajan
rahul rajan
This comment was minimized by the moderator on the site
I am following certificates log in excel sheet. i need to get reminder before the certificates getting expired. please advice me.
Reply
0
0
crystal
crystal     rahul rajan
This comment was minimized by the moderator on the site
Hi rahul rajan,

Do you want to pop up a reminder based on the date of a cell?
Reply
Report
0
0
donkeh
donkeh
This comment was minimized by the moderator on the site
Hi which part of the code should i delete so it only sends email when i click save and not on every action ?
Reply
0
0
crystal
crystal     donkeh
This comment was minimized by the moderator on the site
Hi donkeh,
In the VBA code, please replace the following line:
.Display
with
.Send
Reply
Report
0
0
GERARDO MARTIN OLVERA
GERARDO MARTIN OLVERA
This comment was minimized by the moderator on the site
HOLA CRISTAL, ME GUSTARIA PONERME EN CONTACTO DIRECTO CONTIGO, PARA PEDIR TU AYUDA EN UN PROBLEMITA EN UNA HOJA DE EXCEL
Reply
0
0
crystal
crystal     GERARDO MARTIN OLVERA
This comment was minimized by the moderator on the site
Hi GERARDO MARTIN OLVERA,
You can email me at .
Reply
Report
0
0
GERARDO MARTIN OLVERA
GERARDO MARTIN OLVERA
This comment was minimized by the moderator on the site
HOLA CRISTAL, ME GUSTARIA PONERME EN CONTACTO DIRECTO CONTIGO, NE CESITO DE TU AYUDA EN EXCEL,GRACIAS
Reply
0
0
guest
guest
This comment was minimized by the moderator on the site
please i am a bit confused but where in the code does it detect the sheet being modified/updated?
i am trying to write a code where the table on a particular sheet gets sent when the table is modified (or more specifically when rows are added)
thank you!
Reply
0
0
crystal
crystal     guest
This comment was minimized by the moderator on the site
Hi,
The code in the post detects all cells in the entire worksheet based on the first line of "Private Sub Worksheet_Change(ByVal Target As Range)".
If you want to send an email with a particular table when it is modified, you can try the following VBA.
Note: please change "Table1" to your own table name.
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 2022/5/27
    Dim xYesOrNo As Integer
    Dim xTableName As String
    Dim xTableRg As Range
    Dim I, J As Long
    Dim xEmailBody As String
    Dim xMailOut As Object
    Dim xOutApp As Object
    On Error Resume Next
    
    xTableName = "Table1" 'The name of the table you will send when it is modified
    Set xTableRg = ActiveSheet.ListObjects(xTableName).Range
    If Intersect(Target, xTableRg) Is Nothing Then Exit Sub

    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailOut = xOutApp.CreateItem(olMailItem)
    For I = 1 To xTableRg.Rows.Count
        For J = 1 To xTableRg.Columns.Count
            xEmailBody = xEmailBody & "  " & xTableRg.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 = ""
        .Body = xEmailBody
        .Display
        '.Send
    End With
    Set xMailOut = Nothing
    Set xOutApp = Nothing
End Sub
Reply
Report
0
0
Matias
Matias     crystal
This comment was minimized by the moderator on the site
Hola cristal, donde dice Private Sub Worksheet_Change, deberia poner el nombre de mi excel o no?
Los rangos de (I,J) cambiarlos por los valores de celdas que yo dese estar mirando si cambian?
Reply
0
0
Guest
Guest
This comment was minimized by the moderator on the site
For me the code runs again and again, its like never ending loop. I close email it asks again to send it...
Reply
0
0
MJ
MJ
This comment was minimized by the moderator on the site
When I tried to change something in a cell a message prompted for Outlook email configuration and it just keeps on asking for my password.
Reply
0
0
crystal
crystal     MJ
This comment was minimized by the moderator on the site
Hi MJ,
Which Excel version are you using? Can you provide a screenshot of the prompt?
Reply
Report
0
0
James Crook
James Crook
This comment was minimized by the moderator on the site
Sorry, I posted this in the wrong article. I will post in the correct article
Reply
0
0
James Crook
James Crook
This comment was minimized by the moderator on the site
Hello there, The code works great. However, when a cell changes to below the threshold point via a macro operation that copies and pastes the lower value into the designated cell, the code doesn't seem to trigger.

Also, I would like to apply this code to several cells. Can I copy and paste the code several times and change each copied version to fit each cells' needs? Is there a better way to do that. I can't figure that out. Thank you!
Reply
0
0
Juno24
Juno24
This comment was minimized by the moderator on the site
I copied and pasted the code exactly as-is from this page, but when I make a change to my document I get the message "Syntax Error" and this line highlights: Dim xOutApp As Object. Any help would be appreciated.
Reply
Report
0
0
crystal
crystal     Juno24
This comment was minimized by the moderator on the site
Hi Judy,
Would you provide the Excel version you are using? Thank you for your comment.
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations