Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Link Date in Excell cell to Outlook Calendar

  1. #1
    Join Date
    10th January 2005
    Location
    Northern Ireland
    Posts
    81

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    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


  3. #3
    Join Date
    10th January 2005
    Location
    Northern Ireland
    Posts
    81

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    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.

    VB:
    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


  5. #5
    Join Date
    10th January 2005
    Location
    Northern Ireland
    Posts
    81

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Cheers
    Andy


  7. #7
    Join Date
    10th January 2005
    Location
    Northern Ireland
    Posts
    81

    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    Re: Link Date in Excell cell to Outlook Calendar

    Try this mod.

    VB:
    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


  9. #9
    Join Date
    10th January 2005
    Location
    Northern Ireland
    Posts
    81

    Re: Link Date in Excell cell to Outlook Calendar

    Brilliant Andy

    Thanks for all your help.

    Philip

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    4th November 2010
    Posts
    1

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. SharePoint calendar link to Outlook in Vista
    By Dave Hawley in forum Office 2007 Migration and Application Compatibility
    Replies: 0
    Last Post: April 29th, 2009, 11:33
  2. Date In Cell From Calendar
    By lharris01 in forum EXCEL HELP
    Replies: 1
    Last Post: August 21st, 2008, 09:14
  3. Most Searched Training: Calendar I: Outlook calendar basics
    By Dave Hawley in forum Free Microsoft Excel 2007 Tutorials
    Replies: 0
    Last Post: May 2nd, 2008, 12:17
  4. Link to OUtlook Calender from a particular Cell
    By bizjer in forum EXCEL HELP
    Replies: 1
    Last Post: June 8th, 2006, 23:33

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno