Results 1 to 2 of 2

Thread: Office 2010 - Create Appointment from Data in Excel with specific issues

  1. #1

    Office 2010 - Create Appointment from Data in Excel with specific issues



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there,

    I have been reading some of your forum and I have been able to piece together some code which for the most part works. However I have some glitches with it, I am using Office 2010 (fully patched on Win8).

    Basically I want to do the following:
    • Open a specific Outlook Profile (I've tried using - "Dim oNs As Outlook.NameSpace = oApp.GetNamespace("mapi") oNs.Logon("Outlook", "myPassword", false, True") but it doesn't work)i
    • Create an appointment for dates in column 'n' (starting at n8)
    • Create an appointment at a set time (10:30am)
    • Pause between Opening Outlook & running part 2 of the VBA (the appointments). I have tried using "Application.Wait (Now + TimeValue("0:02:30"))" however it doesn't work



    First, the code (as a whole)
    Code:
    Public Function OpenWithShell(ByVal strFilePath As String) As Boolean'Author       : Ken Puls (link removed as per forum rules)
    'Macro Purpose: To open any file in the appropriate application
        'Test for file existence to avoid Windows error message
        If Not Dir(strFilePath) = vbNullString Then
            'Open the file
            Shell ("RunDLL32.EXE shell32.dll,ShellExec_RunDLL " & strFilePath)
            OpenWithShell = True
        End If
    End Function
    
    
    Sub OpenFileExample()
    'Author       : Ken Puls (link removed as per forum rules)
    'Macro Purpose: To demonstrate the use of the OpenWithShell function
        Dim strFullFileName As String
        
        'Set path to file
        strFullFileName = "E:\My Programs\My Data\1 Outlook 2010\Outlook Resources\Extra Outlook\IRIS - Admin.bat"
        
        'Check if file opened
        If OpenWithShell(strFullFileName) = False Then _
            MsgBox ("File not opened!")
    End Sub
    
    
    
    
    Sub SetAppt()
        Dim olApp As Outlook.Application
        Dim olApt As AppointmentItem
        Dim MySheet As Worksheet
        
        Set MySheet = Worksheets("sql all 20131228")
        Set olApp = New Outlook.Application
        Set olApt = olApp.CreateItem(olAppointmentItem)
            
        With olApt
            .Start = Range("n8")
            .Subject = "EOM Reports #1"
            .Location = "Office"
            .Body = "Start of Month, EOM Reports"
            .BusyStatus = olBusy
            .ReminderMinutesBeforeStart = 60
            .Categories = "Acc - 1st Report"
            'Becomes: "Acc - EOM Final" when 2nd appointment runs'
            .ReminderSet = True
            .Save
        End With
    
    
        Set olApt = Nothing
        Set olApp = Nothing
    
    
    End Sub
    Issues:

    • The spreadsheet is a sales history and has thousands of transactions on it, I only want 1 reminder per calendar month on the First Tuesday of the following month at 10:30.
    • I want to schedule the appointments for the 1st Tuesday & the 4th last day of the following month from the date in the cells
      App1: from: Feb-2014 becomes: 4th March 2014
      App2: from: Feb-2014 becomes: 27th March 2014 (I can set up a duplicate copy of this completed code to run the 2nd event)
    • I have a column that I can calculate that appointment time which I can call in the VBA, however I still need only the Unique number (I may have 50 rows of May 4th 2012, I only want 1 appointment).
    • Dates: Ideally I want it to cycle through the column and pick ONLY the unique whole date based on month (May, June, July etc) not for each time listed date field
    • Date = 22/05/2012 21:40 formatted as: (custom) yyyy-mm-mmm (2012-05-May) -- The VBA picks it at 22 May 2012 @ 21:40 instead of the Month/Year.

      • I would much rather have it as "Date @ 10:30am"

    • I would prefer only forward dates, no back filling of the calendar (so February 2014 onwards). If necessary I can omit the data in the initial run of the macro
    • Outlook 2010 keeps closing if it's not open. With the 2nd lot of data (EOM Reports) I want it to trigger an email from the Categories (I have code that works) and it won't send when Outlook keeps closing.


    Any ideas on how to arrange/fix my code please?

    Many thanks in advance.
    Last edited by mrsklb09; 2014-01-10 at 04:20 PM.

  2. #2
    Sorry for the double post my edit option is no longer showing.


    The other issue I have is when adding the Macro to my Ribbon is that it's showing the 3 sets of code as individual pieces (they are written in the same module):
    1) OpenFileExample
    2) SetAppt

    How do I get the "module" name (AppointmentMaker) now to show in the ribbon so the code runs as 1, not individuals?
    Running: Win8/Office 2010 Skill Lvl: Please be kind to me!

Tags for this Thread

Posting Permissions

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