Note: The other languages of the website are Google-translated. Back to English
Accedi  \/ 
x
or
x
Registrati  \/ 
x

or

Come creare promemoria di Outlook da un foglio di calcolo Excel?

Questo articolo parla della creazione di promemoria di Outlook basati sui dati del foglio di calcolo Excel.

Promemoria di Outlook di Creta dal foglio di calcolo Excel con codice VBA


Promemoria di Outlook di Creta dal foglio di calcolo Excel con codice VBA

Se desideri creare promemoria di Outlook da Excel, procedi come segue.

1. Creare un foglio di lavoro contenente le intestazioni delle colonne e i campi di promemoria corrispondenti come mostrato nell'immagine sottostante.

Note:: Per il stato occupato colonna, numero 2 significa che il promemoria verrà mostrato come Occupato nel calendario di Outlook. Puoi cambiarlo in 1 (provvisorio), 3 (Fuori sede), 4 (Lavorare altrove), o 5 (gratuito) di cui hai bisogno.

2. premi il altro + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.

3. Nel Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo. Quindi copia sotto il codice VBA nella finestra del codice.

Codice VBA: crea promemoria di Outlook dal foglio di calcolo Excel

Sub AddAppointments()
'Update by Extendoffice 20180608
    Dim I As Long
    Dim xRg As Range
    Dim xOutApp As Object
    Dim xOutItem As Object
    Set xOutApp = CreateObject("Outlook.Application")
    Set xRg = Range("A2:G2")
    For I = 1 To xRg.Rows.Count
        Set xOutItem = xOutApp.createitem(1)
        Debug.Print xRg.Cells(I, 1).Value
        xOutItem.Subject = xRg.Cells(I, 1).Value
        xOutItem.Location = xRg.Cells(I, 2).Value
        xOutItem.Start = xRg.Cells(I, 3).Value
        xOutItem.Duration = xRg.Cells(I, 4).Value
        If Trim(xRg.Cells(I, 5).Value) = "" Then
            xOutItem.BusyStatus = 2
        Else
            xOutItem.BusyStatus = xRg.Cells(I, 5).Value
        End If
        If xRg.Cells(I, 6).Value > 0 Then
            xOutItem.ReminderSet = True
            xOutItem.ReminderMinutesBeforeStart = xRg.Cells(I, 6).Value
        Else
            xOutItem.ReminderSet = False
        End If
        xOutItem.Body = xRg.Cells(I, 7).Value
        xOutItem.Save
        Set xOutItem = Nothing
    Next
    Set xOutApp = Nothing
End Sub

Note:: Nel codice sopra, A2: G2 è l'intervallo di dati su cui desideri creare appuntamenti.

4. premi il F5 o fare clic sul pulsante Esegui per eseguire il codice. Quindi tutti gli appuntamenti con campi specifici verranno inseriti nel calendario di Outlook contemporaneamente.

E poi, puoi andare al calendario della tua prospettiva per visualizzare i risultati, vedi screenshot: 


Kutools for Outlook: porta 100 funzionalità avanzate in Outlook e semplifica notevolmente il lavoro!

  • Auto CC / BCC dalle regole durante l'invio di e-mail; Avanzamento automatico Email multiple personalizzate; Risposta automatica senza server di scambio e più funzioni automatiche ...
  • Avviso BCC - mostra il messaggio quando provi a rispondere a tutti se il tuo indirizzo e-mail è nell'elenco BCC; Ricorda quando mancano allegatie altre funzioni di promemoria ...
  • Rispondi (tutti) con tutti gli allegati nella conversazione di posta; Rispondi a molte email in secondi; Aggiunta automatica di saluto quando rispondi; Aggiungi la data all'oggetto ...
  • Strumenti per gli allegati: gestisci tutti gli allegati in tutti i messaggi, Scollegamento automatico, Comprimi tutto, Rinomina tutto, Salva tutto ... Rapporto rapido, Conta le email selezionate...
  • E-mail spazzatura potenti per abitudine; Rimuovi messaggi e contatti duplicati... Consentono di fare in modo più intelligente, veloce e migliore in Outlook.
