Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    New Lounger
    Join Date
    Feb 2015
    Location
    Leeds, UK
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Spreadsheet to send reminders: Please Help!

    Hello,

    This is my first time using your website, however after trying to solve this issue myself I stumbled across some of the amazing things you guys can do!!

    I have created a spread sheet (fairly basic, as I am not the most effective with them!) which tracks information on company vehicles. i.e. MOT's/Services ect. I would like this spread sheet to populate certain peoples Outlook calendars and also send them an email 2 weeks before the date of the inspection is due. I have tried other techniques to import a spread in Outlook, which did work however I felt that there was a much smoother and instant way to do this!

    Vehicle Inspections Master SS 1.2.xlsx

    Could you guys help at all?

    Thanks in Advance

    Matthew Pickering

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    The best way is probably to run a macro in the spreadsheet that looks up the dates and sends the email for you. You could have the macro run automatically when you open the ss, or have a button to fire it off once a day. Is this what you had in mind?

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Feb 2015
    Location
    Leeds, UK
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Paul,

    Thanks for getting back to me,

    That is exactly hat I mean, but for it to also populate a diary for a 2 week period, prior to the inspection due date. (if that's possible!)

    Regards

    Matt

  4. #4
    New Lounger
    Join Date
    Feb 2015
    Location
    Leeds, UK
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    what*

  5. #5
    New Lounger
    Join Date
    Feb 2015
    Location
    Leeds, UK
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Can anyone help me on this?

    Thanks!

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Matty,

    I have been putting something together, not yet complete. Placing tasks and reminders on your own calendar is very easy. As far as I know, you cannot drop reminders or tasks onto another's calendar but you could send them a timely task in which they have the option to add it. Much like a meeting invite. Would this meet your needs?

    Maud

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    You can add items to another User's calendar, provided their folder has been shared with you.
    You'll need to know the full path to that public folder.

    zeddy

  8. #8
    New Lounger
    Join Date
    Feb 2015
    Location
    Leeds, UK
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Ok well, a reminder in my calendar followed by an invite to a meeting would be more than sufficient for this! Sounds brilliant!

    Thanks for getting back to me.

    Regards

    Matthew

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zeddy,
    I agree you can add a reminder to someone else's calendar but I do not know if it can be done through vba.

    Matty,
    I am going to play with this a bit more and add some bells and whistles. I'll get back to you shortly

    Maud

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    What about trying something like this:
    Code:
    'Adding Calendar Event to Shared Calendar from Excel:
    'GetSharedDefaultFolder can be used to refer to another user's folder
    'if it has been shared with you, (not to refer to a public folder).
    '
    'If you know the full path of the shared public folder, you can use something like this:
    'Set oFolder = oNamespace.GetDefaultFolder(olPublicFoldersAllPublicFolders)
    'Set oFolder = oFolder.Folders("My Department")
    'Set oFolder = oFolder.Folders("Team Calendar")
    '..to refer to All Public Folders\My Department\Team Calendar.
    
    Sub addAppointment()
    
    Dim oApp As Outlook.Application
    Dim oAppt As Outlook.AppointmentItem
    
    Dim oSession As Outlook.Namespace
    Dim oFolder As Outlook.Folder
    Dim oRecipient As Outlook.Recipient
    
    'CONNECT TO OUTLOOK..
    Set oApp = CreateObject("Outlook.Application")
    Set oSession = oApp.GetNamespace("MAPI")
    oSession.Logon oApp.DefaultProfileName
    
    Set oRecipient = oSession.CreateRecipient("Maudibe Dune")
    oRecipient.Resolve
    
    Set oFolder = oSession.GetSharedDefaultFolder(oRecipient, olFolderCalendar)
    
    'To add an item to the shared calendar:
    Set oAppt = oFolder.Items.Add(olAppointmentItem)
    With oAppt
    .Start = #2/26/2015#
    '.End = #2/27/2015 11:59:59 PM#
    .Duration = 2
    .Subject = "Excel VBA Test"
    .Save
    End With
    
    End Sub
    zeddy

  11. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2015-02-18)

  12. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zeddy,

    Very impressive! Territory I have not yet ventured. Would love to see it in action integrated into Matty's workbook.
    I will yield to the Master

    ("Maudibe Dune")
    Sale on Weirding Modules. 20% off...today only!

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2015-02-19)

  14. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    I meant to write "Dude" - how weird is that, you know what I mean.
    Now, that code I gave was untested - I don't have any shared calendars to test on.
    I was really looking forward to seeing your take on the request.
    So I thank you once again (top marks for getting your 300th!) and pass the buck back to you.

    zeddy

  15. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Matty,

    Here is your revised Spreadsheet. If you click on the Create Reminders button, the code will evaluate all the inspection dates with todays date. If within a 2 week period, a reminder will be created on your calendar and an email will be generated to the appropriate responsible party (column AK) which may be different from the operator in column C. A comment in the Date of Next Inspection cell will be created indicating that the email and reminder was generated. If the code is run again in the future, the presence of a comment will inhibit a duplicate email/reminder from being created again. That goes without saying, if you want another email/reminder to be created again for that inspection then delete the comment. Replace col AL with the actual email addresses of the responsible parties. The code will adjust automatically as you add additional lines of data. Your formulas have been extended down to row 100 and modified not to show status if last inspection date is empty.

    HTH,
    Maud

    Inserted comment:
    Matty1.png

    Created Reminders:
    Matty2.png

    Opened Reminder in Outlook:
    Matty3.png

    Opened Email sent in Outlook
    Matty4.png
    Attached Files Attached Files

  16. The Following User Says Thank You to Maudibe For This Useful Post:

    Matty134 (2015-02-24)

  17. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zeddy,

    I would have liked to have used your code but I do not know Matty's network path for the MS exchange folder. I do in our IT department and I will give it a try when I get a chance. That would open all kinds of doors in communication.

    Maud

  18. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    Opening doors is good.
    But I am a blind man in a dark room looking for a black cat that is not there.
    (i.e. I cannot test that code as I am not allowed)

    Please let me know how it works out.

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

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