BP115 : Coming Together: Advanced Integration of Lotus Notes 6 and Microsoft Office

1
BP115 Coming Together Advanced Integration
of Lotus Notes 6 and Microsoft Office

• John Head, PSC Group, LLC

2
Before we start Please turn off any cell
phones, pagers, or other disruption
devices! Thank you
3
Agenda

• Who is John Head?
• Overview
• Mail Merge
• Building Documents with Data
• Handling Templates
• Working with Attachments
• Smart Tags
• Looking forward - Office 11
• Q A

4
Positioning this session

• This session is not going to cover the basics of
  COM or OLE
• Our goal is to focus on advanced demonstrations
• For the basics of COM, OLE, and the automation of
  applications from Notes, please attend Rocky
  Olivers BP105 The Integrated Desktop Creating
  Peaceful Coexistence Between Lotus Notes and MS
  Office on Monday, 01/27 at 0400 - 0515 in Swan
  - Osprey

5
Agenda

• Who is John Head?
• Overview
• Mail Merge
• Building Documents with Data
• Handling Templates
• Working with Attachments
• Smart Tags
• Looking forward - Office 11
• Q A

6
John Head, Technical Specialist, PSC Group LLC

• http//www.psclistens.com
• Multi-Brand Premier Partner, 5 offices throughout
  Midwest
• R5 and ND6 Principal CLP - App Dev Lotus 1-2-3
  Certified
• 10 Years Exp., Notes/Domino/SmartSuite/Office/Web
• Author
• Group Computing
• Lotus Advisor
• Speaker
• Lotusphere 96, 97, 99, 00, 02, 03
• Lotusphere Europe 99
• Advisor Devcon Spring 2002
• Webmaster, Official SmartSuite Developers Website
  http//suitedev.psclistens.com

7
Agenda

• Who is John Head?
• Overview
• Mail Merge
• Building Documents with Data
• Handling Templates
• Working with Attachments
• Smart Tags
• Looking forward - Office 11
• Q A

8
Overview

• Notes is an amazing piece of collaboration
  software BUT
• Its not a word processor
• Its not a spreadsheet
• Its not a presentations engine
• Its was not designed for printed output
• Instead of limiting applications to the
  functionality within Notes, use each application
  as it was designed
• The art is in the connection and making it
  seamless and easy for the end user
• Our focus today is on using Notes 6 with Office
  XP to build applications that none of the
  applications can create on their own

9
Agenda

• Who is John Head?
• Overview
• Mail Merge
• Building Documents with Data
• Handling Templates
• Working with Attachments
• Smart Tags
• Looking forward - Office 11
• Q A

10
Mail Merge

• Mail Merge requires the following
• Data file
• Each record is on its row in a table
• First row is header row
• - Trigger Mail Merge Engine in Word via OLE
• Mail Merge in Word XP changed significantly since
  Word 2000
• Mail Merge Wizard
• Multiple Steps
• Complete Customizable via VBA
• Mail Merge Field Names are now reserved
• To work with the new Mail Merge side panel,
  fields must be mapped to the correct names
• Address Block
• Greeting Block
• Blank rows in the data file are now visible to
  Mail Merge

11
Demonstrations
Mail Merge
12
Lets Look at the Code
Mail Merge
13
The best way to learn

• Use the recorder built into the application
• Record the process
• Use keystrokes as much as possible
• The code will have extra steps then necessary

14
But my VBA code does not work!

• VBA code can not be copied and pasted from the
  Office application to Notes
• VBA constants must be translated to their actual
  value
• Msgbox constantname will return the raw value
• MS Office Constant Database
• VBA uses to designate parameters
• VBA skips parameters
• From within LotusScript, you must specify
  parameters you do not need as null ( )

15
Agenda

• Who is John Head?
• Overview
• Mail Merge
• Building Documents with Data
• Handling Templates
• Working with Attachments
• Smart Tags
• Looking forward - Office 11
• Q A

16
Building Documents with Data

• Outside of Mail Merge, pushing data from Notes to
  another application required on of the following
  options
• Notes F/X
• OLE from within Notes
• COM from within the Application
• But all of these options were run-time options
• What about the end user?
• What if they want to add a field while they are
  building the document?
• The Document Engine solves this problem

17
The Document Engine

• Uses Both OLE Automation from within Notes and
  COM from within the application
• Allows for run-time and design-time data transfer
• Works with any document or template
• As long as it was opened from within Notes
• All of the code sits in a Design Template
• Menu added to Word
• Uses Word Bookmarks
• Easily ported to Excel Ranges and PowerPoint
  fields
• Extremely user friendly

18
Demonstrations
Document Engine
19
Lets Look at the Code
Document Engine
20
Agenda

• Who is John Head?
• Overview
• Mail Merge
• Building Documents with Data
• Handling Templates
• Working with Attachments
• Smart Tags
• Looking forward - Office 11
• Q A

21
Handling Templates

• Now that we are working with required templates
  on the end users machine, we need to handle the
  installation and verification of that template
