Solved

VBScript to retrieve Custom Form Properties in Outlook

Posted on 2004-10-07
13
1,116 Views
Last Modified: 2008-01-09
Experts,

I've been trying to access the RequiredAttendees and Body of an AppointmentItem using CDO.  This is an external script I would like to run that isn't the code running behind an Outlook Form.   The VBScript that I'm using is getting an error if I try to access the aforementioned properties.  In an attempt to try to avoid using RequiredAttendees and Body, I've created a custom form that has a section to input Recipients and Comments.  I was hoping that this would replace my need to get information from the RequiredAttendees and Body items which are seemingly restricted.  However, in doing so I found out that I'm also not able to access the Item.UserProperties("Custom Element") properties from VBScript.  Am I missing something, or is there no way to accomplish either of these w/ VBScript using CDO?

My Code (To attempt to access Body or RequiredAttendees):

Const sServerName = "VDCA0113"
Dim sMailboxName
Dim oConnect
Dim command
Dim rs
Dim strSearchBase

set oConnect = CreateObject("ADODB.Connection")
Set command = CreateObject("ADODB.Command")

strADSI_Query = "(&(&(&(& (mailnickname=*******) (| (&(objectCategory=person)(objectClass=user)(msExchHomeServerName=/o=*******/ou=*******/cn=Configuration/cn=Servers/cn=" & sServerName & ")) )))(objectCategory=user)))"
strSearchBase = "ou=Accounts,dc=*******,dc=*******,dc=*******"

oConnect.Provider = "ADsDSOObject"
oConnect.Open "DS Query"

Set command.ActiveConnection = oConnect
command.CommandText = "<LDAP://" &strSearchBase & ">;" & strADSI_Query & ";mailnickname,AdsPath,displayname,cn,mail"
command.Properties("Size Limit") = 10000
command.Properties("Page Size") = 100
Set rs = command.Execute
Do While rs.EOF = False
      WScript.Echo "Account Values: " &  rs.fields(0).value
      WScript.Echo "Account Values: " &  rs.fields(1).value
      WScript.Echo "Account Values: " &  rs.fields(2).value
      WScript.Echo "Account Values: " &  rs.fields(3).value
      WScript.Echo "Account Values: " &  rs.fields(4).value
      WScript.Echo "Account Values: " &  rs.fields(5).value            
      WScript.Echo " "
      
      Set result = ProcessAppointments(sServerName,rs.Fields(0).value)
      rs.MoveNext
Loop

Function ProcessAppointments(strServer, strMailbox)
      
      Set oSession = Nothing
      Set oSession = CreateObject ("MAPI.Session")
                oSession.Logon ,,False,,,,strServer & vbLf & strMailbox
       
       ' Get calendar
       Set objFolder = oSession.GetDefaultFolder(CdoDefaultFolderCalendar)
       Set objMessages = objFolder.Messages

       ''********************************************
       '' FILTER  (Start Date To End Date)
       Set objMessageFilter = objMessages.Filter
       Set objFields = objMessageFilter.Fields

      'TODAY
      objFields.Add CdoPR_END_DATE, CDate(CDate(Month(Now) & "/" & Day(Now) & "/" & Year(Now)))
      7 Days FROM TODAY
      objFields.Add CdoPR_START_DATE, CDate(CDate(Month(Now) & "/" & (Day(Now) + 7) & "/" & Year(Now)))
      
       ' Get appointments
       Set objAppointment = objMessages.GetFirst
   
      Do While Not objAppointment Is Nothing    
               WScript.Echo "Subject: " & objAppointment.Subject
               WScript.Echo "Location: " & objAppointment.Location
               WScript.Echo "Start: " & objAppointment.StartTime
               WScript.Echo "End: " & objAppointment.EndTime
               'WScript.Echo "Required Attendees: " & objAppointment.RequiredAttendees
            'WScript.Echo "Body: " & objAppointment.Body

               Set objAppointment = objMessages.GetNext
               WScript.Echo " "  
      Loop
