Trouble with Mailmerge.Execute using variables

Aspose.Words Product Family
1

I’m converting a vb.net webforms project from an old proprietary mailMerge api to Aspose.Words. (The old api was simply passed a CSV file with one row of merge field names and a second row of values.) Since Aspose.Words.MailMerge.Execute can’t accept a CSV file, I’m trying to define two string variables with the same data (merge field names and corresponding values. The problem seems to be that I have sets of double quotes that surround the fields and values, separated by commas, but nothing is merged into the merge fields in the document.

I have tested the Execute by hardcoding merge field names and data and that does in fact work. So I’m just doing something wrong in my passed values or something.

Here’s my code and partial values that I’m passing:

letter.MailMerge.Execute(New String() {
csvline0
},
New Object() {
csvline1})

partial csvline0: “”“full_discovery_date”","“pretrial_motions_date”","“govt_responses_due”","“dft_reply_date”", etc
partial csvline1:
“”“January 25, 2022"”,"“January 25, 2022"”,"“January 25, 2022"”, etc.

Thank you,
Ward

2

@wrowell1 Could you please attach your template and sample CSV data? We will check the issue and provide you more information.

3

I sure will. It’ll be just a few minutes, then I can send them.

image001.gif (7.68 KB)

4

Here you go Alexey. Thank you for your help.

And here is some of my code. I kind of have a couple of different things going on here that I’ll clean up once I get the Aspose merge working, but I’m generating the CSV file as well as defining the strings/variables that are used in the mailmerge.execute.

Try

'header

Dim i As Integer = 1

For i = 0 To ds.Tables(0).Rows.Count - 1

csvLine = String.Format("{0}{1},", csvLine, ds.Tables(0).Rows(i).Item(0))

csvline0 = String.Format("{0}{1},", csvline0, SetCSV(ds.Tables(0).Rows(i).Item(0)))

Next

csvLine = String.Format("{0}{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12}", csvLine, _division, _plaintiff, _defendant1, _defendant1_pc, _defendant2, _casenumber, _casenumber2, _casenumber3, _casenumber4, _defendants, _SectionSymbol, _defendantnum, _dftresp_last_name)

csvline0 = String.Format("{0}{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12}", csvline0, SetCSV(_division), SetCSV(_plaintiff), SetCSV(_defendant1), SetCSV(_defendant1_pc), SetCSV(_defendant2), SetCSV(_casenumber), SetCSV(_casenumber2), SetCSV(_casenumber3), SetCSV(_casenumber4), SetCSV(_defendants), SetCSV(_SectionSymbol), SetCSV(_defendantnum), SetCSV(_dftresp_last_name))

w.WriteLine(csvLine)

csvline0 = Replace(csvline0, Chr(34) + Chr(34), Chr(34))

'detail

csvLine = String.Empty

For i = 0 To ds.Tables(0).Rows.Count - 1

csvLine = String.Format("{0}{1},", csvLine, SetCSV(ds.Tables(0).Rows(i).Item(1)))

Next

csvLine = String.Format("{0}{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12}", csvLine, SetCSV(division), SetCSV(plaintiff), SetCSV(defendant1), SetCSV(defendant1_pc), SetCSV(defendant2), SetCSV(casenumber.ToUpper), SetCSV(casenumber2.ToUpper), SetCSV(casenumber3.ToUpper), SetCSV(casenumber4), SetCSV(defendants), SetCSV(sectionSymbol), SetCSV(defendantNum), SetCSV(defendant_last_name).ToUpper)

csvline1 = csvLine

w.WriteLine(csvLine)

w.Flush()

w.Close()

'Set the template path

templatePath = String.Format("{0}\{1}", templatePath, templateFilename)

'Get the target path

Dim targetPath = String.Format("{0}\final.docx", userDirectory)

Dim targetPDF As String = String.Format("{0}\final.pdf", userDirectory)

Dim letter As New Document(templatePath)

letter.MailMerge.Execute(New String() {

csvline0

},

New Object() {

csvline1})

letter.Save(targetPath)

Public Function SetCSV(field As String) As String

SetCSV = Chr(34) & field & Chr(34)

End Function

And, finally, here are the two full strings that contain the merge field names and the corresponding values that I’m trying to send to the merge template.

"""full_discovery_date"",""pretrial_motions_date"",""govt_responses_due"",""dft_reply_date"",""pretrial_materials_date"",""pretrial_conference_date"",""trial_date_time"",""order_date"",""division"",""plaintiff"",""defendant1"",""defendant1_pc"",""defendant2"",""casenumber"",""casenumber2"",""casenumber3"",""casenumber4"",""defendants"",""SS"",""num"""

"""January 25, 2022"" ,""January 25, 2022"" ,""January 25, 2022"",""January 25, 2022"",""January 25, 2022"" ,""January 25, 2022 at 1:00 AM"",""January 25, 2022 at 9:00 AM. "",""January 25, 2022"",""DALLAS DIVISION"",""UNITED STATES OF AMERICA"","" John Doe "",""John Doe "","" John Doe "",""3:21-CR-00070-B"",""3:21-CR-70"",""3:21-CR-00070-B-1"",""3:02-CR-070-01-B"",""Defendant"",""§"",""(1)"""

image001.gif (7.68 KB)

CRPTO.docx (44.4 KB)

(Attachment mergeBNFDMXUNSO.csv is missing)

5

Alexey, the csv file was rejected in the email, so I renamed it to .txt.

image001.gif (7.68 KB)

(Attachment mergeBNFDMXUNSO.txt is missing)

6

Well, I’ll try a 3rd time. Ha! Changed to .docx.

image001.gif (7.68 KB)

mergeBNFDMXUNSO.docx (658 Bytes)

7

@wrowell1 Thank you for additional information. The problem occurs because mergefields in your template are defined improperly. Here is an example:
{ MERGEFIELD "pretrial_motions_date \\@ MMMM d, yyyy" \* MERGEFORMAT }
Date format is defined in the double quotes with field name. That is why Aspose.Words recognizes whole text in the double quotes as field name. The proper field code of the mergefield must be the following:
{ MERGEFIELD pretrial_motions_date \@ "MMMM d, yyyy" \* MERGEFORMAT }
In this case pretrial_motions_date is read as field name by Aspose.Words and mail merge will be executed properly.

8

Thank you Alexey. I should’ve mentioned that I realized some of the merge fields are improperly named at this point and I’ll need to address that, but as I understand it, in the merge, those would simply be ignored and the ones that are properly named would be merged (like division, plaintiff, casenumber). Not even the matching, correct merge fields are being merged.

image001.gif (7.68 KB)

9

@wrowell1 I have check using the following simple code and the correctly named merge fields are merged properly:

    Dim rows() As String = File.ReadAllLines("C:\Temp\mergeBNFDMXUNSO.csv")

    ' The first line contains names of fields.
    Dim names() As String = rows(0).Split(",")
    ' The second line contains values of fields enclosed into double quotes.
    ' So use "," as delimiter. Also trim unnecessary double quotes
    Dim dlimiter() As String = {""","""}
    Dim values() As String = rows(1).Split(dlimiter, StringSplitOptions.None).Select(Function(s) s.Trim("""")).ToArray()

    ' Open template and execute mail merge.
    Dim doc As Document = New Document("C:\Temp\CRPTO.docx")
    doc.MailMerge.Execute(names, values)
    doc.Save("C:\Temp\out.docx")
10

Thanks very much! I’ll give this all a try. It sure looks good.

11

Alexey,

Thank you SO much for your efficient, correct and fast help with my problem. You were a great help! If you need any review or anything, I’m here for you. The detail in the sample code that you sent was above and beyond. Thank you so much.

Ward

12

@wrowell1 Thank you for appreciating our work. Please feel free to ask in case of any issues, we are always glad to help you.