354 Lotus blogs updated hourly. Who will post next? Home | Downloads | Events | Pods | Blogs | Search | myPL | About 
 
Latest 7 Posts
Code snippet – jQuery
Sat, Mar 15th 2014 317
World Wide Web turning 25 years this week
Fri, Mar 14th 2014 151
Connect 2014 – Day 0 (Saturday)
Mon, Jan 27th 2014 255
Connect 2014 – Chilly evenings in Orlando
Fri, Jan 24th 2014 287
Connect 2014 – More about the sessions
Wed, Jan 22nd 2014 316
Connect 2014 – 3 days left until I leave for Orlando
Wed, Jan 22nd 2014 298
Connect 2014 – My preliminary schedule
Wed, Jan 15th 2014 149
Top 10
Pool party or no pool party, that is the question – or is it?
Mon, Jan 13th 2014 636
Export Notes view to Excel – with multi-value fields
Fri, Apr 5th 2013 485
Code snippet – jQuery
Sat, Mar 15th 2014 317
Connect 2014 – More about the sessions
Wed, Jan 22nd 2014 316
Free Tool: Analyze ACL in Notes Application/Database
Tue, Nov 15th 2011 300
Create and update Calendar reminders from Notes document
Thu, Aug 11th 2011 299
Connect 2014 – 3 days left until I leave for Orlando
Wed, Jan 22nd 2014 298
Connect 2014 – Chilly evenings in Orlando
Fri, Jan 24th 2014 287
Review: Samsung Galaxy S3
Sun, Jun 24th 2012 257
Connect 2014 – Day 0 (Saturday)
Mon, Jan 27th 2014 255


[Code] - Mail Merge/Form Letters in Lotuscript
Karl-Henry Martinsson    

Back in 2003 or so, I wrote some code to take a form letter (stored in a Notes document) and merge that with data stored in another Notes document in order to create a personalized letter that could be printed or emailed. Back then we were still on Notes 5, so very limited rich text functionality and no budget to purchase Ben's excellent Midas LSX. The end result worked, but any formatting in the form letter template was lost.

Eventually we upgraded to Notes 7 and letar to Notes 8.5. Now I had much more rich text functionality to play with, so I rewrote the code as a class. I added some additional functionality, like formatting values using a mask, and some lookup functionality. The class support all kind of formatting in the form letter template, including fonts, colors, tables, graphics, etc.

This is what a typical form letter look like:

Form Letter Template

As you can see, the placeholders are using curly brackets to hold either a field name or a command. The commands are indicated by the percent sign (%). There can also be different arguments, for formatting, lookup into the NAB/Domino Directory, etc. I even have functionality to present a nice dialog box where the user can pick recipient from a list of everyone associated with the claim (as this is from a claim system used by an insurance company).

Here is a description of the syntax for the placeholders:

{fieldname}
Displays the content of the specified field from the selected source document. Additional (optional) formatting arguments can be used, for example to format values to desired format.
By using the argument LOSSNOTICE or SOURCE="LOSSNOTICE" the value of the field is retrieved from the Loss Notice instead of the current source document, e.g. {adjuster LOSSNOTICE}.
Use the argument SELECTED to get the fields name, address, city, state, zip, email, SSN and DoB for the recipient selected in the separate dialog box.
{email SELECTED} will return the email address for the recipient selected, either producer, insured or one of the claimants.
The optional argument NABFIELD will retrieve the value of the specified field from the NAB for the user specified in the field (field must be spelled exactly as in the NAB design):
{Adjuster NABFIELD="JobTitle"} will return the title of the person in the 'Adjuster' field.

{%DATE}
Displayes the current date. Default format is mm/dd/yyyy but the FORMAT argument can be used to change the value into desired format. The old {%DUS}, {%DUT}, etc have been removed and must be replaced with the new format, as they don't work.
Examples of date formats:
FORMAT="mmmm d, yyyy" -> March 3, 2010
FORMAT="yyyy-mm-dd" -> 2010-03-01
FORMAT="mmmm yyyy" -> March 2010

{%TIME}
Displayes the current time. Default format is hh:nn:ss (24h universal format) but like with the date, the FORMAT argument can be used to change the value into desired format. Note that minutes use the letter N, not M (which is used for month)! The old {%TUS}, {%TUT}, etc have been removed and must be replaced with the new format, as they don't work.
Examples of time formats:
FORMAT="h.nn ampm" -> 2.34 pm
FORMAT="hh.nn ampm" -> 02.34 pm
FORMAT="hh:nn" -> 14:34