End Function


Any help would be appreciated.

Regards,
James
0
Comment
Question by:jamestiger10
  • 6
  • 5
  • 2
13 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 12255317
Hi, James.

You've got a couple of incorrect property names.  There is no RequiredAttendees or Body property in the Appointment object.  Instead it's Recipients and Text, respectively.  Also, you need to test the Type property of each member of Recipients to determine of they're a required attendee or an optional one.  Required attendees have a Type of 1.  I've made the necessary changes to your code and posted below.  I tested it in my environment and it works properly.


Function ProcessAppointments(strServer, strMailbox)
     Set oSession = Nothing
     Set oSession = CreateObject("MAPI.Session")
     oSession.Logon , , False, , , , strServer & vbLf & strMailbox
     
      ' Get calendar
      Set objFolder = oSession.GetDefaultFolder(CdoDefaultFolderCalendar)
      Set objMessages = objFolder.Messages

      ''********************************************
      '' FILTER  (Start Date To End Date)
      Set objMessageFilter = objMessages.Filter
      Set objFields = objMessageFilter.Fields

     'TODAY
     objFields.Add CdoPR_END_DATE, CDate(CDate(Month(Now) & "/" & Day(Now) & "/" & Year(Now)))
     '7 Days FROM TODAY
     objFields.Add CdoPR_START_DATE, CDate(CDate(Month(Now) & "/" & (Day(Now) + 7) & "/" & Year(Now)))
     
      ' Get appointments
      Set objAppointment = objMessages.GetFirst
   
     Do While Not objAppointment Is Nothing
             Wscript.Echo "Subject: " & objAppointment.Subject
             Wscript.Echo "Location: " & objAppointment.Location
             Wscript.Echo "Start: " & objAppointment.StartTime
             Wscript.Echo "End: " & objAppointment.EndTime
             Set objRecipients = objAppointment.Recipients
             For Each objRecipient In objRecipients
                If objRecipient.Type = 1 Then
                    Wscript.Echo "Required Attendees: " & objRecipient.Name
                End If
             Next
             Wscript.Echo "Body: " & objAppointment.Text

             Set objAppointment = objMessages.GetNext
             Wscript.Echo " "
     Loop
End Function
0
 

Author Comment

by:jamestiger10
ID: 12258011
Your my boy Blue...

However, one question.  Is there any way to avoid the pop-up that ensues informing me that "A program is trying to access e-mail addresses you have stored in Outlook.  Do you want to allow this?"  I have the option of allowing access for up to 10 minutes.  This is going to run on a scheduled task, and I won't have the ability to select 'Yes' each time.  Any suggestions?

Kind Regards,
James Michalak
0
 
LVL 76

Expert Comment

by:David Lee
ID: 12263952
Hi, James.

Thanks.  What you've run into is the security Microsoft added to Outlook/CDO to combat the number of viruses using Outlook as a transport mechanism.  If you're determined to use scripting, then I don't believe there's any way to get around Microsoft's security.  Unless you want to disable the security features altogether which would mean that the system running the script would be somewhat more open to attack.  But you can only undo the security features if you're using Outlook 2000.  In Outlook 2002/2003 the features are built into the software and cannot be removed.  Or at least that's my understanding.  Here is a link (http://support.microsoft.com/?kbid=262701) to a Microsoft knowledgebase article that describes the effects of the patch on developers.  There's a link in the article to another article that talks about how it affects developers using CDO.  Simply put, sending a message or accessing an email address causes the "A program is trying to access ..." prompt.  If you can use straight VB instead of VBScript, then there's another alternative.  You can use a third-party library called Redemption (http://www.dimastr.com/redemption/).  It provides a means of accessing Outlook information without the prompts.  But it won't work with scripting, only VB.  

Hope this helps.  Let me know if there's anything else I can do.



