Mae cwcis yn ein helpu i gyflwyno ein gwasanaethau. Drwy ddefnyddio ein gwasanaethau, rydych yn cytuno i'n defnydd cwcis.
Tip: Mae ieithoedd eraill yn Google-Cyfieithu. Gallwch ymweld â'r English fersiwn o'r ddolen hon.
Mewngofnodi
x
or
x
x
cofrestr
x

or

Sut i greu atgoffa Outlook o daenlen Excel?

Mae'r erthygl hon yn sôn am greu atgoffa Outlook yn seiliedig ar ddata taenlen Excel.

Atgoffa Crete Outlook o daenlen Excel gyda chod VBA

Kutools ar gyfer Outlook: 100 + Offer Uwch Newydd ar gyfer Outlook.
Tab Swyddfa: Galluogi Golygu Tablau a Pori yn y Swyddfa, Just Like Chrome, Firefox, IE 8 / 9 / 10.
Bwydlen Clasurol: Dewch â Hen Fwydlenni a Pheiriau Offer Yn ôl i Office 2007, 2010, 2013, 2016 a 2019.

Atgoffa Crete Outlook o daenlen Excel gyda chod VBA

Os ydych chi am greu atgoffa Outlook gan Excel, gwnewch fel a ganlyn.

1. Mae creu taflen waith yn cynnwys y penawdau colofn a'r meysydd atgoffa cyfatebol fel y dangosir y sgrin isod.

Nodyn: Am y statws prysur colofn, rhif 2 yn golygu y dangosir yr atgoffa fel Prysur yn eich calendr Outlook. Gallwch ei newid i 1 (Tentative), 3 (Allan o'r Swyddfa), 4 (Gweithio mewn mannau eraill), Neu 5 (Am ddim) fel y mae arnoch ei angen.

2. Gwasgwch y Alt + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

3. Yn y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, cliciwch Mewnosod > Modiwlau. Yna copïwch y cod VBA isod i mewn i ffenestr y Cod.

Cod VBA: Creu atgoffa Outlook o daenlen 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

Nodyn: Yn y cod uchod, A2: G2 yw'r amrediad data yr ydych am greu apwyntiadau yn seiliedig arno.

4. Gwasgwch y F5 neu cliciwch y botwm Run i redeg y cod. Yna bydd yr holl benodiadau gyda meysydd penodol yn cael eu mewnosod i mewn i galendr eich Outlook ar unwaith.

Ac yna, gallwch fynd i Calendr eich rhagolwg i weld y canlyniadau, Gweler y sgrin:



Kutools ar gyfer Outlook

Mwy na Swyddogaethau Uwch 100 ar gyfer Outlook 2010, 2013, 2016, 2019 a 365

sgrinio kutools ar gyfer rhagolygon bach ar gyfer baner 201812

Mwy o Nodweddion | Lawrlwythiad Am Ddim | $ 39.00 yn unig ar gyfer cannoedd o swyddogaethau

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.
    Alexandra · 9 days 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.
    Michelle · 23 days 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.
    Riaz · 3 months 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 · 2 months 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.