{%INPUT PROMPT="Please enter the amount" FORMAT="$#,##0.00" REQUIRED SETVAR="variablename"}
Asks the user to enter a value. The optional arguemnt REQUIRED is used to force the user to enter a value (blank values will not be accepted). FORMAT can be used to format the value entered, for example into correct currency format (as shown above) or desired date format.
Additionally, SETVAR="variablename" can be used to store the value entered for re-use later in the document, where {%GETVAR NAME="variablename"} is used to retrieve/display it.

{%GETVAR NAME="variablename"}
Get the value previously stored in an {%INPUT} command using the SETVAR argument.

{%USER} or {%USR}
Displayes the current user's name. It will be the name of the user creating the form letter. {%USR} have been deprecated, it is just available for backwards compatibility. The optional argument NABFIELD will retrieve the value of the specified field from the NAB for the current user (field must be spelled exactly as in the NAB design):
{%USER NABFIELD="JobTitle"}

{%PICKLIST SERVER="servername" DB="database" VIEW="viewname" FIELD="fieldname" PROMPT="text}
This command let the user select a document from a list, and returns the value in the specified field. SERVER and DB are optional, and defaults to current server and current database. VIEW, FIELD and PROMPT are required. Optionally CLEARCACHE can be used for multiple lookups to the same view where different documents are to be selected.
By using the FILTER argument, the document collection will be filtered to only those where the category (in a categorized view) matches the value in the specified field in the source document. E.g: VIEW="(ClaimantList)" FILTER="ParentUNID" will only show documents in the (categorized) lookup view with the same ParentUNID as the document being processed (merged).
By specifying the optional keyword/argument CLAIMANT, the VIEW, PROMPT and FILTER argumenst are set automatically as follows: VIEW="(SysLookupClaimantsCatByParentUNID)" PROMPT="Select Claimant:" FILTER="ParentUNID".

{%PICKLIST VIEW="viewname" FIELD="fieldname" CACHED}
This command is used after the initial picklist command. It will retrieve additional fields from the same (cached) document, so the user only have to select the document once but can retrieve multiple field values from it. If another {%PICKLIST} command is encountered with the CLEARCACHE argument, the cached document will not be available anymore.

And now the code, from a script library called Class.MailMerge.
Right now I don't have the time to move the code out of my application and build a working sample database (the form letters are actually stored in a separate database), but I hope that this code can still help someone. 

	Option Public
Option Declare

' *** Script Library for MailMerge, preserving formatting in Rich Text field
' *** Requires Notes 6.5 or higher (tested in 7.0)
' *** Written by Karl-Henry Martinsson, Deep South

Const TYPE_FIELD = 1
Const TYPE_CMD = 2
Dim picklist List As NotesDocument 
Dim variable List As String

Class PlaceHolderData
    Public placeholderstring As String
    Public placeholdertype As Integer
    Public fieldname As String
    Public fieldtype As Long
    Public Command As String
    Public argument List As String
    Public Text As String 
    
    Public Sub New(ByVal placeholder As String)
        ' Store the original placeholder
        placeholderstring = placeholder        
        ' Strip out curly brackets before and after
        placeholder = Mid$(placeholder,2,Len(placeholder)-2)
        If Left$(placeholder,1) = "%" Then    ' Check if it is a command
            Me.placeholdertype = TYPE_CMD
            ' Remove the % in front of the command
            placeholder = Right$(placeholder,Len(placeholder)-1)    
        Else
            Me.placeholdertype = TYPE_FIELD
        End If
        Call ParsePlaceHolder(placeholder)
    End Sub
    
    Private Sub ParsePlaceHolder(ByVal placeholder As String)
        Dim startpos As Integer 
        Dim midpos As Integer 
        Dim endpos As Integer 
        Dim args As Integer                            ' Boolean to indicate argements present or not
        Dim argstring As String
        Dim qt As Integer 
        Dim eq As Integer
        Dim char As String
        Dim argname As String
        Dim argvalue As String
        Dim i As Integer
        
        ' First we need to find the end of the command or field.
        ' It is either at the end of the placeholder or when we encounter a space
        endpos = InStr(placeholder," ")        ' Search for space
        If endpos = 0 Then                    ' No space, e.g. no arguments
            endpos = Len(placeholder)                
            args = False
        Else                                ' We have some arguments
            endpos = endpos -1                ' Reduce by one to get rid of trailing space
            args = True
        End If
        If Me.placeholdertype = TYPE_CMD Then
            Me.command = UCase(Left$(placeholder,endpos))
        Else
            Me.fieldname = Left$(placeholder,endpos)            
        End If
        If args = True Then 
            ' Get arguments
            argstring = FullTrim(Right$(placeholder,Len(placeholder)-endpos)    )    ' Get arguments only
            qt = False
            eq = False
            For i = 1 To Len(argstring)
                char = Mid$(argstring,i,1)    ' Get character
                If eq = True Then
                    argvalue=argvalue & char
                Else
                    argname=argname & char
                End If
                
                If char = |"| Then                    ' We found a quote
                    If qt = False Then
                        qt = True                        
                    Else
                        qt = False
                    End If
                ElseIf char="=" Then             ' Found a equal, e.g. now we are getting to a value
                    If eq = False Then
                        eq = True                        
                    Else
                        eq = False
                    End If
                End If
                If i = Len(argstring) Then        ' We are at the end
                    char = " "                        ' Fake a space
                End If
                If char = " " Then                    ' Found a space
                    If qt = False Then            ' Make sure it is not within quotes
                        eq = False                    ' Now we are back at argument name again
                        If Right$(argname,1) = "=" Then                    
                            argname = Left$(argname,Len(argname)-1)    ' Remove trainling equal sign
                        End If
                        Me.argument(UCase(argname)) = FullTrim(Replace(argvalue,|"|,""))    ' Create list item, remove quotes
                        argname = ""
                        argvalue = ""
                    End If
                End If
            Next
        End If
    End Sub
    
    Public Sub ProcessPlaceHolder(sourcedoc As NotesDocument, lossnotice As NotesDocument, selected List As String)
        Dim session As New NotesSession                
        Dim thisdb As NotesDatabase
        Dim ws As New NotesUIWorkspace
        Dim pickcollection As NotesDocumentCollection
        Dim pickdoc As NotesDocument
        Dim servername As String
        Dim dbname As String
        Dim viewname As String        
        Dim fieldvalue As String
        Dim formatstring As String
        Dim inputstr As String
        Dim prompt As String
        Dim title As String
        Dim default As String
        Dim filterfield As String
        Dim filtervalue As String
        Dim nabdoc As NotesDocument
        
        On Error GoTo errCase
        ' Read any formatting specified in arguments
        If IsElement(Me.argument("FORMAT")) Then
            formatstring = Me.argument("FORMAT")
        ElseIf IsElement(Me.argument("FMT")) Then
            formatstring = Me.argument("FMT")
        Else 
            formatstring = ""
        End If
        
        If placeholdertype = TYPE_FIELD Then
            If sourcedoc Is Nothing Then
                MsgBox "Error: sourcedoc not defined, unable to retrieve data from field '" & _
                Me.fieldname & "'.",,"MailMerge::PlaceHolder.ProcessPlaceHolder()"
                fieldvalue = "*** ERROR ***"                
                Exit Sub
            Else
                If IsElement(Me.Argument("LOSSNOTICE")) Then
                    fieldvalue = lossnotice.GetItemValue(Me.fieldname)(0)
                    Me.FieldType = lossnotice.GetFirstItem(Me.fieldname).Type
                ElseIf IsElement(Me.Argument("SOURCE")) Then
                    If UCase(Me.Argument("SOURCE")) = "LOSSNOTICE" Then
                        fieldvalue = lossnotice.GetItemValue(Me.fieldname)(0)
                        Me.FieldType = lossnotice.GetFirstItem(Me.fieldname).Type
                    End If
                ElseIf IsElement(Me.Argument("SELECTED")) Then
                    me.FieldName = StrConv(me.fieldname,3)    ' ProperCase
                '    MsgBox selected("SendTo_" & Me.fieldname),,"Debug"
                    If IsElement(selected("SendTo_" & Me.fieldname)) = True Then
                        fieldvalue = selected("SendTo_" & Me.fieldname)
                    Else 
                        fieldvalue = "n/a"
                    End If
                    Me.FieldType = 1280        ' Force Text
                ElseIf IsElement(Me.Argument("NABFIELD")) Then
                    fieldvalue = GetNABField(sourcedoc.GetItemValue(Me.fieldname)(0), Me.Argument("NABFIELD"))
                Else
                    fieldvalue = sourcedoc.GetItemValue(Me.fieldname)(0)
                    Me.FieldType = sourcedoc.GetFirstItem(Me.fieldname).Type
                End If
                If formatstring <> "" Then
                    If IsDate(fieldvalue) Then           ' Check if it might be a date/time value
                        fieldvalue = Format$(CDat(fieldvalue),formatstring)
                    ElseIf IsNumeric(fieldvalue) Then    ' Check if it might be a numeric value
                        fieldvalue = Format$(CDbl(fieldvalue),formatstring)
                    End If
                End If
            End If
            Me.text = fieldvalue
        Else
        '    MsgBox Me.Command,,"Command"
            ' *** Fix legacy commands
            If UCase(Me.Command) = "USR" Then    
                Me.Command = "USER"
            End If
            ' *** Process placeholder commands
            Select Case UCase(Me.Command)
            Case "USER" :
                If IsElement(Me.Argument("NABFIELD")) Then
                    Me.Text = GetNABField(session.CommonUserName, Me.Argument("NABFIELD")) 
                Else
                    Me.Text = session.CommonUserName 
                End If
            Case "INPUT"        :        ' *** Ask the user to enter information
                prompt = Me.Argument("PROMPT")
                If IsElement(Me.Argument("TITLE")) Then
                    title = Me.Argument("TITLE")
                Else
                    title = "FormLetter Mail Merge"
                End If
                If IsElement(Me.Argument("DEFAULT")) Then
                    default = Me.Argument("DEFAULT")
                Else
                    default = ""
                End If
                If IsElement(Me.Argument("REQUIRED")) Then
                    ' Repeat until user enter a value
                    Do                
                        inputstr = InputBox$(prompt, title, default)                        
                    Loop While FullTrim(inputstr)=""
                Else
                    inputstr = InputBox$(prompt, title, default)                        
                End If
                If formatstring <> "" Then
                    If IsDate(inputstr) Then                    ' Check if it might be a date/time value
                        inputstr = Format$(CDat(inputstr),formatstring)
                    ElseIf IsNumeric(inputstr) Then    ' Check if it might be a numeric value
                        inputstr = Format$(CDbl(inputstr),formatstring)
                    End If
                End If
                Me.Text = inputstr
                ' *** Check for SETVAR argument
                If IsElement(Me.argument("SETVAR")) Then
                    variable(UCase(Me.argument("SETVAR")))=Me.Text
                End If
                
            Case "PICKLIST"    :        ' Present the user with a list of documents to choose from
                Set thisdb = session.CurrentDatabase
                Set pickdoc = Nothing        ' Clear pickdoc
                ' *** We need to get the view argument to perform a lookup into the list...
                If IsElement(Me.Argument("VIEW")) Then
                    viewname = UCase(Me.Argument("VIEW"))
                End If
                ' *** If CLAIMANT argument is specified, set arguments to predefined values
                If IsElement(Me.Argument("CLAIMANT")) Then
                    Me.Argument("VIEW") = "(SysLookupClaimantsCatByParentUNID)"
                    Me.Argument("FILTER") = "ParentUNID"
                    Me.Argument("PROMPT") = "Select Claimant:"
                End If
                ' *** Check if user requested to clear cached data
                If IsElement(Me.Argument("CLEARCACHE")) Then
                    If IsElement(picklist(viewname)) Then
                        Erase picklist(viewname)    ' Delete this cached item (document)
                    End If
                End If
                ' *** If user want to use cached data, load pickdoc with cached data
                If IsElement(Me.Argument("CACHED")) Then
                    If IsElement(picklist(viewname)) Then
                        Set pickdoc = picklist(viewname)
                    End If
                End If
                If pickdoc Is Nothing Then    ' No cached document for this view 
                    If IsElement(Me.Argument("SERVER")) Then
                        servername = Me.Argument("SERVER")
                    Else
                        servername = thisdb.Server
                    End If
                    If IsElement(Me.Argument("DB")) Then
                        dbname = Me.Argument("DB")
                    Else
                        dbname = thisdb.FilePath 
                    End If
                    If IsElement(Me.Argument("VIEW")) Then
                        viewname = Me.Argument("VIEW")    
                    Else
                        MsgBox "Missing Required Argument - VIEW" & Chr$(13) & Me.PlaceHolderString,,"Missing Argument"
                        Exit Sub
                    End If
                    If IsElement(Me.Argument("PROMPT")) Then
                        prompt = Me.Argument("PROMPT")
                    Else
                        MsgBox "Missing Required Argument - PROMPT" & Chr$(13) & Me.PlaceHolderString,,"Missing Argument"
                        Exit Sub
                    End If
                    If IsElement(Me.Argument("TITLE")) Then
                        title = Me.Argument("TITLE")
                    Else
                        title = "FormLetter Mail Merge"
                    End If
onemoretime:                
                    If IsElement(Me.Argument("FILTER")) Then
                        filterfield = Me.Argument("FILTER")                         ' Get field to filter on
                        filtervalue = sourcedoc.GetItemValue(filterfield)(0)        ' Get value of field on source document
                        Set pickcollection = ws.PicklistCollection(3, False, servername, dbname, viewname, title, prompt, filtervalue)
                    Else
                        Set pickcollection = ws.PicklistCollection(3, False, servername, dbname, viewname, title, prompt)
                    End If
                    If IsEmpty(pickcollection) Then
                        If IsElement(Me.Argument("REQUIRED")) Then
                            If UCase(Me.Argument("REQUIRED")) <> "NO" Then
                                MsgBox "You need to select one item/document in the list.", , title
                                GoTo onemoretime
                            End If
                        End If
                    Else
                        Set pickdoc = pickcollection.GetFirstDocument        
                    End If
                    If pickdoc Is Nothing Then
                        MsgBox "Error: No document returned.",,"MailMerge::PlaceHolder.ProcessPlaceHolder()"
                        Exit Sub
                    End If
                    Set picklist(UCase(viewname)) = pickdoc
                End If
                If IsElement(Me.Argument("FIELD")) Then
                    fieldname = Me.Argument("FIELD")
                Else
                    MsgBox "Missing Required Argument - FIELD" & Chr$(13) & Me.PlaceHolderString,,"Missing Argument"                        
                    Exit Sub
                End If
                inputstr = pickdoc.GetItemValue(fieldname)(0)
                If formatstring <> "" Then
                    If IsDate(inputstr) Then                    ' Check if it might be a date/time value
                        inputstr = Format$(CDat(inputstr),formatstring)
                    ElseIf IsNumeric(inputstr) Then    ' Check if it might be a numeric value
                        inputstr = Format$(CDbl(inputstr),formatstring)
                    End If
                End If
                Me.Text = inputstr
                ' *** Check for SETVAR argument
                If IsElement(Me.argument("SETVAR")) Then
                    variable(UCase(Me.argument("SETVAR")))=Me.Text
                End If
                
            Case "DATE"            :         ' *** Current Date
                If formatstring = "" Then 
                    Me.Text = Format$(Now(),"mm/dd/yyyy")
                Else
                    Me.Text = Format$(Now(),formatstring)
                End If
                
            Case "TIME"            :         ' *** Current Time
                If formatstring = "" Then 
                    Me.Text = Format$(Now(),"hh:nn:ss")
                Else
                    Me.Text = Format$(Now(),formatstring)
                End If
                
            Case "GETVAR"    :    ' *** Get variable previously stored
                If IsElement(variable(UCase(Me.argument("NAME")))) Then
                    Me.Text = variable(UCase(Me.argument("NAME")))
                End If
            
            Case Else                :
                Me.Text = "**** undefined command ***"
                
            End Select
        End If
exitSub:
        Exit Sub
errCase:        
        If placeholdertype = TYPE_FIELD Then
            Me.Text = "**** unknown field '" & me.FieldName & "' ***"
            MsgBox "Unknown field '" & me.FieldName & "'.",,"Class.MailMerge:ProcessPlaceholder()"
        Else
            Me.Text = "**** unknown command '" & UCase(me.Command) & "' ***"
            MsgBox "Unknown command '" & UCase(me.Command) & "'.",,"Class.MailMerge:ProcessPlaceholder()"
        End If
        Resume exitSub
    End Sub
    
    ' *** Private supporting functions/subs
    
    Private Function GetNABField(user As String, fieldname As String) As String
        Dim session As New NotesSession
        Dim curdb As NotesDatabase    
        Dim nabdb As NotesDatabase
        Dim view As NotesView
        Dim col As NotesDocumentCollection
        Dim userdoc As NotesDocument
        
        Set curdb = session.CurrentDatabase
        Set nabdb = New NotesDatabase(curdb.Server, "names.nsf")
        Set view = nabdb.GetView("PeopleByFirstname")
        Set col = view.GetAllDocumentsByKey(user)
        If col Is Nothing Then
            GetNABField = ""
            Exit Function
        End If
        Set userdoc = col.GetFirstDocument
        If userdoc Is Nothing Then
            GetNABField = ""
            Exit Function
        End If
        GetNABField = userdoc.GetItemValue(fieldname)(0)
    End Function
    
    
    
End Class


Class MailMergeObject
    Public templatedoc As NotesDocument    ' Where to get layout from
    Public sourcefield As NotesRichTextItem    
    Public targetfield As NotesRichTextItem    ' Where to put the merged text
    Public placeholder List As PlaceHolderData
    Public selected List As String 
    Private sourcedoc As NotesDocument     ' The document containing data to be merged
    Private lossnotice As NotesDocument        ' The loss notice for the processed document
    
    Public Sub New()
        
    End Sub
    
    Public Sub SetSourceDoc(doc As NotesDocument)
        Dim session As New NotesSession
        Dim db As NotesDatabase
        Dim view As NotesView 
        Set sourcedoc = doc                
        Set db = session.CurrentDatabase 
        Set view = db.GetView("(SysLookupLossNoticeByParentUNID)")
        Set lossnotice = view.GetDocumentByKey(doc.ParentUNID(0),True)
    End Sub
    
    Public Function LoadTemplate(doc As NotesDocument) As Integer
        Dim body As NotesRichTextItem                 ' Temporary copy of body field for this function
        Dim temp As String            
        Dim bodytext As String
        Dim startpos As Long
        Dim endpos As Long
        Set sourcefield = doc.GetFirstItem("Body")    ' Put template body field (rich text) into global object
        Set body = sourcefield                        ' Put rich text into temporary body object
        bodytext = body.GetUnformattedText()
        startpos = InStr(bodytext,"{")
        Do While startpos > 0
            endpos = InStr(startpos,bodytext,"}")
            If endpos>0 Then
                temp = Mid$(bodytext,startpos,endpos-startpos+1)
                Set placeholder(temp & "~" & startpos) = New PlaceHolderData(temp)    ' Add to list of placeholder objects        
            End If
            startpos = InStr(endpos,bodytext,"{")
        Loop
    End Function
    
    Public Function MergedRichText() As NotesRichTextItem
        Dim body As NotesRichTextItem        
        Dim range As NotesRichTextRange         
        Dim cnt As Integer
        Set body = sourcefield
        Set range = body.CreateRange 
        ForAll p In placeholder
            Call p.ProcessPlaceHolder(sourcedoc, lossnotice, me.selected)
            If p.text = "" Then
                p.text = " -- "
            End If
            cnt =  range.FindAndReplace(p.placeholderstring, p.text, 1+4+8+16)            
        End ForAll
        Call body.Compact 
        Call body.Update 
        Set targetfield = body
        Set MergedRichText = body
    End Function    
    
    Public Function Content() As NotesRichTextItem
        Set Content = targetfield
    End Function
    
End Class
This LotusScript was converted to HTML using the ls2html routine, provided by Julian Robichaux at nsftools.com.

 

Here is an example of how the functions in teh script library can be called. I have stripped some code out of of this example, in order to make it easier to see the actual functionality. There are a number of additional functions (logging the letters, stopping after creation so the user can edit the resulting letter, force a copy of the letter to be saved in the database, etc.

You can see the code to select recipients. The form letter template document also has a number of additional fields that I haven't mentioned, where the creator of the template can decide if a particular recipient (e.g. the insured or the agency that wrote the policy) should be forced in, or if the user should be able to pick the recipient from all persons/companies associated with teh claim, or just the claimants (people having part of the claim, which excludes the agency but include anyone else involved in teh accident).

Use "Class.MailMerge"

	Dim ws As New NotesUIWorkspace 
	Dim uidoc As NotesUIDocument
	Dim session As New NotesSession
	Dim formdb As NotesDatabase 
	Dim lossnotice As NotesDocument
	Dim templatedoc As NotesDocument
	Dim sourcedoc As NotesDocument
	Dim newdoc As NotesDocument 
	Dim body As NotesRichTextItem
	Dim mmbody As NotesRichTextItem 
	Dim mm As MailMergeObject
	Dim templatename As String
	Dim templateunid As String
	Dim parentunid As String
	Dim picklist As NotesDocumentCollection	
	Dim nabdoc As NotesDocument
	
	Set formdb = New NotesDatabase(claimdb.Server,"ClaimSystemFormLetters.nsf")
	Set picklist = ws.PicklistCollection(3,False,formdb.Server, formdb.FilePath,"LookupForm", "Select Form Letter","Please select document to process.") 	
	If picklist Is Nothing Then 
		Exit Sub
	Elseif picklist.Count <> 1 Then
		Exit Sub
	End If
	Set templatedoc = picklist.GetFirstDocument()
	templatename = templatedoc.Title(0)
	templateunid = templatedoc.UniversalID
	Set nabdoc = GetNABDoc(GetUserName()) 		' Get person document for creator (current user)
	Set claimview = claimdb.GetView("(SysLookupLossNoticeByClaimNumber)")
	Set sourcedoc = ws.CurrentDocument.Document
	Set lossnotice = ws.CurrentDocument.Document
	parentunid = lossnotice.GetItemValue("ParentUNID")(0)
	Set mm = New MailMergeObject
	Call mm.LoadTemplate(templatedoc)
	Call mm.SetSourceDoc(sourcedoc)
	Set newdoc = New NotesDocument(claimdb)
	If Instr(templatedoc.GetItemValue("Prompt")(0),"Yes") > 0 Then
		flag = ws.DialogBox("DialogSelectRecipient", True, True, False, False, False, Fales, "Select Recipient", newdoc, True, False, True)
		mm.Selected("SendTo_Name") = newdoc.GetItemValue("SendTo_Name")(0)
		mm.Selected("SendTo_Address") = newdoc.GetItemValue("SendTo_Address")(0)
		mm.Selected("SendTo_Address2") = newdoc.GetItemValue("SendTo_Address2")(0)
		mm.Selected("SendTo_City") = newdoc.GetItemValue("SendTo_City")(0)
		mm.Selected("SendTo_State") = newdoc.GetItemValue("SendTo_State")(0)
		mm.Selected("SendTo_Zip") = newdoc.GetItemValue("SendTo_ZIP")(0)
		mm.Selected("SendTo_Email") =  newdoc.GetItemValue("SendTo_Email")(0)
		mm.Selected("SendTo") =  newdoc.GetItemValue("SendTo_Email")(0)
		mm.Selected("SendTo_Dob") =  newdoc.GetItemValue("SendTo_DOB")(0)
		mm.Selected("SendTo_Ssn") =  newdoc.GetItemValue("SendTo_SSN")(0)
	Elseif templatedoc.GetItemValue("ForceRecipient")(0)="Yes" Then		
		recipient = templatedoc.GetItemValue("Recipient")(0)
		mm.Selected("SendTo_Name") = sourcedoc.GetItemValue(recipient & "_Name")(0)
		mm.Selected("SendTo_Address") = sourcedoc.GetItemValue(recipient & "_Address")(0)
		mm.Selected("SendTo_Address2") = sourcedoc.GetItemValue(recipient & "_Address2")(0)
		mm.Selected("SendTo_City") = sourcedoc.GetItemValue(recipient & "_City")(0)
		mm.Selected("SendTo_State") = sourcedoc.GetItemValue(recipient & "_State")(0)
		mm.Selected("SendTo_Zip") = sourcedoc.GetItemValue(recipient & "_ZIP")(0)
		mm.Selected("SendTo_Email") =  sourcedoc.GetItemValue(recipient & "_Email")(0)
		mm.Selected("SendTo") =  sourcedoc.GetItemValue(recipient & "_Email")(0)
		mm.Selected("SendTo_Dob") =  sourcedoc.GetItemValue(recipient & "_DOB")(0)
		mm.Selected("SendTo_Ssn") =  sourcedoc.GetItemValue(recipient & "_SSN")(0)
		Forall sto In mm.Selected
			Call newdoc.ReplaceItemValue(Listtag(sto), sto)
		End Forall
	End If
	Set body = New NotesRichTextItem(newdoc,"Body")
	Set mmbody = mm.MergedRichText()
	Call body.AppendRTItem(mmbody)
	Call newdoc.Save(True,True)	
This LotusScript was converted to HTML using the ls2html routine, provided by Julian Robichaux at nsftools.com.

Hope this can help someone!



---------------------
http://www.bleedyellow.com/blogs/texasswede/entry/mailmergelotusscript
May 04, 2012
60 hits



Recent Blog Posts
317


Code snippet – jQuery
Sat, Mar 15th 2014 11:43a   Karl-Henry Martinsson
This morning I was working on a web application, and I came up with a pretty neat and simple little solution. So I just wanted to share it, in case anyone else need something similar. I have a webpage with an HTML form. Each input tag has an attribute called notesfield, matching the name of the field in Notes where the value is stored: First Name Initial Last Name Then I created a simple function that will call an agent on the Domino server, which will return all th [read] Keywords: agent domino notes ajax application database server
151


World Wide Web turning 25 years this week
Fri, Mar 14th 2014 3:02p   Karl-Henry Martinsson
In some articles it is claimed that Internet turns 25 years old this week, which of course is not true. But the World Wide Web is. It was on March 12, 1989 that Tim Berners-Lee wrote a proposal for a hypertext system that would become what we call “the web”. In 1991 the first webpage was created and published at CERN. Since then the web has exploded. I first got in touch with web pages and HTML in 1994, and in 1995 I had my own little server running on my work computer. I don’t [read] Keywords: lotus notes ajax development email google instant messaging javascript network server wiki wireless
255


Connect 2014 – Day 0 (Saturday)
Mon, Jan 27th 2014 6:03a   Karl-Henry Martinsson
After not hearing the alarm this morning and therefore missing my original flight from DFW to MCO (Orlando International), I got booked on the next flight. I was in the air just a couple of hours late, and arrived at Dolphin in time for BALD at Big River on the Boardwalk. Had a great time connecting with friends after a year of not seeing many of them. Of course I got a hug from Mat Newman, meaning that IBM Connect started for real. Låter I was going to hit ESPN, but due to changes there, [read] Keywords: ibm
287


Connect 2014 – Chilly evenings in Orlando
Fri, Jan 24th 2014 9:42a   Karl-Henry Martinsson
Countdown to Connect 2014 It looks like the evenings next week week will be a little bit chilly. So it might be a good idea to bring something warm to wear for the Tuesday party at Disney’s Hollywood Studios. Hope to see you in Orlando! [read] Keywords: collaboration ibm
316


Connect 2014 – More about the sessions
Wed, Jan 22nd 2014 12:02p   Karl-Henry Martinsson
Countdown to Connect 2014 The sessions at Lotusphere/Connect are focusing more and more on XPages each year. I don’t think there are really any sessions about classic Notes development this year. Currently I am not able to use XPages at work, due to the client environment we run (Lotus Notes 8.5.2 Basic in Citrix), but the sessions are still great (see video below) and I am learning new things every year. Hopefully we will soon switch the client to IBM Notes 9.0 so I can use all th [read] Keywords: admin domino ibm lotus lotusphere notes xpages citrix development network office
298


Connect 2014 – 3 days left until I leave for Orlando
Wed, Jan 22nd 2014 9:42a   Karl-Henry Martinsson
Wednesday morning. In exactly 3 days I will arrive in Orlando, after a short flight from Dallas. My preparations for the trip are almost done, I just have to charge up some batteries for cameras, dig out some spare USB cables and purchase a few last-minute items. So what is it that is so exciting about Lotusphere (now Connect)? How do you explain it to someone who never been there? In the past, several members of the community have brought their significant other to Orlando, at least for a few [read] Keywords: domino lotus lotusphere notes community exchange exchange office wiki
149


Connect 2014 – My preliminary schedule
Wed, Jan 15th 2014 8:08a   Karl-Henry Martinsson
I have now created my preliminary schedule for Connect 2014. There are two only sessions that collide (on Tuesday), and I have not been able to find any repeats for them. That is how often has been at Lotusphere, but I would say that in the later years, the collisions have been fewer and fewer, thanks to more repeats and (perhaps) smarter scheduling. Monday afternoon is when I have set time aside to visit the showcase and the labs. I also have a little bit of time after lunch Wednesday. As [read] Keywords: ibm lotusphere




636


Pool party or no pool party, that is the question – or is it?
Mon, Jan 13th 2014 10:11a   Karl-Henry Martinsson
With the name change to Connect in 2013, the welcome reception/pool party changed somewhat. Instead of taking place at the pools/beach area between Swan and Dolphin, it started inside in the showcase area (Atlantic hall), and then moved outside for food, drinks and entertainment. From what it looks like on the Connect 2014 website, and based on Chris Miller’s blog post, it sounds like the traditional poolside party is no more. The Connect website also only mention snacks and beverages. [read] Keywords: atlantic ibm lotusphere
129


Connect 2014 – Survive the week!
Mon, Jan 6th 2014 7:02a   Karl-Henry Martinsson
Countdown to Connect 2014 In exactly 3 weeks, thousands of us will be sitting in the Northern Hemisphere ballroom at the Dolphin hotel in Orlando. We have already been involved in some social activities on Saturday with soccer, BALD and ESPN, followed by a full day of jumpstart and master class sessions on Sunday. Sunday evening there was the big welcome reception on the beach between Swan and Dolphin. Some of us may have continued to Kimonos afterwards, or perhaps to Jelly Rolls dueling [read] Keywords: collaboration ibm lotusphere apple office
190


Connect 2014 – Plan your schedule!
Fri, Jan 3rd 2014 7:22p   Karl-Henry Martinsson
Countdown to Connect 2014 We already talked about how to prepare for Connect by getting everything together, like batteries, chargers, comfortable shoes and questions for the IBM developers. But you also should plan ahead for the sessions. You will spend hours listening to experts from IBM as well as from business partners from all around the world. There are always several sessions at the same time, sometimes making it hard to choose. There are also the longer Show n’ Tell sessions, s [read] Keywords: collaboration ibm lotusphere notes application archive database development mobile planetlotus planetlotus.org




Created and Maintained by Yancy Lent - About - Blog Submission - Suggestions - Change Log - Blog Widget - Advertising - Mobile Edition