• All Office XP applications have a default
  template location
• Office XP applications must be restarted if new
  Templates have menu bars / icons bars that load
  at application start
• Use the Notes Database launch events to check to
  see if the template is installed

22
Demonstrations
Handling Templates
23
Lets Look at the Code
Handling Templates
24
Agenda

• Who is John Head?
• Overview
• Mail Merge
• Building Documents with Data
• Handling Templates
• Working with Attachments
• Smart Tags
• Looking forward - Office 11
• Q A

25
Working with Attachments

• Attachments are preferred over in-place embedded
  objects
• Smaller in Size
• Easier to Control
• Notes 6 comes overhauled the Attachment options
• View
• Open
• Edit (in place editing !!!!!!!!!!!!!)
• Save
• Save and Delete
• Delete
• Save All
• Save and Delete All
• Delete All
• But there is something missing!

26
Working with Attachments (continued)

• There is no programmatic access to the new
  features!
• So
• There are 2 methods that can be used to handle
  attachments for the end user
• The LotusScript Method that worked in R5
• A new Windows / Notes API method
• LotusScript Method
• Hides the attachment from the end user
• Forces the end user to use Custom buttons/actions
• Locks the Notes Document until the end user
  clicks Update
• One attachment per Rich Text field
• Rich Text field is computed
• Not 100 Bulletproof
• When the application is known, much easier to
  control

27
Demonstrations
Working with Templates Pure LotusScript
28
Lets Look at the Code
Working with Templates Pure LotusScript
29
Working with Attachments (continued)

• API Method
• Recreates in place editing
• Works with multiple documents per Rich Text field
• Rich Text field does not need to be computed
• Uses Execute and Shell Commands
• Not tied to any specific application
• Custom right-click menu
• Original Code taken from the LDD Sandbox

30
Demonstrations
Working with Templates API
31
Lets Look at the Code
Working with Templates API
32
Agenda

• Who is John Head?
• Overview
• Mail Merge
• Building Documents with Data
• Handling Templates
• Working with Attachments
• Smart Tags
• Looking forward - Office 11
• Q A

33
Smart Tags

• What are they?
• Make data more meaningful and actionable
• API to develop and manage SmartTags
• Applications
• Office XP (10)
• Word
• Excel
• Outlook with Word as Editor
• Internet Explorer (limited functionality)
• Office 11
• PowerPoint
• Access
• But why dont they work with Notes data today?

34
SmartTags (continued)

• SmartTags do not work with local data sources
• SmartTags do not work with COM connections that
  authenticate
• Good News!
• Office 11 fixes both of these issues
• Plans for a detailed article and sample on Notes
  and SmartTag integration with Office 11 planed
  for this summer

35
SmartTags in Office 11
36
Other options?

• Visual Studio.Net
• VBA macros can be converted to
• Visual Basic.Net
• C
• COM Add-ins
• Managed
• Requires use of shims

37
Agenda

• Who is John Head?
• Overview
• Mail Merge
• Building Documents with Data
• Handling Templates
• Working with Attachments
• Smart Tags
• Looking forward - Office 11
• Q A

38
Looking Forward - Office 11

• Expanded XML support in Word and Excel
• Object Model Programmability
• XMLNameSpace, XMLNode, and other objects
• XMLNamespaces, XMLNodes, and other collections
• Ability to use Customer-Defined XML schemas
• New option in the File New dialog
• XML Structure task pane
• In Word, add XML data to an existing document for
  use with document fields via the Insert Field
  dialog
• Smart Documents
• Customized Task Panes
• Uses XML
• UI looks like the Notes 6 Quick Notes Pane in the
  Welcome Page

39
Office 11 (continued)

• Visual Studio Tools for Office
• Adds the ability to create Office solutions with
  VB.NET, Visual C.NET and the Microsoft .NET
  Framework Class Libraries

40
Visual Studio Tools for Office
41
Office 11 (continued)

• XDocs
• UI for users to work with XML-based documents
• But why do I care?
• All of these features will increase the
  integration level between Notes and Office
• Better applications, allowing the end user to use
  the best tool for working with the data, while
  allowing you to control the data and its storage

42
Agenda

• Who is John Head?
• Overview
• Mail Merge
• Building Documents with Data
• Handling Templates
• Working with Attachments
• Other Demonstrations
• Smart Tags
• Looking forward - Office 11
• Q A

43
For more information

• To see Office automation in a real world customer
  case studyIts all about the content -
  Knowledge Management Put to Use College Bowl
  Company,Inc.
• come to CS112 tomorrow, Thursday 830 AM
• Swan Mockingbird

44
Q A
Please fill out and hand in your evaluations
They make a difference in planning for next
year! The beginnings of the Document Engine came
from an evaluation suggestion from last year
45
Thank You! For more information This
presentation and all of the samples will be made
available on http//jhead.dominodeveloper.net by
Friday, February 7th, 2003 Contact John Head
jhead_at_psclistens.com