Datum in Excel cel gebruiken voor afspraak in Outlook agenda

[timothee]

Weet iemand of het op een niet al te moeilijke manier mogelijk is om de datum uit een cel in Excel te gebruiken als "trigger" voor het aanmaken van een afspraak in de agenda van Outlook XP op de dag die in die cel aangegeven staat?

Ik heb nl. een Excel bestand gemaakt waarin ik facturen genereer voor klanten die op een ander werkblad staan. Gaat allemaal perfect, ook de koppeling met Word gaat goed om die klangegevens te gebruiken voor het printen van etiketten. Nu wil ik echter dat als ik een vervaldatum voor een factuur in een cel zet, dat er voor die datum een afspraak wordt gemaakt in de agenda van Outlook.

Heb al op GoT en Google gezocht, maar vind niks bruikbaars. Kan iemand me uitleggen of dit te doen is voor iemand die nog nooit geprogrammeerd heeft in Visual Basic?

[onkl]

Op het eerste gezicht lijkt me dit moeilijk worden zonder VB(A).
Ik denk wel dat je grote blokken code zo van het net kan plukken, dus het is niet heel erg moeilijk in VBA.

[martinr]

Ik hoop dat je de link kunt gebruiken (vanwege de lengte), maar hier krijgt iemand wat tips hoe dat te maken:

http://www.microsoft.com/...=&ptlist=&exp=&sloc=en-us

[Pink Panther]

Je moet vanuit VBA een referentie leggen naar Microsoft Outlook obeject library.

Vervolgens voer je volgende macro in....

Sub test()
' Start Outlook.
' If it is already running, you'll use the same instance...
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

' Logon. Doesn't hurt if you are already running and logged on...
Dim olNs As Outlook.Namespace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

' Create a new appointment.
Dim olAppt As Outlook.AppointmentItem
Set olAppt = olApp.CreateItem(olAppointmentItem)