girato kutools outlook scheda kutools 1180x121
girato kutools outlook kutools plus tab 1180x121
 
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.
    Sruthi · 2 days ago
    Hi, I wanted to include this in a shared document as we all share one and write tasks assigned to each one of us. How do I get the email id of the one who has been assigned the task and create a reminder in their outlook respectively ? 

    Thanks
  • To post as a guest, your comment is unpublished.
    NewYears1978 · 4 months ago
    Error "This object does not support this action" on xOutItem.ReminderMinutesBeforeStart = xRg.Cells(I, 6).Value
  • To post as a guest, your comment is unpublished.
    Muss · 6 months ago
    Hi, many thanks for this code. It work beautifully on windows. Do you happen to know how i can implement this on Mac? I've been trying to find a way for a few days now, one way i've found is to create an AppleScript but i have no knowledge of how to do this. Another way was to use a Virtual Machine running Windows 10 however it's not my ideal way of working.
  • To post as a guest, your comment is unpublished.
    Liz · 7 months ago
    Hi, is it possible to specify which calendar the reminder will be added to. I would like to add them to a shared mailbox calendar so all in the team get the reminder, rather than my personal email calendar
  • To post as a guest, your comment is unpublished.
    Guest2 · 1 years ago
    So we have an excel spreadsheet that we enter expiration dates for licenses, and we are wondering how we would do it so that when we add new expiration dates it will automatically add them to our outlook calendar with reminders a month prior and 2 weeks prior. What would that require?
  • To post as a guest, your comment is unpublished.
    Guest · 1 years ago
    i have some dynamic table in excel to be made available in body of reminder. Can we do that ? please suggest how
  • To post as a guest, your comment is unpublished.
    Abe · 1 years ago
    Hello,

    When I follow the exact steps with Microsoft Outlook open and running in the background, running the macro gives me an error " Compile error: invalid outside procedure". Any help would be appreciated.

    Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Abe,
      As the reminder fields have been created in a worksheet, you need to add the code to Microsoft Excel workbook's Module code window and run it in Excel.
  • To post as a guest, your comment is unpublished.
    chan · 1 years ago
    Hi, Is there a way to automate running this code, eg. when opening outlook?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi chan,
      As the code is saved in Excel workbook, we can't automatically run it when opening Outlook. Sorry for that.
  • To post as a guest, your comment is unpublished.
    Juicy · 1 years ago
    Hi..
    Shall we run this macro everytime to activate the reminder ? or we only need to click just once ? then its ok tu torn of the computer and close the excel file ?

    Thanks.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Juicy,
      Just need one click to run the code, then prepared reminders will be automatically created in the Outlook calendar. After that, you can do anything as you like.
  • To post as a guest, your comment is unpublished.
    vineet · 2 years ago
    when we use VBA code for reminder then error shows
    Compliler error :
    User define type is not define
    after press ok button
    cursor move on DIM xRg as Range

    please help...??
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi vineet,
      Sorry for the inconvenience. Which Excel version are you using?
  • To post as a guest, your comment is unpublished.
    Sandi · 2 years ago
    If I use this in a gantt chart and I update due dates must I re run? Is there an easy way to remove from calendar without deleting each individual one?
  • To post as a guest, your comment is unpublished.
    Shruthy · 2 years ago
    Hi,

    Is it possible to use code to make entry on group calendar in outlook?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi,
      Sorry can't help solving the problem yet.
  • To post as a guest, your comment is unpublished.
    Carlos Xavier · 2 years ago
    Do you know how i can put a all day event?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi,
      Sorry can't help solving the problem yet.
  • To post as a guest, your comment is unpublished.
    singgih · 2 years ago
    dear webmaster
    thank you for the code. I have a problem as it failed to implemented. (run-time error '-21447467263 (80004001)' : Not implemented.
    when I press the debug button it highlights the "xOutItem.Save" code line 28.
    could you give solution of this issue?
    regards
    Singgih
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi singgih,
      Sorry for replying so late. I need to know your Excel version in order to figure out the problem. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    H · 2 years ago
    Hi Crystal! Great code.

    I need to add/invite attendees. Any idea?
  • To post as a guest, your comment is unpublished.
    Dharma · 2 years ago
    how to add others attendees as well in this appointment.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Dharma,
      Sorry the probem can't be solved yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    cb · 2 years ago
    this code works but only for the first entry. How do you adjust it for range: Sheet3!A2:G17 ?

    I changed the code to say: Set xRg = Range("Set xRg = Range("A2:G2") Yet it still only picks the first line up in outlook.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good day,
      Please replace the line Set xRg = Range("A2:G2") with Set xRg = Worksheets("Sheet3").Range("A2:G17").
      Thank you for your comment.
      • To post as a guest, your comment is unpublished.
        Andre · 2 years ago
        Hi Crystal,
        I already replaced the line and it works, but 'body' in second row and so on are not come out in reminder, but only picks first row.
        Please advise..
  • To post as a guest, your comment is unpublished.
    Keith · 2 years ago
    Hello, the code seems very helpful but has one drawback, If the file is updated continuously it will create a new reminder each time it is run. Can a line be added to delete all previous reminders and then create new ones?
    Also can this be exported to multiple outlook accounts?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Keith,
      Sorry can't help you with that. You need to delete all previous reminders manually in Outlook. And the reminders are added to the default Outlook account only. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Alexandra · 2 years ago
    Hello! Love the idea of this code, but I have a few questions:


    1. It won't run because of the "bug" on this line (error 440, Run time error):
    xOutItem.ReminderMinutesBeforeStart = xRg.Cells(I, 6).Value
    2. If I don't necessarily want to put the column headers from A2:G2, can i just simply change it in the code (ex: F3-K3), will it still work?
    3. How does it know to connect to my outlook?


    Thank you for your response, I'd really appreciate it!
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Alexandra,
      The 440 error didn't occur in my case. Which Office version do you use?
      The code won't work if you simply change the code to F3:K3. If you need the answer, i will try to figure it out.
      This line "Set xOutApp = CreateObject("Outlook.Application")" will help to connect the Excel data to the Outlook application.
      Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Saybier@gmail.com · 2 years ago
    Hello, thank you for the code but I'm struggling with it only putting in the first entry in excel into Outlook. How do i get it to enter in all line items?
    Here is my code, I added in a category color


    Sub AddAppointments()
    'Update by Extendoffice 20180608
    Dim I As Long
    Dim xRg As Range
    Dim xOutApp As Object
    Dim xOutItem As Object
    Set xOutApp = CreateObject("Outlook.Application")
    Set xRg = Range("A2:G2")
    For I = 1 To xRg.Rows.Count
    Set xOutItem = xOutApp.createitem(1)
    Debug.Print xRg.Cells(I, 1).Value
    xOutItem.Subject = xRg.Cells(I, 1).Value
    xOutItem.Location = xRg.Cells(I, 2).Value
    xOutItem.Start = xRg.Cells(I, 3) + xRg.Cells(I, 8).Value
    xOutItem.Duration = xRg.Cells(I, 4).Value
    xOutItem.Categories = xRg.Cells(I, 9).Value
    If Trim(xRg.Cells(I, 5).Value) = "" Then
    xOutItem.BusyStatus = 2
    Else
    xOutItem.BusyStatus = xRg.Cells(I, 5).Value
    End If
    If xRg.Cells(I, 6).Value > 0 Then
    xOutItem.ReminderSet = True
    xOutItem.ReminderMinutesBeforeStart = xRg.Cells(I, 6).Value
    Else
    xOutItem.ReminderSet = False
    End If
    xOutItem.Body = xRg.Cells(I, 7).Value
    xOutItem.Save
    Set xOutItem = Nothing
    Next
    Set xOutApp = Nothing
    End Sub
    • To post as a guest, your comment is unpublished.
      Dennis Briggs · 2 years ago
      I'm having the same issue, the code works great but is only adding the first appointment to the calendar. Here is my code:
      <code>
      Sub AddAppointments()
      'Update by Extendoffice 20180608
      Dim I As Long
      Dim xRg As Range
      Dim xOutApp As Object
      Dim xOutItem As Object
      Set xOutApp = CreateObject("Outlook.Application")
      Set xRg = Range("A2:G2")
      For I = 1 To xRg.Rows.Count
      Set xOutItem = xOutApp.createitem(1)
      Debug.Print xRg.Cells(I, 1).Value
      xOutItem.Subject = xRg.Cells(I, 1).Value
      xOutItem.Location = xRg.Cells(I, 2).Value
      xOutItem.Start = xRg.Cells(I, 3).Value
      xOutItem.Duration = xRg.Cells(I, 4).Value
      If Trim(xRg.Cells(I, 5).Value) = "" Then
      xOutItem.BusyStatus = 2
      Else
      xOutItem.BusyStatus = xRg.Cells(I, 5).Value
      End If
      If xRg.Cells(I, 6).Value > 0 Then
      xOutItem.ReminderSet = True
      xOutItem.ReminderMinutesBeforeStart = xRg.Cells(I, 6).Value
      Else
      xOutItem.ReminderSet = False
      End If
      xOutItem.Body = xRg.Cells(I, 7).Value
      xOutItem.Save
      Set xOutItem = Nothing
      Next
      Set xOutApp = Nothing
      End Sub
      </code>
      • To post as a guest, your comment is unpublished.
        mog · 2 years ago
        You need to change Set xRg = Range("A2:G2") to be A":wherever your data ends, so if you have 20 rows it will be A2:G20.
  • To post as a guest, your comment is unpublished.
    Ahmed · 3 years ago
    Hi There,

    Great formula/code.


    What happens that this code generates multiple entries in the outlook calendar if its run more than once by mistake. I also wanted it to be setup as a template so that staff can use it. They can put in the dates and rest of the details are required and when they run it should list the entries in outlook calendar. Any deleted excel data should be deleted from outlook as well and same applies to any changes. When I save the excel spreadsheet it saves in the default excel format, but I want it to be saved with Macros so that it does not lose the code and staff can use it as many times as needed. Can you please advise and make relevant changes to the code? Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Hi,
      For saving the macros for future use in the Workbook, you need to click File > Save As > Browse > select a folder to save the workbook > choose “Excel Macro-enabled Workbook” from the Save as type drop-down > Save.
      • To post as a guest, your comment is unpublished.
        Gitika Khanchandani · 1 years ago
        Hi,

        How can I send the reminders to multiple people? Please help.