Announcement

Collapse
No announcement yet.

Link Date in Excell cell to Outlook Calendar

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Link Date in Excell cell to Outlook Calendar



    Hi

    I have created a spreadsheet with code on a command box to insert a new line and copy formulae etc into the new line (using advice gained from this site). One of the cells (G4) calculates a due date from information in another worksheet and I would like to create a calendar appointment (or task) for that date in Outlook, which will inform the recipient of the task (text in cell C4 & D4).

    I have looked at previous posts and have seen code but I know very little about VBA and codes and would appreciate it if someone could take me step by step through it for example some of the code listed said “Do Not forget To include a reference To the Outlook Library” this was included in the code – is that a note to remind me to do this or should it be in the code? And, secondly how do I do that?

    Any help will be greatly appreciated.

    Thanks

    BigRed

  • #2
    Re: Link Date in Excell cell to Outlook Calendar

    Hi BigRed,

    The reminder to "include reference to library" is something you need to.

    Go to the VBE (Visual Basic Editor) ALT+F11
    Use the menu Tools > References
    From the list choose the library you need to reference. Once referenced the compiler will understand properties and methods about these new objects.

    So if you reference the Outlook library you can now declare variables as MailItem and the compiler will know. It will even provide intellisense prompts.

    If you post an example of what you already have and what you need help on I'm sure we can help.

    Cheers
    Andy

    Comment


    • #3
      Re: Link Date in Excell cell to Outlook Calendar

      Thanks for your reply Andy.

      I have attached an example of what i would like to do. The date inserted in column A is the date a request comes in and column B looks up the Due Dates sheet and inserts (in bold red) a working day 5 days later. I would like a calendar entry to be automatically created in outlook 2002 (using xp) on that date.

      You may wish to note its not my personal outlook mailbox but a public one used by 5 staff - i have found out that the reminder option on public mailbox calendars doesnt work, so i may have to amend my spreadsheet to include another cell into which an email address is inserted so the calendar entry is sent to that person's own account, but im not sure if im just making more work for myself or not. Am i making sense? i hope so.

      Please read this thread i started a couple of weeks ago requesting previous help with this spreadsheet. it may make more sense?!? http://www.ozgrid.com/forum/showthread.php?t=28154

      BigRed
      Attached Files

      Comment


      • #4
        Re: Link Date in Excell cell to Outlook Calendar

        Hi BigRed,

        Here is a routine, using late binding, that will add entrys to you calender.
        Not sure if this be in the local or shared calender.

        Code:
        Private Sub AddToOLCalendar()
            
            Dim objOL As Object
            Dim objItem As Object
            Dim lngRow As Long
            
            Set objOL = CreateObject("Outlook.Application")
            
            lngRow = 4
            Do While ActiveSheet.Cells(lngRow, 1) <> ""
                If ActiveSheet.Cells(lngRow, 2).Text <> "" Then
                    Set objItem = objOL.CreateItem(1) ' constant olAppointmentItem = 1
                     
                    With objItem
                        .Body = "Message"
                        .Duration = 10
                        .Start = Range("B4") & " 9:00:00 AM"
                        .Subject = "Subject Text"
                        .Save
                    End With
                End If
                lngRow = lngRow + 1
            Loop
            Set objItem = Nothing
            Set objOL = Nothing
             
        End Sub

        Cheers
        Andy

        Comment


        • #5
          Re: Link Date in Excell cell to Outlook Calendar

          Andy

          I did what you said. Opened the example1 file, did Alt +F11 then Tools +References, found 'Microsoft Outlook 10.0 Object Library' and clicked the Priority button until it was 5th in the list behind 'VBA', 'Microsoft Excel .... Library', 'OLE Automation' and 'Microsoft Office ... Library'. Then I copied and pasted your code into the big white code box (im sure there is a proper name for that box - apologies for my ignorance ) on the right hand side of the screen. I then saved it and inserted a new date in cell A4 to test it. No appt appeared in either my own calendar or in the public calendar. I have attached the file again if you wouldnt mind having a check and make sure i did everything i was supposed to.

          I dont know if this makes a difference but everything (including my personal outlook mailbox) is held on servers in my organisation rather than on pc hard disks.

          Thanks again for taking the time to assist me.

          Philip
          Attached Files

          Comment


          • #6
            Re: Link Date in Excell cell to Outlook Calendar

            Hi Philip,

            I moved the code to a standard code module instead of in the sheets.
            You show now be able to see the routine if you use Tools > Macro > Run Macro.

            As this uses late binding you do not need to woory about the references.

            The code works for me, although my calander is not shared.
            Attached Files

            Cheers
            Andy

            Comment


            • #7
              Re: Link Date in Excell cell to Outlook Calendar

              Thanks Andy that worked perfectly.

              Can i just ask one more question - just in case it is decided 'up above' that an email would be preferable would you be able to give me the code for that (for future reference).

              Thanks again for your help with this one

              Philip

              Comment


              • #8
                Re: Link Date in Excell cell to Outlook Calendar

                Try this mod.

                Code:
                Sub EmailInstead()
                     
                    Dim objOL As Object
                    Dim objItem As Object
                    Dim lngRow As Long
                     
                    Set objOL = CreateObject("Outlook.Application")
                     
                    lngRow = 4
                    Do While ActiveSheet.Cells(lngRow, 1) <> ""
                        If ActiveSheet.Cells(lngRow, 2).Text <> "" Then
                            Set objItem = objOL.CreateItem(0) 'constant olMailItem = 0
                             
                            With objItem
                                .Body = "Message"
                                .Recipients.Add Range("C4")
                                .Subject = "Subject Text"
                                .Save
                                .Send
                            End With
                        End If
                        lngRow = lngRow + 1
                    Loop
                    Set objItem = Nothing
                    Set objOL = Nothing
                     
                End Sub

                Cheers
                Andy

                Comment


                • #9
                  Re: Link Date in Excell cell to Outlook Calendar

                  Brilliant Andy

                  Thanks for all your help.

                  Philip

                  Comment


                  • #10
                    Re: Link Date in Excell cell to Outlook Calendar

                    HI Just a simple question: how can I create a link from excel (will be a date) wich will open the relative outlook calendar date tasks?

                    Thank you

                    Comment


                    • #11


                      Re: Link Date in Excell cell to Outlook Calendar

                      Ziggy12,

                      Ozgrid forum rules allow only one question (or very close follow-ups) by the original poster only per thread. Please start your own thread for your question and if it helps to explain your problem then link back to a previous thread.

                      Thank you

                      Comment

                      Working...
                      X