+ Reply to Thread
Results 1 to 18 of 18

Excel macro to create appointments in outlook calandar

  1. #1
    Registered User
    Join Date
    01-18-2012
    Location
    Suffolk, UK
    MS-Off Ver
    Excel 2007
    Posts
    11

    Excel macro to create appointments in outlook calandar

    Hi. I have a spreadsheet that has become a useful work management tool. I deal with planning applications and each one has a reference number (colmun A) and expiry dates (colmun F). I would like a macro that runs and for each row, ie each application, puts in an appointment in outlook. This way outlook can remind me when things expire and I can see whats coming up. I have the following, but it only works for the first application (first row) and does not continue to create seperate appointments for each of the other applications in the list (now reaching about 600) I am new to VB so I don't understand half of this code as I copied from others with similar ideas. Help.

    Sub Appointments()
    Const olAppointmentItem As Long = 1
    Dim olApp As Object
    Dim OLNS As Object
    Dim OLAppointment As Object

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then Set olApp = CreateObject("Outlook.Application")
    On Error GoTo 0

    If Not olApp Is Nothing Then

    Set OLNS = olApp.GetNamespace("MAPI")
    OLNS.Logon

    Set OLAppointment = olApp.CreateItem(olAppointmentItem)
    OLAppointment.Subject = Range("A2").Value
    OLAppointment.Start = Range("F2").Value
    OLAppointment.Duration = 30
    OLAppointment.ReminderMinutesBeforeStart = 15
    OLAppointment.Save

    Set OLAppointment = Nothing
    Set OLNS = Nothing
    Set olApp = Nothing
    End If

    End Sub

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Excel macro to create appointments in outlook calandar

    You need a loop and a look over here.



  3. #3
    Registered User
    Join Date
    01-18-2012
    Location
    Suffolk, UK
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Excel macro to create appointments in outlook calandar

    Ok now I have this


    Sub appointment()
    With CreateObject("Outlook.Application").CreateItem(1)
    .Subject = Range("A2").Value
    .Start = Range("F2").Value + TimeValue("12:30")
    .Duration = 45
    .Save
    End With
    End Sub

    Its a lot more simplier so thank you, but I have the same problem in that only one appointment is created. I have put in Loop at the end and other locations, but get error message. Where does loop go?

  4. #4
    Registered User
    Join Date
    01-18-2012
    Location
    Suffolk, UK
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Excel macro to create appointments in outlook calandar

    If helps I have attahced a copy (i hope) of the spreadsheet Case List JPGtest.xlsm

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Excel macro to create appointments in outlook calandar

    Please use code tags (see the forum rules)

    Please Login or Register  to view this content.
    Last edited by snb; 01-19-2012 at 04:49 AM.

  6. #6
    Registered User
    Join Date
    01-18-2012
    Location
    Suffolk, UK
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Excel macro to create appointments in outlook calandar

    Sorry to say that does not work. It states there is a Syntax Error and the second line refering to With CreateObject is highlighted red.
    Sorry not used tags

    John

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Excel macro to create appointments in outlook calandar

    I amended the previous code: displaced the period.

  8. #8
    Registered User
    Join Date
    01-18-2012
    Location
    Suffolk, UK
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Excel macro to create appointments in outlook calandar

    Many thanks you are a star. It works like a dream and I can even select which cells I need, despite not understanding most of it. Great stuff. John

  9. #9
    Registered User
    Join Date
    01-18-2012
    Location
    Suffolk, UK
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Excel macro to create appointments in outlook calandar

    While I guess I might be pushing my luck. Now I have found the macro so useful, I wonder if I can use it to email the relevant people. In otherwords inputs the expiry date as a calendar event not just for me, but using a list of email address in another column alongside also puts a meeting request or task to others s they can accept the reminder for themselves?

    I have tried adding .To = Cells(j, 7.Value within the code for the list of email addresses to send to, but that does not work in itself. I have found a code string that allows me to create a list of people and their email addresses and emails them, but its not enough as I need to set up a meeting request/appointment based on the expiry date within the spreadsheet so they get the reminder at the appropriate time. Any help would be great.

    Please Login or Register  to view this content.

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Excel macro to create appointments in outlook calandar

    if you are interested have a look over here

    Please Login or Register  to view this content.
    Last edited by snb; 02-22-2012 at 07:25 AM.

  11. #11
    Registered User
    Join Date
    01-18-2012
    Location
    Suffolk, UK
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Excel macro to create appointments in outlook calandar

    Hi there thanks for coming back. I have tried your suggested code, but I get a run time error 1004. The only thing I have changed is which row to take the information from. The debug highlights the .Subject....etc line. I have tried moving "For j = 1 to 4" back up to the top, but then it does not like the . Recipenints.Add...etc line. Any further suggestion, your help is much appeciaited.

    Please Login or Register  to view this content.

  12. #12
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Excel macro to create appointments in outlook calandar

    A typo:

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    01-18-2012
    Location
    Suffolk, UK
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Excel macro to create appointments in outlook calandar

    Sadly not, getting the same error message despite correcting the spelling error.

  14. #14
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Excel macro to create appointments in outlook calandar

    In that case you have to show the contents of cells G1, G2, G3 and G4.

  15. #15
    Registered User
    Join Date
    01-18-2012
    Location
    Suffolk, UK
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Excel macro to create appointments in outlook calandar

    Hi,

    I have had a play and have come up with this.

    Please Login or Register  to view this content.
    This enters the information into my calander as before as meeting organiser and when I look at the list of attendees I see that the email address obtained from j 7 is there. I thought I might have solved it, but sadly when I askd if the person I indeeded to get the calendar approintment/meeting request got it as well, they didn't.

    I thought I was missing ".send" and so added that in as well, but the same happened and the other people did not get a meeting request etc.

    Does the .Send need to be in a different place? I have also attached the speadsheet this time, its purpose is to test the idea out rather than the full version I can transfer the macro to later.

    Again very much your help is welcome.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    01-18-2012
    Location
    Suffolk, UK
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Excel macro to create appointments in outlook calandar

    Hi again,

    Just to say I have solved it and thanks for your help.

    Final code below.

    Please Login or Register  to view this content.
    All the best

  17. #17
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Excel macro to create appointments in outlook calandar

    you do not have to use both 'save' & 'send'.
    to make it more rubust, refer to the sheet that contains the data:

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    01-18-2012
    Location
    Suffolk, UK
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Excel macro to create appointments in outlook calandar

    Well now it all works and in fact so successfully I have been asked to generate the dates for outlook not just for my team, but the whole section. This helps everyone, but my own calandar looks a mess. The main reason being I am the meeting organiser, thus I essentially get the master copy and I know everyone's expiry dates as and well they come up. When it incldues 30 people and each has 40+ pieces of work at any one time you can imagine my view on outlook. I can transfer them to a seperate calendar manually (eg you have run more than one calander in outlook), but would like a less labour intensive way.

    So I was thinking

    a) Anyone got an idea how to stop being the meeting organiser so the meeting request only goes to the relevant people?

    b) Failing that how are you able to get it to put your entries in a second calendar, not your primary one.

    The code I have so far below


    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1