1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Trying to add reminders from multiple cells in excell to outlook

Discussion in 'Business Applications' started by Dinobravo6, Nov 29, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Dinobravo6

    Dinobravo6 Thread Starter

    Joined:
    Nov 29, 2011
    Messages:
    14
    Sorry if that has been answered.
    I am using office 2010 and have what I hope to be a simple question.
    I want excell to notice dates in multiple cells and add those dates in each cell as a reminder in my outlook. This is the code I am currently using but it is pulling from 1 cell only, I have tried to use a array but it returns an error.

    Thanks in advance for all your help.
    Here is the code i am currently using:
    Code:
    Sub StoreReminders()
    Dim appOL As Object
    Dim objReminder As Object
     
    Set appOL = GetObject(, "Outlook.application")
    Set objReminder = appOL.CreateItem(1) ' olAppointmentItem
     
    objReminder.Start = Range("b4") ' 11/Dec/2011 18:30
    objReminder.Duration = Range("b1") ' 30
    objReminder.Subject = Range("d1") ' Money Due
    objReminder.ReminderSet = True
    objReminder.Save
    End Sub
    
    BTW I inserted the name of the subject because it was not pulling it from the cell inherently. If needed, I can attach the worksheet (its very basic) essentially I am trying for it to see the dates in cells b4:j4 and mark the respective dates with reminders in outlook. Also if possible, for the macro run upon workbook being opened on its own and not needing to run the macro everytime
    Again, thanks in advance.
     
  2. Dinobravo6

    Dinobravo6 Thread Starter

    Joined:
    Nov 29, 2011
    Messages:
    14
    anyone with any ideas how I can accomplish this task? Thanks in advance.
     
  3. Dinobravo6

    Dinobravo6 Thread Starter

    Joined:
    Nov 29, 2011
    Messages:
    14
    EDIT: Didn't mean to post
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hello, and welcome to the board!

    If you want this to run every time the workbook is opened, do you want to check if the appointment item(s) is there already? What do you want to do if it is encountered previously? Delete the old one and create new? Do nothing?

    What is the range of data you have? Posting a file would help, otherwise please try to explain in detail where you data range is, header rows, data starting rows, data ending rows, etc.
     
  5. Dinobravo6

    Dinobravo6 Thread Starter

    Joined:
    Nov 29, 2011
    Messages:
    14
    Zack thanks very much for your reply.
    Yes I'd like for it to run everytime its opened or possibly closed since times will change upon exit usually. Yes, if it encounters a pervious entry that has changed delete it and add the new (if that possible!!). I have attached a very simple spreadsheet that will be used. It will grow and become much more elaborate, but the ranges in question will must likely remain the same. In the event the ranges grow can I not just edit what you have shown me??? The range of dates I highlight though, I'm sure you got it.....


    EDIT BTW Zack if you notice, I since edited the macro to look at 5 cells by repeating the same set of code 5 times, I'm assuming there is a better way?? Plus, all the other options you asked about that I answered would be amazing if you could intergrate that it.


    Thank you again so much for your help it is greatly appreciated!!

    Dino
     

    Attached Files:

  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    There is nothing attached, Dino.

    If you tell us how and where it will grow to, you will not need to amend anything, as we can make it dynamic. But we would need to know how it would grow. The more you can tell us, the better off you'll be. We can make things fairly easy for you if you'd like. There is a lot of customizations you can do with this.

    And yes, we can delete previous instances. A couple of questions about that though. If you already have the item, why delete and re-create, why not just move on? And pertaining to that question, is there a possibility of updated information? If so, would you want to check all of the fields you listed? Should we add in the notes somewhere the date of when it was changed?

    Whatever you want to do in any of these cases should not be bound by what you may think is possible. In other words, just tell us what you want, we'll figure out what the application can do. :)
     
  7. Dinobravo6

    Dinobravo6 Thread Starter

    Joined:
    Nov 29, 2011
    Messages:
    14
    Wow I really appreciate that truly!! I have since attached the file sorry for the mishap. I will compose a response to your questions right now!!
     
  8. Dinobravo6

    Dinobravo6 Thread Starter

    Joined:
    Nov 29, 2011
    Messages:
    14
    Ok Zack I have attached a workbook as a sample of how I see this flowing.

    There will be a master sheet that will then flow to a chart showing dates, company and money owed. That will then set a reminder to outlook for that date and company.

    As for a previous instance it would be a previous date that might be extended due to a market condition or necessity. The only thing that would obviously change at that point would be date. The actual date it was changed is not necessary just the deletion of prior date and implementation of the new date. Also, if the reminder can kick off a 7 days reminder, that would be great. E.G JLM LLP - due Jan 21, 2012 (scheduled in outlook) the reminder is sent to us on Jan 14, 2012 (that is a luxury if it causes you more time and energy its not necessary as we check every day of course).

    Lastly, can the macro run upon close of the workbook? This might be very helpful, as there is always the chance that info may be changed and new dates recorded.

    I have also made a few notes on the worksheet, on how the macro might be able to populate the appropriate cells on its own. If you have any question please don’t hesitate to send me a post here or you can email me which I will PM you my email address.

    Thank you again very much I can't tell you how appreciative I am!!

    Dino
     

    Attached Files:

  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    In looking at your worksheet, your data structure needs to change. How much latitude are you willing to give? :) In other words, if I take your data, alter your data structure and create code, would you be ok with that?
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Ok, so I took some latitude. I'm posting some code and uploading the sample file I've been working with. I took the liberty of creating a Table on a new worksheet labeled "Data Entry". The Table has a few columns, Company, Amount Due, Date Entered, Due Date, Remaining, Paid.

    Basically what this does is there is a routine which you can run manually, or it will fire when the workbook is closed. I've attached a button with this routine tied to it. NOTE: There is a test line of code in there which will exit after the first cell in the Table is run (I only wanted it to run once after testing instead of the whole Table column and cluttering my Outlook calendar). So this code is not the final code and would need to be amended.

    Here is the code which would need to go into the ThisWorkbook module:
    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Call CheckTable_AddToOutlook
        If Me.Saved = False Then Me.Save
    End Sub
    Here is the code which needs to go into a standard module (in the sample file I named it "modOutlook"):
    Code:
    Option Explicit
    
    '//////////////////////////////////////////////////////////////////////////////////////////////
    'Purpose: To send data from Excel table into specific Outlook calendar.
    'Date created: 12/5/2011
    'Date last modified: 12/5/2011
    'Created by: Zack Barresse
    'References: Microsoft Outlook 14.0 Object Library
    '//////////////////////////////////////////////////////////////////////////////////////////////
    
    Public Const NL                     As String = vbNewLine
    Public Const DNL                    As String = vbNewLine & vbNewLine
    Public Const sDateFormat            As String = "m/d/yyyy"
    
    Const sTestAddy                     As String = "G6"
    
    Sub CheckTable_AddToOutlook()
        Dim c                           As Range
        'Turn off application events/properties to speed up code execution
        Call TOGGLEEVENTS(False)
        For Each c In DataEntry.Range("tblFees[Paid]")
            If c.Address(0, 0) = sTestAddy Then Exit For
            If c.Value = "No" Then
                Call ToOutlook(DataEntry.Cells(c.Row, "B"), _
                               DataEntry.Cells(c.Row, "D").Value, _
                               DataEntry.Cells(c.Row, "E").Value, _
                               DataEntry.Cells(c.Row, "B").Value)
            End If
        Next c
        Call TOGGLEEVENTS(True)
    End Sub
    
    Sub ToOutlook(rCheck As Range, dtStart As Date, dtEnd As Date, sSubject As String)
    
    'Routine variables
        Dim OL                          As Outlook.Application
        Dim olNS                        As Outlook.Namespace
        Dim olFolder                    As Outlook.Folder
        Dim olRecItems                  As Outlook.Folder
        Dim olFilterRecItems            As Outlook.Items
        Dim olAppt                      As Outlook.AppointmentItem
        Dim bOLOpen                     As Boolean
        Dim i                           As Long
        Dim strFilter                   As String
    
        'Set Outlook objects
        On Error Resume Next
        Set OL = GetObject(, "Outlook.Application")
        bOLOpen = True
        If OL Is Nothing Then
            bOLOpen = False
            Set OL = CreateObject("Outlook.Application")
        End If
        On Error GoTo 0
        
        'Ensure Outlook is actually installed and grabbed
        If OL Is Nothing Then
            MsgBox "It appears you do not have Outlook installed.  You must have Outlook to run this functionality.", vbCritical, "ERROR!"
            GoTo ExitRoutine
        End If
        
        'Set Outlook Namespace
        Set olNS = OL.GetNamespace("MAPI")
        Set olFolder = olNS.GetDefaultFolder(olFolderCalendar)
    
        'Set filter string
    '    strFilter = "[Start] = '" & Format(dtStart, sDateFormat) & "'"
    '    strFilter = strFilter & " AND [End] = '" & Format(dtEnd, sDateFormat) & "'"
    '    strFilter = strFilter & " AND [Subject] = ""FEE: " & sSubject & """"
        strFilter = "[Subject] = ""FEE: " & sSubject & """"
        
        'Filter folder
        On Error GoTo NoItems
        Set olFilterRecItems = olFolder.Items.Restrict(strFilter)
    
        'Check count of filtered items (if any)
        If olFilterRecItems.Count = 0 Then GoTo NoItems
    
        'Loop through any items found in filtered folder, delete them
        For i = 1 To olFilterRecItems.Count
            olFilterRecItems.Item(i).Delete
        Next
    
    NoItems:
    
        'Create appointment item in calendar
        Set olAppt = olFolder.Items.Add("IPM.Appointment")
    
        'Set appointment details
        With olAppt
            .Start = dtStart
            IIf dtEnd = 0, dtEnd = dtStart, dtEnd = dtEnd
            .End = dtEnd
            .Subject = "FEE: " & sSubject
            .BusyStatus = olBusy
    '        .Duration = 30
            .ReminderMinutesBeforeStart = 1440    '(one day)
            .ReminderSet = True
            .Save
            .Close olSave
        End With
    
        'Exit routine clean-up, close Outlook if it was already closed
    ExitRoutine:
        If bOLOpen = False Then OL.Quit
        
    End Sub
    
    Public Sub TOGGLEEVENTS(blnState As Boolean)
    'Originally written by Zack Barresse
        With Application
            .DisplayAlerts = blnState
            .EnableEvents = blnState
            .ScreenUpdating = blnState
            If blnState Then .CutCopyMode = False
            If blnState Then .StatusBar = False
        End With
    End Sub
    While the code works, we need to make sure it does what you want it to. I commented the code to make it easier to read, so go through it and see if that indeed works. If nothing else, try the sample file, then let us know what tweaks you may need.

    HTH
     

    Attached Files:

  11. Dinobravo6

    Dinobravo6 Thread Starter

    Joined:
    Nov 29, 2011
    Messages:
    14
    Zack so sorry I am now just getting back to you was out of town on business. I will look at this now and respond with remarks. thank you again
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    No worries. The post isn't going anywhere. :)
     
  13. Dinobravo6

    Dinobravo6 Thread Starter

    Joined:
    Nov 29, 2011
    Messages:
    14
    Zack it looks great a few questions for you:
    1) If under the paid category its marked as "yes", is there a way to change the tab color in Outlook as well, change the name from "Fee: Co. Name" to "Paid: Co. Name" in outlook?
    2) Date in Outlook - can it just show the "Fee: Co. Name" on the actual date it is owned instead of it trailing from date entered to date owed yet issue a reminder a week prior to the event?
    3) Will the macro run upon close on its own when you finish the sheet or will there always be the need to hit the tab b4 exit?

    Again, thank you for the time and effort you have put into this I am very grateful for all your help!!
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    1) Yes. Would that be without a reminder at that point?
    2) No. Sorry. It must have a start and end date. The subject is what should be listed in the view. At least that's what it shows in mine. Do you have different view settnings?
    3) Yes. If you use all the code I posted. The first snippet (explicit where it goes, it MUST go in the ThisWorkbook module) will do this.

    Let me know and I'll finish the code for you. Does that type of data structure work for you? I know I took some liberties.
     
  15. Dinobravo6

    Dinobravo6 Thread Starter

    Joined:
    Nov 29, 2011
    Messages:
    14
    1) correct at the point "yes" is answered the reminder is no longer needed.
    2) yes sorry, the subject is listed on mine I was just using the structure as an example.
    3) ok great

    The structure is great only question I have is: Is there a way to have a master sheet that feeds into the sheet you created? Meaning can I just imput info on sheet 1 that will feed into yours on sheet 2?
    If not its not a problem. also, there will be many more entries, will I be able to add those as norm or will you have to creat extra space so the macro can read further than just the 9 rows currently listed.

    Thanks
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1028993

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice