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

mrsklb09

New member
Joined
Jan 10, 2014
Messages
2
Reaction score
0
Points
0
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:

mrsklb09

New member
Joined
Jan 10, 2014
Messages
2
Reaction score
0
Points
0
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?
 
Top