0
 
LVL 12

Expert Comment

by:BobLamberson
ID: 12269051
Hi jamestiger10,
You might want to look at the clickyes freeware to help with the popup dialog.
http://www.contextmagic.com/express-clickyes/


Bob
0
 

Author Comment

by:jamestiger10
ID: 12275781
BlueDevilFan,

You confirmed my suspicion.  With that being the case, if I were to create a custom form and have the recipients email addresses entered into a custom property, can I access the custom property using CDO from VBScript?

James
0
 
LVL 76

Expert Comment

by:David Lee
ID: 12280816
Hi, James.

I'm not clear on what you have in mind when you mention a custom form and custom properties.  Scripts don't usually involve forms.  I've never played around with a custom form from CDO so I don't know for sure if it'll be accessible or not.  
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jamestiger10
ID: 12280935
Blue,

I created a custom form in Outlook using the AppointmentItem as a template.  I have all the properties of an Appointment, but I can also require additional information in custom property fields.  I was wondering if it is possible to access the properties within the Outlook custom form (Custom Appointment) using VBScript and CDO.  This would be an external script that's not the VBScript code used in conjunction with the Custom Form.

James
0
 
LVL 76

Expert Comment

by:David Lee
ID: 12281051
Honestly, James, I don't know.  I've never tried.  My Exchange environment doesn't use any custom forms and because it's a production environment I don't have the latitude of creating one to test and see.  I suspect you can, but that's just my guess.  I'm not sure a custom form will solve the issue of the popup though.  
0
 

Author Comment

by:jamestiger10
ID: 12283083
Only when I access the Recipient field from an Appointment am I prompted with the pop-up.  I can retrieve values from all other fields w/out any manual intervention.  I was thinking in this way I could add a custom field that didn't use the address book to resolve an email address from the display name and possibly avoid the added security pop-up.

James
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 12283122
It's certainly worth a try.  Do you by chance have access to Visual Basic?  If so, then you can use it to examine the custom appointment object and view the properties.  It's a lot easier than trying to do this from VBScript.
0
 
LVL 12

Expert Comment

by:BobLamberson
ID: 12283286
jamestiger10,
did you look at
http://www.contextmagic.com/express-clickyes/    ?

Here is a quote from their web page.......

Express ClickYes is a tiny program that runs in the system tray and automatically clicks the Yes button for the Outlook security prompt, that asks you to confirm mail sending from third party applications or access to Outlook's address book.

You can suspend or activate Express ClickYes by double-clicking its icon in the taskbar notification area (system tray). Developers can automate its behavior by sending special messages via standard Windows API functions.

If you are using a program that causes Outlook to generate the prompt, you will find this tiny tool very handy.
Click Here To Download Your Free Copy of Express ClickYes (93 KB)
 

How to Install and Launch Express ClickYes
Download the installation package, run ClickYesSetup.exe and follow the instructions (silent mode setup is available for v.1.0.7 and later).
When ClickYes is installed just double-click its icon on your Desktop to launch the program.
How to Use Express ClickYes
Express ClickYes runs in the system tray and automatically clicks the Yes button for the Outlook security prompt.
To activate or suspend program execution double-click its icon in the system tray.
Right-click the system tray icon, to get the popup menu, and select some additional options.
To exit ClickYes, right-click its system tray icon and select Exit in the popup menu.

Bob
0
 

Author Comment

by:jamestiger10
ID: 12283347
I had already put in the time to use VBScript...I was just hoping not to have to duplicate effort as my time is limited.  Thank you all for your assistance.

James
0
 
LVL 76

Expert Comment

by:David Lee
ID: 12286575
Hi, James.

I wasn't suggesting that you switch from VBScript to VB, just that you use VB to test and see if you can get the custom properties.  When I write VBScripts I almost always create them in VB first since debugging in VB is so much easier.  Then I convert them to VBScript.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now