Automate recurring appointments on MS Outlook



Here is another piece of code that is fun when it comes to automating data from excel and setting up outlook recurring appointments.

strExcelPath = ""


Const olAppointmentItem = 1
Const olRecursWeekly = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.WorkBooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

intRow = 3
Do While objSheet.Cells(intRow, 1).Value <> ""
    strName = objSheet.Cells(intRow, 3).Value
    strDate = objSheet.Cells(intRow, 4).Value
   
    Set objOutlook = CreateObject("Outlook.Application")
    Set objAppointment = objOutlook.CreateItem(olAppointmentItem)

    strStart = strDate & "/2012 11:00 AM"
    strEnd = strDate & "/2099 11:00 AM"
    objAppointment.Start = strStart
    objAppointment.Duration = 30
    objAppointment.Subject = strName & " Birthday Reminder"
    objAppointment.Body = "Today is " &strName& "'s Birthday!"
    objAppointment.Location = "Team Decides"
    objAppointment.ReminderMinutesBeforeStart = 15
    objAppointment.ReminderSet = True

    Set objRecurrence = objAppointment.GetRecurrencePattern
    objRecurrence.RecurrenceType = 5
    objRecurrence.PatternStartDate = strStart
    objRecurrence.PatternEndDate = strEnd

    objAppointment.Save
   
    Set objRecurrence = nothing
    Set objAppointment = nothing
    Set objOutlook = nothing
 
    intRow = intRow + 1
Loop

' Close workbook and quit Excel.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
set objSheet = nothing
set objExcel = nothing

Happy automating!

Comments

  1. Aditya Kalra (Ady)22/6/12

    Technology Specific Guide for QTP is a new book that has got great feedback from all the readers. More on feedback and where to buy the book from here:
    http://bluemagic-automation.blogspot.in/

    ReplyDelete

Post a Comment

Popular posts from this blog

Software Testing @ Microsoft

Image
In continuation from my previous  series of posts  targeting testing in top few companies from the technology space. After  Software Testing @Facebook  and @Google  we shall now look at Microsoft: Testing@Microsoft Software Development Engineers in Test (SDETs) are usually just called Test and sometimes Software Testing. SDETs are responsible for maintaining high testing and qualityassurance standards for all Microsoft products. Software Development Engineers (SDEs) are often referred to as Software Development. SDEs write the code that drives Microsoft products and upgrades. Very few teams in Microsoft are still using SDETs to do significant amount of manual testing. Manual testing is mainly outsourced. Actually, as early as in 2004, for example, most of the manual testing of MSN Explorer was outsourced. Think about it this way: it just doesn't make sense for test manager to spend their headcount (SDETs) on something can be easily outsourced.  Until 2005, Microso
Read more

Trim / Remove spaces in Xpath?

Image
If you are wondering how you can trim the spaces in your xpath, this is how: The normalize-space function strips leading and trailing white-space from a string, replaces sequences of white-space characters by a single space, and returns the resulting string. Example : Which one is better? //td[starts-with(normalize-space(),'Text to Trim')] //td[starts-with(normalize-space(text()),' Text to Trim ')] The second option is better as it targets at the specific nodes More string functions in XPATH: Functions Description starts-with( string1 ,  string2 ) Returns true if the first string starts with the second string. contains( string1 ,  string2 ) Returns true if the first string contains the second string. substring( string offset length ) Returns a section of the string. The section starts at  offset (which is a number), and is as long as the value provided at  length. substring-before( string1 ,  string2 ) Returns the part of  string1  up unti
Read more