' Set start time for 2-minutes from now...
olAppt.Start = Now() + (2# / 24# / 60#)

' Setup other appointment information...
With olAppt
.Duration = 60
.Subject = "Meeting to discuss plans..."
.Body = "Meeting with " & olItem.FullName & " to discuss plans."
.Location = "Home Office"
.ReminderMinutesBeforeStart = 1
.ReminderSet = True
End With

' Save Appointment...
olAppt.Save

' Clean up...
MsgBox "All done...", vbMsgBoxSetForeground
olNs.Logoff
Set olNs = Nothing
Set olAppt = Nothing
Set olItem = Nothing
Set olApp = Nothing
End Sub

Dit zou moeten werken.....

[timothee]

Hmm...weet niet of ik nu moet zeggen dat het er relatief eenvoudig uitziet of niet Ik heb wel een beetje ervaring met PHP omdat ik nogal eens een website bouw met wat PHP/MySQL snufjes erin, maar van VB(A) en macro's heb ik nog geen kaas gegeten. Desondanks ziet de opbouw van de code er niet bijster moeilijk uit. Het is alleen even voor mij uitpluizen hoe ik e.e.a. aan elkaar moet knopen.

Tot zover bedankt voor de info...kom hier denk ik al wel een eind mee. Zal ook komende week eens aan een VBA ontwikkelaar op het werk vragen of ie wil meehelpen.

Als er nog meer (min of meer kant-en-klaren of bruikbare) suggesties zijn, dan lees ik die graag.

[CoRrRan]

offtopic:
Pink Panther, er is een "nieuwe" manier van code plaatsen in topics:

Pink Panther schreef op zaterdag 11 december 2004 @ 20:58:[...]

Visual Basic:

Sub test()
' Start Outlook.
 ' If it is already running, you'll use the same instance...
   Dim olApp As Outlook.Application
   Set olApp = CreateObject("Outlook.Application")
    
 ' Logon. Doesn't hurt if you are already running and logged on...
   Dim olNs As Outlook.Namespace
   Set olNs = olApp.GetNamespace("MAPI")
   olNs.Logon

 ' Create a new appointment.
   Dim olAppt As Outlook.AppointmentItem
   Set olAppt = olApp.CreateItem(olAppointmentItem)
    

 ' Set start time for 2-minutes from now...
   olAppt.Start = Now() + (2# / 24# / 60#)
    
 ' Setup other appointment information...
   With olAppt
      .Duration = 60
      .Subject = "Meeting to discuss plans..."
      .Body = "Meeting with " & olItem.FullName & " to discuss plans."
      .Location = "Home Office"
      .ReminderMinutesBeforeStart = 1
      .ReminderSet = True
   End With
    
 ' Save Appointment...
   olAppt.Save
 
 ' Clean up...
   MsgBox "All done...", vbMsgBoxSetForeground
   olNs.Logoff
   Set olNs = Nothing
   Set olAppt = Nothing
   Set olItem = Nothing
   Set olApp = Nothing
End Sub

[...]

offtopic:
Dit "leest" wat makkelijker...

martinr schreef op zaterdag 11 december 2004 @ 20:51:
Ik hoop dat je de link kunt gebruiken (vanwege de lengte), maar hier krijgt iemand wat tips hoe dat te maken:[...]

http://www.tinyurl.com

[ Voor 8% gewijzigd door CoRrRan op 12-12-2004 01:59 ]

[timothee]

Ik heb nog een goeie site gevonden via dat excel forum: http://www.dicks-clicks.com/excel/olCalendar.htm Volgens mij is dit ook waar Pink Panther naar refereert.

Met die info zou het moeten lukken als ik enige kennis van VB en macro's had gehad. Op dit moment snap ik nog niet hoe ik de touwtjes aan elkaar moet knopen.
Ik heb vanuit Excel via het menu Extra -> Macro de VB editor opgestart en ik heb een vinkje gezet bij de verwijzing (referentie) naar Microsoft Outlook 10.0 Object Library. Maar hoe ik nu verder moet en waar ik die code die op boven genoemde site staat moet invoeren en hoe ik e.e.a. vervolgens gekoppeld krijg aan die datum cel is me op dit moment nog volstrekt een raadsel

Denk dat ik eerst maar eens wat info over macro's moet gaan opzoeken, tenzij iemand me in een paar stappen beknopt kan uitleggen wat de logische volgorde is...dan zou ik denk ik al geholpen zijn.

[timothee]

Okay, ik ben alweer wat verder

Ik heb op basis van het voorbeeld van Pink Panther en het voorbeeld van die site die ik hiervoor noemde het volgende gemaakt:

Visual Basic:

Sub Factuur_reminder()
' Start Outlook.
 ' If it is already running, you'll use the same instance...
   Dim olApp As Outlook.Application
   Set olApp = CreateObject("Outlook.Application")
     
 ' Logon. Doesn't hurt if you are already running and logged on...
   Dim olNs As Outlook.NameSpace
   Set olNs = olApp.GetNamespace("MAPI")
   olNs.Logon

 ' Create a new appointment.
   Dim olAppt As Outlook.AppointmentItem
   Set olAppt = olApp.CreateItem(olAppointmentItem)
     
 ' Setup appointment ...
   With olAppt
      .Start = Date + 1 + TimeValue("19:00:00")
      .Duration = 30
      .Subject = "Nieuwe factuur sturen naar..."
      .Body = "Nieuwe factuur sturen naar KLANT XYZ."
      .ReminderMinutesBeforeStart = 30
      .ReminderSet = True
   End With
     
 ' Save Appointment...
   olAppt.Save
  
 ' Clean up...
   MsgBox "Reminder aangemaakt in Outlook agenda...", vbMsgBoxSetForeground
   olNs.Logoff
   Set olNs = Nothing
   Set olAppt = Nothing
   Set olItem = Nothing
   Set olApp = Nothing
End Sub

Als ik nu vanuit Excel naar menuoptie Extra ga en vervolgens zeg dat ie de macro Factuur_reminder moet uitvoeren, dan plaatst ie netjes een afspraak in mijn Outlook agenda en geeft ie me een melding "Reminder aangemaakt in Outlook agenda...". Werkt fantastisch zo!

Echter...hij plaatst de afspraak nu volgens code altijd 1 dag nadat de macro is uitgevoerd (dus ik test vandaag en de afspraak wordt voor morgen in de agenda gezet) om 19:00 uur en de afspraak duurt altijd 30 minuten. Ook het onderwerp en de body tekst zijn altijd hetzelfde.
Ik wil echter een aantal dingen variabel hebben. De tijd, de duur en het onderwerp vind ik prima zo, die mogen altijd hetzelfde zijn. De datum waarop de afspraak in de agenda gezet wordt en de body tekst moeten echter variabel zijn.

Ik wil het volgende:
Op basis van een aantal variabelen in diverse cellen op het Excel werkblad, rekent excel automatisch de vervaldatum voor de betreffende factuur uit en zet die datum in een bepaalde cel neer. Vervolgens wil ik dus dat die macro automatisch uitgevoerd wordt zodra in die betreffende cel een vervaldatum komt te staan en de macro moet die datum meenemen in de berekening voor het plaatsen van de afspraak. Dus als ik vandaag een rekening aanmaak die voor een half jaar geldig is, dan rekent Excel automatisch uit dat er op 12-06-2005 een nieuwe factuur gestuurd moet worden...dat is dan dus de vervaldatum. Ik wil dat de macro deze datum dan neemt en op die dag een afspraak in Outlook zet.
Vervolgens wil ik ook dat de tekst "Klant XYZ" uit de body variabel is. In de factuur staat in een cel de naam van de klant. Die naam wil ik dat opgenomen wordt in de macro als klantnaam.


Kan iemand mij uitleggen hoe ik die macro aan een vaste cel gekoppeld krijg, zodanig dat ie automatisch uitgevoerd wordt zodra die cel gevuld is? En kan iemand me tevens vertellen hoe ik de vervaldatum en de klantnaam als variabelen in de macro code krijg?

[onkl]

Stel dat in cel A1 van blad "Sheet1" het aantal vervaldagen staat en in A2 je klantnaam,
vervang dan:

Visual Basic:

.Start = Date + 1 + TimeValue("19:00:00") 
'in
.Start = Date + ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value+ TimeValue("19:00:00") 
.Body = "Nieuwe factuur sturen naar KLANT XYZ." 
'wordt:
.Body = "Nieuwe factuur sturen naar "& ActiveWorkbook.Worksheets("Sheet1").Range("A2").Value

edit: reageren op een celwijziging.
Als je in je VBA editor bent, zoek dan in je project het werkblad op waarin je cel staat (da's in die explorer boom in het Alt-F11 scherm, niet in Excel zelf). Dubbelklik en plak de volgende code:

Visual Basic:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    MsgBox "Je hebt cel A1 veranderd. Stouterd!!11"
End If
End Sub

Verander nu cel A1 in het bijpassende blad en huiver .
De MsgBox regel kan natuurlijk ook code bevatten, of type er Factuur_Reminder, gaat het in een keer goed.

BTW: gokje, je wilt de afspraak aanmaken zodra de cel veranderd. Let er dan op dat als een druiloor variant van collega die zich tien keer vergist bij het invullen van die cel er ook tien afspraken in Outlook worden gegenereerd (en dat wil je niet). Misschien kan je beter een knopje inbouwen of zo.

[ Voor 57% gewijzigd door onkl op 13-12-2004 14:29 ]

[timothee]

Geweldig, bedankt voor die input onkl (en de rest natuurlijk ook voor de eerdere hints)!! Hij doet het

Ik bedacht me inderdaad dat het met een knopje minder foutgevoelig zou zijn en aangezien ik als enige van dit bestand gebruik maak weet ik wel dat ik elke keer op het knopje moet klikken als ik een factuur gemaakt en geprint heb om nog even een reminder in de outlook agenda te zetten.
Ik heb nu een cel gemaakt met een lijst erin die uit vier andere cellen waarden uitleest. Hierdoor kan ik in die cel kiezen of de vervaltermijn van de factuur een maand, kwartaal, halfjaar of jaar is. Afhankelijk van die termijn in die cel, wordt in een andere cel 30, 90, 180 of 360 gezet en die waardes telt hij door jouw bovenstaande code op bij de datum van vandaag. Op die dag die hij daarmee uitrekent zet hij dus na een druk op het knopje een afspraak in de agenda! Niet 100% nauwkeurig, maar voldoende voor mijn doel

De macro ziet er uiteindelijk als volgt uit:

Visual Basic:

Sub Factuur_reminder()
' Start Outlook.
 ' If it is already running, you'll use the same instance...
   Dim olApp As Outlook.Application
   Set olApp = CreateObject("Outlook.Application")
     
 ' Logon. Doesn't hurt if you are already running and logged on...
   Dim olNs As Outlook.NameSpace
   Set olNs = olApp.GetNamespace("MAPI")
   olNs.Logon

 ' Create a new appointment.
   Dim olAppt As Outlook.AppointmentItem
   Set olAppt = olApp.CreateItem(olAppointmentItem)
     
 ' Setup appointment ...
   With olAppt
      .Start = Date + ActiveWorkbook.Worksheets("Factuur").Range("O15").Value + TimeValue("19:00:00")
      .Duration = 30
      .Subject = "Nieuwe factuur sturen naar..."
      .Body = "Nieuwe factuur sturen naar " & ActiveWorkbook.Worksheets("Factuur").Range("D13").Value
      .ReminderMinutesBeforeStart = 30
      .ReminderSet = True
   End With
     
 ' Save Appointment...
   olAppt.Save
  
 ' Clean up...
   MsgBox "Reminder aangemaakt in Outlook agenda...", vbMsgBoxSetForeground
   olNs.Logoff
   Set olNs = Nothing
   Set olAppt = Nothing
   Set olItem = Nothing
   Set olApp = Nothing
End Sub

Als iemand interesse heeft in het factuur bestand, dan hoor ik het wel....het is echt geweldig wat je met een pakket als Excel kunt (daar hoef je geen dure boekhoudprogramma's voor te kopen)

[onkl]

Ik werk al een tijd mee met de implementatie van boekhoudpakketten. Excel is idd een fantastisch geval, maar geloof me, er zijn toch nog een paar situaties waarbij een boekhoudpakket een klein beetje meerwaarde heeft

[timothee]

Hallo allemaal...we zijn inmiddels alweer geruime tijd verder en ik heb al die tijd prima gebruik kunnen maken van deze macro in Excel Nu ik echter voor een vriend van me wat meer met macro's heb moeten doen en in Excel zo'n beetje een database heb kunnen bouwe, vond ik het tijd worden om deze macro toch op wat puntjes bij te schaven

Het probleem is alleen dat ik er dus niet uit kom

De macro ziet er nu als volgt uit:

Visual Basic:

Sub Factuur_reminder()
' Start Outlook.
 ' If it is already running, you'll use the same instance...
   Dim olApp As Outlook.Application
   Set olApp = CreateObject("Outlook.Application")
     
 ' Logon. Doesn't hurt if you are already running and logged on...
   Dim olNs As Outlook.NameSpace
   Set olNs = olApp.GetNamespace("MAPI")
   olNs.Logon

 ' Create a new appointment.
   Dim olAppt As Outlook.AppointmentItem
   Set olAppt = olApp.CreateItem(olAppointmentItem)
     
 ' Setup appointment ...
   With olAppt
      '.Start = Date + ActiveWorkbook.Worksheets("Factuur").Range("O15").Value + TimeValue("19:00:00")
      .Start = Format(ActiveWorkbook.Worksheets("Factuur").Range("M13").Value, "dd.mm.yyyy") + ActiveWorkbook.Worksheets("Factuur").Range("O15").Value + TimeValue("19:00:00")
      '.Start = Format(ActiveCell.Value, "dd.mm.yyyy") & " 08:00"
      .Duration = 30
      .Subject = "Nieuwe factuur sturen naar..."
      .Body = "Nieuwe factuur sturen naar " & ActiveWorkbook.Worksheets("Factuur").Range("D13").Value & " (vorige factuur: " & ActiveWorkbook.Worksheets("Factuur").Range("M14").Value & ")"
      .ReminderMinutesBeforeStart = 30
      .ReminderSet = True
   End With
     
 ' Save Appointment...
   olAppt.Save
  
 ' Clean up...
   MsgBox "Reminder aangemaakt in Outlook agenda...", vbMsgBoxSetForeground
   olNs.Logoff
   Set olNs = Nothing
   Set olAppt = Nothing
   Set olItem = Nothing
   Set olApp = Nothing
End Sub

Bij het aanmaken van een nieuw agenda-item gebruikt hij de dag van vandaag (Date) en telt daar de waarde op van cel O15. Dat werkt prima. Omdat ik wel eens een factuur iets te laat aan maak en dus wil antidateren moet hij niet van de systeemdatum uitgaan maar de datum overnemen uit cel M13 en daar de waarde (een x aantal dagen) uit cel O15 bij optellen.
Omdat ik vermoed dat ik moet opgeven wat voor formaat cel M13 heeft (nl. een datumformaat), heb ik wat gezocht met Google en een heel klein beetje info gevonden. Op basis daarvan kun je zien dat ik wat heb zitten proberen. Ik krijg echter telkens de melding dat de formaten niet overeenkomen. Ik vermoed dat de formaten van de cellen M13 en O15 niet matchen en dat kan goed kloppen. Cel M13 bevat getallen die samen via het formaat dd-mm-yyyy een datum vormen en cel O15 bevat gewoon een getal dat het aantal dagen weergeeft (30 voor een maand, 90 voor een kwartaal enz.).

Kan iemand mij vertellen hoe ik die datum variabel maak op basis van cel M13 zodanig dat hij de inhoud van cel O15 erbij kan optellen?

Mijn dank is groot!

[timothee]

Niemand die me hiermee kan helpen? Op zich zou je verwachten dat het simpel is om op te geven dat hij de inhoud van twee voorgedefinieerde cellen moet optellen maar de complexiteit ligt erin dat het om een datumnotatie gaat en niet zomaar een getal als celinhoud. Ik kom er niet uit hoe ik moet definiëren dat het om een datumveld gaat waar ik iets bij wil optellen.

[onkl]

In VBA heb je het variabele type date.
Goede kans dat iets als

Visual Basic:

dim mijndag as date
dim volgendemaand as date
mijndag=ActiveWorkbook.Worksheets("Factuur").Range("M13").Value
volgendemaand =mijndag +ActiveWorkbook.Worksheets("Factuur").Range("O13").Value

gewoon werkt.
Die Format maakt van je datum juist expliciet een string, daarmee kan je echt niet rekenen.

[ Voor 23% gewijzigd door onkl op 07-08-2006 20:41 ]

[timothee]

Onkl, bedankt! Ik heb je suggestie geprobeerd en het werkt perfect.

Voor degene die er interesse in heeft, dit is het eindresultaat:

Visual Basic:

Sub Factuur_reminder()
' Start Outlook.
 ' If it is already running, you'll use the same instance...
   Dim olApp As Outlook.Application
   Set olApp = CreateObject("Outlook.Application")
     
 ' Logon. Doesn't hurt if you are already running and logged on...
   Dim olNs As Outlook.NameSpace
   Set olNs = olApp.GetNamespace("MAPI")
   olNs.Logon

 ' Create a new appointment.
   
    Dim factuurdatum As Date
    Dim nieuwefactuurdatum As Date
    factuurdatum = ActiveWorkbook.Worksheets("Factuur").Range("M13").Value
    nieuwefactuurdatum = factuurdatum + ActiveWorkbook.Worksheets("Factuur").Range("O15").Value + TimeValue("19:00:00")
   
   Dim olAppt As Outlook.AppointmentItem
   Set olAppt = olApp.CreateItem(olAppointmentItem)
     
 ' Setup appointment ...
   With olAppt
      .Start = nieuwefactuurdatum
      .Duration = 30
      .Subject = "Nieuwe factuur sturen naar..."
      .Body = "Nieuwe factuur sturen naar " & ActiveWorkbook.Worksheets("Factuur").Range("D13").Value & " (vorige factuur: " & ActiveWorkbook.Worksheets("Factuur").Range("M14").Value & ")"
      .ReminderMinutesBeforeStart = 30
      .ReminderSet = True
   End With
     
 ' Save Appointment...
   olAppt.Save
  
  
  ' Create second new appointment.
   'Dim olAppt As Outlook.AppointmentItem
   Set olAppt = olApp.CreateItem(olAppointmentItem)
     
 ' Setup appointment ...
   With olAppt
      .Start = factuurdatum + 14 + TimeValue("19:00:00")
      .Duration = 30
      .Subject = "Betaling checken van " & ActiveWorkbook.Worksheets("Factuur").Range("D13").Value
      .Body = "Betaling checken van " & ActiveWorkbook.Worksheets("Factuur").Range("D13").Value & " n.a.v. factuur: " & ActiveWorkbook.Worksheets("Factuur").Range("M14").Value
      .ReminderMinutesBeforeStart = 30
      .ReminderSet = True
   End With
     
 ' Save Appointment...
   olAppt.Save
  
  
 ' Clean up...
   MsgBox "Reminders aangemaakt in Outlook agenda...", vbMsgBoxSetForeground
   olNs.Logoff
   Set olNs = Nothing
   Set olAppt = Nothing
   Set olItem = Nothing
   Set olApp = Nothing
End Sub

Ik heb trouwens ook meteen een tweede appointment eraan toegevoegd die van een betalingstermijn van 14 dagen uit gaat. Zo hoef ik niet "handmatig" bij te houden wanneer de betalingstermijn afloopt van een factuur.

[ Voor 4% gewijzigd door timothee op 10-08-2006 11:11 ]

[Anoniem: 186673]

timothee, ik heb zeker interesse in jouw excel bestand, ben nu zelf bezig en krijg nog steeds de foutmelding dat het subscript buiten het bereik zou vallen

[timothee]

Anoniem: 186673 schreef op donderdag 24 augustus 2006 @ 10:02:
timothee, ik heb zeker interesse in jouw excel bestand, ben nu zelf bezig en krijg nog steeds de foutmelding dat het subscript buiten het bereik zou vallen

Mail me maar even op timothee apestaartje minddigger punt nl.

[riZZy]

Zou het nou moeilijk zijn om het zo te maken dat je de afspraak in outlook ook kunt verzetten?(vanuit Excel/Access). Houdt outlook een id bij van de gemaakte afspraak zodat je de juiste afspraak kunt verplaatsen??

Lijkt me reuze handig!

[Anoniem: 186673]

Heeft er iemand een idee wat hier fout aan is? ik kan het niet vinden en volgens mij staat jet er toch echt goed???

Nutteloos om die hele lap code hier te posten zonder enige foutmelding of zelfs maar de regel waar de foutmelding komt

[ Voor 76% gewijzigd door Lustucru op 30-08-2006 09:46 ]

[Anoniem: 163896]

Zullen we het zo doen

en dan ga jij hem nog eens quoten

[ Voor 94% gewijzigd door Lustucru op 30-08-2006 09:46 ]

[Anoniem: 186673]

ja dat kreeg ik al niet echt voor elkaar zeg maar maar bedankt.

HIj geeft bij regel
revisiedatum = ActiveWorkbook.Worksheets("Map1").Range("G8").Value + TimeValue("10:00:00")

fout 9: het subscript valt buiten het bereik.

Iemand enig idee waar dit in kan zitten?

P.S: ik gebruik dit ook pas een paar weken dus misschien is het heel simpel maar ik kom er niet uit ;-)

[Anoniem: 186673]

Heeft niemand toevallig een idee?

[Lustucru]

Hoi Bierens,

Allereerst welkom op GoT

ZO help je langzaam maar zeker dit topic om zeep door er met te weinig informatie een privehelpdesk van te maken. Als je een foutmelding krijgt is het slim om meteen erbij te vermelden in welke regel de fout optreedt en welke melding het is. Mocht je nog meer fouten krijgen dan is het wellicht handiger om even een eigen topic te openen. Lees even Algemene gedragsregels (Netiquette) om te weten waar je op moet letten bij het starten van een draadje.

(VBA) Code maak je op door er [code=vb][/code] tags omheen te zetten. Zie ook de Overzicht van UBB-codes.

On topic: de foutmelding geeft aan dat je een element uit een array opvraagt dat niet bestaat. In de regel die je quote zou dat betekenen dat er geen "Map1" bestaat.