Hi all,
I'm running into a conundrum ... I am now sending out about 1,000 letters a week from our company, and I want to insert a JPG of a staff member's signature in our mail merge process.
What is the best way to merge a graphic stored in Sesame? Or should I use a file path location stored in the staff profile?
Here's my current code:
// WordMerge 2.44 November 2008
// Written for use with MS Word(tm) Word Processor
// The following 3 variables are user-modifiable ..
var vDataPath as String = "Docs\MergeData.txt" // Merge data source file for Word (output file)
// THIS FILE GETS CREATED AUTOMATICALLY
// can optionally be a full path such as
// "C:\Sesame2\Docs\MergeData.txt"
var vDocType as string = ".DOCx;.DOC" // document types (file extensions)
// For Office 2007, added DOCx to document types
var vStartPath as string = "docs" // folder to start in, relative to Sesame's Start In folder,
// normally c:\sesame2
// Can optionally be a full path such as "C:\Docs\MergeDocs"
// ************** Variables for Program (Do Not Modify) ******************
var vFileHandle as Int // For writing merge data source file for Word
var vHeader as String // Data source file header line
var vData as String // Data source file data line
var n as Int // General purpose counter var
var vSelectedDoc as String // The selected Word doc
var vOneOrAll as String // Include record(s) option
var vNames as string // Form LE names of LEs with editable data (not static text, etc)
var vStartingRec as INT // Current record number
var vProceed as INT // Used to determine whether to run Word
var vVal as string // Used to hold One data element
var vName as string // name of an LE on the form for merge
var nn as Int // general purpose counter
var vLEs as String // All LE's on current form
// BW Addition 2011-12-20
// BW Addition 2012-01-25 -- Added StaffExt as phone field
var gFileID as int
var vHeaderField as String
var vNewFileID as int
var vNote as string
var vLttr as String
var vLttrDesc as String
var vLttrInfo as String
var vCount as int
var vNewNoteNo as int
var vNewNote as string
var vLog as string
var vStaffLEs as String = "StaffName;StaffTitle;StaffEmail;StaffExt;StaffSignature;Staff800"
var vClientLEs as String = "CltName;CltTel1;CltAdd1;CltAdd2;CltCity;CltProv;CltPCod"
var vLttrDate1 as string
var vLttrDate2 as date
// The following are used in the GetDoc() subroutine that selects the doc to merge with
var vPath as String // Currrent docs path (changes as user navigates)
var vFileTypes as String // File types to find, such as .doc and .pdf
var vExtensions as String // Permitted file extensions
var vList as String // List of folders & files in vPath
var vFolderFlag as Int // Tells program that user selected a subfolder
var vFolders as String // Subfolders in selected folder
var vDocs as String // Available doc files of all desired types in selected folder
var vItem as String // Item selected by user
// vPath and vItem are assigned to vSelectedDoc for the actual Merge
// ******************************************************************
SUBROUTINE CheckData()
// This next line gets rid of embedded carriage returns in the data fields. Replaces with two spaces.
// This is necessary otherwise the merge will fail
vData = @Replace(vData, @Newline(), " ")
// This next line gets rid of double quotes in the data fields. Puts in 2 x single quotes.
// This is necessary otherwise the merge will fail ('cannot open source document')
vData = @Replace(vData, @Chr(34), @Chr(39) + @Chr(39) )
// This next line checks for an umlauted Y in data fields, resulting from mal-formed yes/no values from Q&A. Replaces with zero-length string.
vData = @Replace(vData, @Chr(255), "" )
END SUBROUTINE
// ******************************************************************
SUBROUTINE MergeFields() // to take all elements on the form, and generate a list of those that can contain data - not statics, not Command buttons, etc
var n as Int
var nn as Int
vLEs = @StringArrayElementList() // All LE's on current form
For n = 1 to @CountStringArray(vLEs)
vName = @AccessStringArray(vLEs, n)
SetThisElement(vName)
nn = @ElementType(ThisElement)
If nn > 999 and nn < 1009 // if in this range, element contains editable data
vNames = @AppendStringArray(vNames, vName) // add to the list of LEs to be exported for the Mailmerge
UnSetThisElement()
Next
END SUBROUTINE
// ******************************************************************
Subroutine GET_DOC()
var vTemp as String
var n as Int
vList = @LocalListDirectory(vPath)
// Isolate subfolders (items that have no "." in them)
vFolders = @SearchStringArray(vList, "/..\...")
vTemp = ""
// Add boldface and "+" sign to folder names
For n = 1 to @CountStringArray(vFolders)
vTemp = @AppendStringArray(vTemp, "@b" + "+" + @AccessStringArray(vFolders, n))
Next
vFolders = vTemp
vFileTypes = @Replace(vExtensions,".","...")
// Isolate document type filenames
vDocs = @SearchStringArray(vList, vFileTypes)
vDocs = @SortStringArray(vDocs, 0)
PopupSelectPosition(4, @XPos(ThisElement), @YPos(ThisElement))
If vFolderFlag = 1 // User selected a subfolder
{
vItem = @PopupChoiceList("@b[BACK]" + ";" + vFolders + ";" + vDocs,
"SELECT FOLDER or DOCUMENT")
vFolderFlag = 0
}
Else
{
vItem = @PopupChoiceList(vFolders + ";" + vDocs, "SELECT FOLDER or DOCUMENT")
}
If vItem = "@b[BACK]"
{
SetStringArraySeparator("\")
vPath = @TrimStringRight(@ReplaceStringArray(vPath, "",
@CountStringArray(vPath)), "\")
RestoreStringArraySeparator()
If @Instr(vPath, "\") > 0 And vPath <> vStartPath Then vFolderFlag = 1
GET_DOC()
}
Else If vItem <> ""
{
If @FindStringArray(vExtensions, @Right(vItem, 4)) = -1
{
vFolderFlag = 1
vPath = vPath + "\" + @Replace(vItem, "@b+", "")
GET_DOC()
}
// user selected a document file
}
END SUBROUTINE
// ******************************************************************
// BW Added Functions 2011-12-20
// BW Added Functions 2012-01-25 - Added allowance for StaffExt, extra ^
// BW Added Functions 2014-10-09 - Added allowance for StaffSignature, Staff800, extra ^^
Function AddStaffInfo(vDBStaffID as String) as String
var vVals1 as String
var vReturn as String
vVals1 = @XLookupSourceList(@FN, vDBStaffID, "Staff Screen!StaffRef", vStaffLEs)
If vVals1 <> ""
{
vReturn = vReturn + @AccessStringArray(vVals1, 1) + "^"
+ @AccessStringArray(vVals1, 2) + "^"
+ @AccessStringArray(vVals1, 3) + "^"
+ @AccessStringArray(vVals1, 4) + "^"
+ @AccessStringArray(vVals1, 5) + "^"
+ @AccessStringArray(vVals1, 6) + "^"
}
Else
{
vReturn = vReturn + "^^^^^^"
}
Return(vReturn)
End Function
// ******************************************************************
Function AddClientInfo(vDBClientID as String) as String
var vVals2 as String
var vReturn as String
vVals2 = @XLookupSourceList(@FN, vDBClientID, "Sales Screen!CltNo", vClientLEs)
If vVals2 <> ""
{
vReturn = vReturn + @AccessStringArray(vVals2, 1) + "^"
+ @AccessStringArray(vVals2, 2) + "^"
+ @AccessStringArray(vVals2, 3) + "^"
+ @AccessStringArray(vVals2, 4) + "^"
+ @AccessStringArray(vVals2, 5) + "^"
+ @AccessStringArray(vVals2, 6) + "^"
+ @AccessStringArray(vVals2, 7) + "^"
}
Else
{
vReturn = vReturn + "^^^^^^^"
}
Return(vReturn)
End Function
// ******************************************************************
SUBROUTINE PrintCurrentRecordOnly()
var n as Int
Var i as Int
For n = 1 to @CountStringArray(vNames)
vHeader = vHeader + @AccessStringArray(vNames,n) + "^"
vVal = @(@AccessStringArray(vNames,n)) // the one piece of data from an element
// Convert if money, number, date, time, bool, image
vName = @AccessStringArray(vNames, n)
If vVal <> ""
{
SetThisElement(vName)
vVal = @AsFormattedByLE(ThisElement, 0, vVal)
}
vData = vData + vVal + "^"
UnSetThisElement()
If vName = "DBColl#" Then
{
For i = 1 to @CountStringArray(vStaffLEs)
vHeader += @AccessStringArray(vStaffLEs, i) + "^"
Next
vData = vData + AddStaffInfo(@(vName))
}
If vName = "DBCltNo" Then
{
For i = 1 to @CountStringArray(vClientLEs)
vHeader += @AccessStringArray(vClientLEs, i) + "^"
Next
vData = vData + AddClientInfo(@(vName))
}
Next
CheckData() // Run subroutine to remove any of 3 non-permitted characters
vFileHandle = FileOpen(vDataPath)
IF vFileHandle <0 // IF FILE CANNOT BE OPENED
THEN
{
@MsgBox( " FILE CANNOT BE OPENED."," THIS IS PROBABLY BECAUSE A MERGE DOCUMENT ATTACHED TO IT IS ALREADY OPENED."," CLOSE THE MERGE DOCUMENT(S) AND TRY AGAIN.")
}
ELSE
{
FileSeek(vFileHandle, 0)
FileWriteLn(vFileHandle, vHeader + @NewLine() + vData)
FileClose(vFileHandle)
vProceed = 1
}
// ==============================
// BW SINGLE NOTE LOG ATTEMPT #2
// ==============================
nn = @FormNewRecord("DRNotes")
vLttrDesc = vSelectedDoc
vNote = "Sent " + vLttrDesc + ": $" + DBBal + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod
vLog = @left((@Serverdate() + " " + vLttrDesc + ": $" + DBBal + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod),80)
vNote = @replace(vNote,".doc","")
vNote = @replace(vNote,"docs\","")
vNote = @replace(vNote,"DB\","")
vLog = @replace(vLog,".doc","")
vLog = @replace(vLog,"docs\","")
vLog = @replace(vLog,"DB\","")
gFileID = @ToNumber(@GlobalValue("gFileID")) +1
vNewFileID = gFileID
GlobalValue("gFileID", vNewFileID)
FormFieldValue("DRNotes", "DRNoteID", nn, vNewFileID)
FormFieldValue("DRNotes", "DRNoteRef", nn, DBID)
FormFieldValue("DRNotes", "DRNoteDate", nn, @ServerDate())
FormFieldValue("DRNotes", "DRNoteTime", nn, @ServerTime())
FormFieldValue("DRNotes", "DRNoteSig", nn, @UserID)
FormFieldValue("DRNotes", "DRNote", nn, vNote)
FormFieldValue("DRNotes", "DRNoteClt", nn, DBCltNo)
FormCommit("DRNotes")
DBWorked = @serverdate()
DBLttrLog0 = vLog + @NewLine() + DBLttrLog0
END SUBROUTINE
// ******************************************************************
SUBROUTINE PrintAllRetrievedRecords()
var n as Int
var i as Int
var vLetterDate2 as date
vStartingRec = @ResultSetCurrentPosition()
IF @Askuser("Merge Print " + vSelectedDoc + " for these " + @ResultSetTotal() + " retrieved records?",
"( If not, click on NO to cancel this task, press F7 for a new search,"," and retrieve the records you DO want to print. )")
THEN
{
vLetterDate2 = @PromptForUserInput("Date Lettered Files Until?", @date+7)
For n = 1 to @CountStringArray(vNames)
vHeaderField = @AccessStringArray(vNames,n)
vHeader = vHeader+ @AccessStringArray(vNames,n) + "^"
// ***
// BW Header Attempt #2
if vHeaderField = "DBColl#"
{
For i = 1 to @CountStringArray(vStaffLEs)
vHeader += @AccessStringArray(vStaffLEs, i) + "^"
Next
}
If vHeaderField = "DBCltNo" Then
{
For i = 1 to @CountStringArray(vClientLEs)
vHeader += @AccessStringArray(vClientLEs, i) + "^"
Next
}
Next
vFileHandle = fileOpen(vDataPath)
IF vFileHandle <0 // IF FILE CANNOT BE OPENED
THEN
{
@MsgBox( " FILE CANNOT BE OPENED."," THIS IS PROBABLY BECAUSE A MERGE DOCUMENT ATTACHED TO IT IS ALREADY OPENED."," CLOSE THE MERGE DOCUMENT(S) AND TRY AGAIN.")
}
ELSE
{
FileSeek(vFileHandle, 0)
FileWriteLn(vFileHandle, vHeader)
For n = 1 to @ResultSetTotal()
ResultSetCurrentPosition(n)
For i = 1 to @CountStringArray(vNames)
// Convert if money, number, date, time, bool, image
vName = @AccessStringArray(vNames, i)
vVal = @(@AccessStringArray(vNames,i))
If vVal <> ""
{
SetThisElement(vName)
vVal = @AsFormattedByLE(ThisElement, 0, vVal)
}
UnSetThisElement()
vData = vData + vVal + "^"
// **
// BW Data Merge Attempt #3
If vName = "DBColl#" Then
{
vData = vData + AddStaffInfo(@(vName))
}
If vName = "DBCltNo" Then
{
vData = vData + AddClientInfo(@(vName))
}
CheckData() // Run subroutine to remove any of 3 non-permitted characters
Next
// ============================
// BW MASS NOTE LOG ATTEMPT #2
// ============================
nn = @FormNewRecord("DRNotes")
vLttrDesc = vSelectedDoc
vNote = "Sent " + vLttrDesc + ": $" + DBBal + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod
vLog = @left((@Serverdate() + " " + vLttrDesc + ": $" + DBBal + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod),80)
vNote = @replace(vNote,".doc","")
vNote = @replace(vNote,"docs\","")
vNote = @replace(vNote,"DB\","")
vLog = @replace(vLog,".doc","")
vLog = @replace(vLog,"docs\","")
vLog = @replace(vLog,"DB\","")
gFileID = @ToNumber(@GlobalValue("gFileID")) +1
vNewFileID = gFileID
GlobalValue("gFileID", vNewFileID)
FormFieldValue("DRNotes", "DRNoteID", nn, vNewFileID)
FormFieldValue("DRNotes", "DRNoteRef", nn, DBID)
FormFieldValue("DRNotes", "DRNoteDate", nn, @ServerDate())
FormFieldValue("DRNotes", "DRNoteTime", nn, @ServerTime())
FormFieldValue("DRNotes", "DRNoteSig", nn, @UserID)
FormFieldValue("DRNotes", "DRNote", nn, vNote)
FormFieldValue("DRNotes", "DRNoteClt", nn, DBCltNo)
FormCommit("DRNotes")
DBWorked = @Serverdate()
If vLetterDate2 <> "" then DBNxt = vLetterDate2
DBLttrLog0 = vLog + @NewLine() + DBLttrLog0
FileWriteLn(vFileHandle, vData)
@Msg("Processed Record " + n + " of " + @ResultSetTotal())
vData = ""
Next
FileClose(vFileHandle)
vProceed = 1
// Rewind to record where it all started
ResultSetCurrentPosition(vStartingRec)
} // END CONDITIONAL FILE CANNOT BE OPENED
} // END CONDITIONAL DO YOU WANT TO PROCEED WITH THIS MERGE
END SUBROUTINE
// - - - - - - - E N D O F V A R I B L E D E C L A R A T I O N S - - - - - - - - - - - -
// BEGIN MAIN PROGRAM
FormCommit("")
// Jan 2008 - generate the list of LEs for export for merge via MergeFields() subroutine
MergeFields() // generates a stringarray list of all editable LEs on the form - Export all editable data for merge
// Use GetDoc subroutine for user to select the document
vExtensions = vDocType // set the extensions the GetDoc() subroutine looks for
vPath = vStartPath
GET_DOC()
// GetDoc ends up with vItem - make that the selected doc
vSelectedDoc = vPath + "\" + vItem
// Set position of picklist to nearly over the command button
PopupSelectPosition(4,@Xpos(ThisElement)+10,@Ypos(ThisElement))
IF @RT(vSelectedDoc, 1) <> "\" and vSelectedDoc <> "" Then
{
// Build which record(s) menu
vOneOrAll = @PopupMenu("THIS Record Only;ALL Retrieved Records", "SELECT RECORD(S) TO MERGE PRINT")
// ==================================================================
// Prevents Users from printing batch letters -- 2005/10/31 BW
// Updated to Use Staff Profile Checkbox StAccMLttr -- 2013/09/24 BW
// ==================================================================
if vOneOrAll = "All Retrieved Records" and @Xlookupsourcelist(@FN, @userid, "Staff Screen!StaffRef", "StAccMLttr") <> 1
{
@msgbox("You do not have access to batch letter printing!","","")
vOneOrAll = ""
}
// Reset Popup position
PopupSelectPosition(0,99,99)
IF vSelectedDoc <> "" and vOneOrAll <> "" Then
{
// Delete existing data file. New one will be generated.
IF FileExists(vSelectedDoc) Then
{
FileDelete(vDataPath)
IF vOneOrAll = "THIS Record Only" Then
PrintCurrentRecordOnly()
Else
PrintAllRetrievedRecords()
IF vProceed = 1 THEN
{
n = @ASynchShell(vselectedDoc)
}
} // END CONDITIONAL SELECTED DOCUMENT EXISTS
ELSE
@Msgbox(vSelectedDoc + " doesn't exist.","","Aw shucks!")
} // END CONDITIONAL ONE OR ALL HAS BEEN SELECTED
} // END CONDITIONAL A DOCUMENT HAS BEEN SELECTED
Pages: 1
Mail Merging Signature JPGs (Read 1109 times)
