• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

mail merge problems

I'm trying to do a mailmerge in D7 using an OleContainer. my question my be more appropriate for another group if so please advise. i've tried to different ways to mail merge with no success for the following reasons: 1.) using the creation of a data document i exceed my maximumn number of columns, is there a way around this? 2.) if i try to use the OpenDataSource to do the merge my query string exceeds 255 characters and even if i use both SQLStatement and SQLStatement1 i still exceed my limit, is there a better way? Thanks in advance
0
Michael Sterling
Asked:
Michael Sterling
  • 4
  • 4
1 Solution
 
ziolkoCommented:
see this:
http://support.microsoft.com/kb/229310

yes that's true Delphi sample on Microsoft's website!!:)

ziolko.
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
yeh i've seen that and have chopped that code and split it out and tried to use it but like i said my problem is that i need more that 4 colums (about 28 to be exact), and that exceeds that maximum width for some reason.
0
 
ziolkoCommented:
i did this:

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls;

type
  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    procedure InsertLines(LineNum : Integer);
    procedure CreateMailMergeDataFile;
    procedure FillRow(Doc : Variant; Row : Integer;
                 Text1,Text2,Text3,Text4 : String);
  public
    wrdApp, wrdDoc: Variant;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

uses ComObj;

Const wdAlignParagraphLeft = 0;
Const wdAlignParagraphCenter = 1;
Const wdAlignParagraphRight = 2;
Const wdAlignParagraphJustify = 3;
Const wdAdjustNone = 0;
Const wdGray25 = 16;
Const wdGoToLine = 3;
Const wdGoToLast = -1;
Const wdSendToNewDocument = 0;

procedure TForm1.Button1Click(Sender: TObject);
var
  StrToAdd : String;
  wrdSelection, wrdMailMerge, wrdMergeFields : Variant;
begin
// Create an instance of Word and make it visible
  wrdApp := CreateOleObject('Word.Application');
  wrdApp.Visible := True;
  // Create a new document
  wrdDoc := wrdApp.Documents.Add();
  wrdDoc.Select;

  wrdSelection := wrdApp.Selection;
  wrdMailMerge := wrdDoc.MailMerge;

  // Create MailMerge data file
  CreateMailMergeDataFile;


  // Create a string and insert it into the document
  StrToAdd := 'State University' + Chr(13) +
              'Electrical Engineering Department';
  wrdSelection.ParagraphFormat.Alignment := wdAlignParagraphCenter;
  wrdSelection.TypeText(StrToAdd);

  InsertLines(5);

  // Insert Merge Data
  wrdSelection.ParagraphFormat.Alignment := wdAlignParagraphLeft;
  wrdMergeFields := wrdMailMerge.Fields;

  wrdMergeFields.Add(wrdSelection.Range,'FirstName');
  wrdSelection.TypeText(' ');
  wrdMergeFields.Add(wrdSelection.Range,'LastName');
  wrdSelection.TypeParagraph;
  wrdMergeFields.Add(wrdSelection.Range,'Address');
  wrdSelection.TypeParagraph;
  wrdMergeFields.Add(wrdSelection.Range,'CityStateZip');
  wrdSelection.TypeParagraph;
  wrdMergeFields.Add(wrdSelection.Range,'Col5');

  InsertLines(2);

  // Right justify the line and insert a date field with
  // the current date
  wrdSelection.ParagraphFormat.Alignment := wdAlignParagraphRight;
  wrdSelection.InsertDateTime('dddd, MMMM dd, yyyy',False);

  InsertLines(2);

  // Justify the rest of the document
  wrdSelection.ParagraphFormat.Alignment := wdAlignParagraphJustify;

  wrdSelection.TypeText('Dear ');
  wrdMergeFields.Add(wrdSelection.Range,'FirstName');

  wrdSelection.TypeText(',');
  InsertLines(2);

  // Create a string and insert it into the document
  StrToAdd := 'Thank you for your recent request for next ' +
      'semester''s class schedule for the Electrical ' +
      'Engineering Department.  Enclosed with this ' +
      'letter is a booklet containing all the classes ' +
      'offered next semester at State University.  ' +
      'Several new classes will be offered in the ' +
      'Electrical Engineering Department next semester.  ' +
      'These classes are listed below.';
  wrdSelection.TypeText(StrToAdd);

  InsertLines(2);

  // Insert a new table with 9 rows and 4 columns
  wrdDoc.Tables.Add(wrdSelection.Range,9,30);
  wrdDoc.Tables.Item(1).Columns.Item(1).SetWidth(51,wdAdjustNone);
  wrdDoc.Tables.Item(1).Columns.Item(2).SetWidth(170,wdAdjustNone);
  wrdDoc.Tables.Item(1).Columns.Item(3).SetWidth(100,wdAdjustNone);
  wrdDoc.Tables.Item(1).Columns.Item(4).SetWidth(111,wdAdjustNone);
  // Set the shading on the first row to light gray

  wrdDoc.Tables.Item(1).Rows.Item(1).Cells
      .Shading.BackgroundPatternColorIndex := wdGray25;
  // BOLD the first row
  wrdDoc.Tables.Item(1).Rows.Item(1).Range.Bold := True;
  // Center the text in Cell (1,1)
  wrdDoc.Tables.Item(1).Cell(1,1).Range.Paragraphs.Alignment :=
        wdAlignParagraphCenter;

  // Fill each row of the table with data
  FillRow(wrdDoc, 1, 'Class Number', 'Class Name', 'Class Time',
     'Instructor');
  FillRow(wrdDoc, 2, 'EE220', 'Introduction to Electronics II',
     '1:00-2:00 M,W,F', 'Dr. Jensen');
  FillRow(wrdDoc, 3, 'EE230', 'Electromagnetic Field Theory I',
     '10:00-11:30 T,T', 'Dr. Crump');
  FillRow(wrdDoc, 4, 'EE300', 'Feedback Control Systems',
     '9:00-10:00 M,W,F', 'Dr. Murdy');
  FillRow(wrdDoc, 5, 'EE325', 'Advanced Digital Design',
     '9:00-10:30 T,T', 'Dr. Alley');
  FillRow(wrdDoc, 6, 'EE350', 'Advanced Communication Systems',
     '9:00-10:30 T,T', 'Dr. Taylor');
  FillRow(wrdDoc, 7, 'EE400', 'Advanced Microwave Theory',
     '1:00-2:30 T,T', 'Dr. Lee');
  FillRow(wrdDoc, 8, 'EE450', 'Plasma Theory',
     '1:00-2:00 M,W,F', 'Dr. Davis');
  FillRow(wrdDoc, 9, 'EE500', 'Principles of VLSI Design',
     '3:00-4:00 M,W,F', 'Dr. Ellison');

  // Go to the end of the document

  wrdApp.Selection.GoTo(wdGotoLine,wdGoToLast);
  InsertLines(2);

  // Create a string and insert it into the document
  StrToAdd := 'For additional information regarding the ' +
             'Department of Electrical Engineering, ' +
             'you can visit our website at ';
  wrdSelection.TypeText(StrToAdd);
  // Insert a hyperlink to the web page
  wrdSelection.Hyperlinks.Add(wrdSelection.Range,'http://www.ee.stateu.tld');
  // Create a string and insert it into the document
  StrToAdd := '.  Thank you for your interest in the classes ' +
             'offered in the Department of Electrical ' +
             'Engineering.  If you have any other questions, ' +
             'please feel free to give us a call at ' +
             '555-1212.' + Chr(13) + Chr(13) +
             'Sincerely,' + Chr(13) + Chr(13) +
             'Kathryn M. Hinsch' + Chr(13) +
             'Department of Electrical Engineering' + Chr(13);
  wrdSelection.TypeText(StrToAdd);

  // Perform mail merge
  wrdMailMerge.Destination := wdSendToNewDocument;
  wrdMailMerge.Execute(False);

  // Close the original form document
  wrdDoc.Saved := True;
  wrdDoc.Close(False);

  // Notify the user we are done.
  ShowMessage('Mail Merge Complete.');

  // Clean up temp file
  DeleteFile('C:\DataDoc.doc');
end;

procedure TForm1.CreateMailMergeDataFile;
var
  wrdDataDoc : Variant;
  iCount : Integer;
begin
  // Create a data source at C:\DataDoc.doc containing the field data
  wrdDoc.MailMerge.CreateDataSource('C:\DataDoc.doc',,,'FirstName; LastName;Address;CityStateZip;' +
                                    'col5;col6;col7;col8;col9;col10;col11;col12;col13;col14;col15;' +
                                    'col16;col17;col18;col19;col20;col21;col22;col23;col24;col25;col26;' +
                                    'col27;col28;col29;col30');
  // Open the file to insert data
  wrdDataDoc := wrdApp.Documents.Open('C:\DataDoc.doc');
  for iCount := 1 to 4 do
    wrdDataDoc.Tables.Item(1).Rows.Add;
  // Fill in the data
  FillRow(wrdDataDoc, 2, 'Steve', 'DeBroux',
        '4567 Main Street', 'Buffalo, NY  98052');
  FillRow(wrdDataDoc, 3, 'Jan', 'Miksovsky',
        '1234 5th Street', 'Charlotte, NC  98765');
  FillRow(wrdDataDoc, 4, 'Brian', 'Valentine',
        '12348 78th Street  Apt. 214', 'Lubbock, TX  25874');
  // Save and close the file
  wrdDataDoc.Save;
  wrdDataDoc.Close(False);
end;

procedure TForm1.FillRow(Doc: Variant; Row: Integer; Text1, Text2, Text3,
  Text4: String);
var cnt: Integer;
begin
  Doc.Tables.Item(1).Cell(Row,1).Range.InsertAfter(Text1);
  Doc.Tables.Item(1).Cell(Row,2).Range.InsertAfter(Text2);
  Doc.Tables.Item(1).Cell(Row,3).Range.InsertAfter(Text3);
  Doc.Tables.Item(1).Cell(Row,4).Range.InsertAfter(Text4);
  for cnt := 5 to 30 do
    Doc.Tables.Item(1).Cell(Row, cnt).Range.InsertAfter(IntToStr(cnt));
end;

procedure TForm1.InsertLines(LineNum: Integer);
var
  iCount : Integer;
begin
  for iCount := 1 to LineNum do
     wrdApp.Selection.TypeParagraph;
end;

end.

and works fine with 30 columns.  
ziolko.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ziolkoCommented:
>>and that exceeds that maximum width for some reason
yup if you change wrdDoc.Tables.Item(1).Columns.Item(1).SetWidth(51,wdAdjustNone);

to

wrdDoc.Tables.Item(1).Columns.Item(1).SetWidth(1,wdAdjustNone);

you will also get error. I tried to browse microsoft's website for some info about column widths but no luck so i think is down to finding right values by yourself.

ziolko.
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
so by your last comment it seems as though your suggesting that i have changed this line:

wrdDoc.Tables.Item(1).Columns.Item(1).SetWidth(51,wdAdjustNone);

to something else which i have not. where do i change this to make it work? the error i get is
"The requested member of the collection does not exist"
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
you are right,...i can add up to as many columns as i need how ever my problem is that i can only make that string 255 chars long:
CreateDataSource('C:\DataDoc.doc',,,'FirstName; LastName;Address;CityStateZip;' +
                                    'col5;col6;col7;col8;col9;col10;col11;col12;col13;col14;col15;' +
                                    'col16;col17;col18;col19;col20;col21;col22;col23;col24;col25;col26;' +
                                    'col27;col28;col29;col30');
also i noticed that you are using semicolons ';' that doesn't work i have to use commas ','...
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
or better yet is there a way to loop through the fields in the document?
0
 
ziolkoCommented:
>>wrdDoc.Tables.Item(1).Columns.Item(1).SetWidth(51,wdAdjustNone);
>>to something else which i have not. where do i change this to make it work? the error i get is
having this:
wrdDoc.Tables.Item(1).Columns.Item(1).SetWidth(51,wdAdjustNone);
  wrdDoc.Tables.Item(1).Columns.Item(2).SetWidth(170,wdAdjustNone);
  wrdDoc.Tables.Item(1).Columns.Item(3).SetWidth(100,wdAdjustNone);
  wrdDoc.Tables.Item(1).Columns.Item(4).SetWidth(111,wdAdjustNone);

i added on more line:
wrdDoc.Tables.Item(1).Columns.Item(4).SetWidth(100,wdAdjustNone);
and it didnt work either but when i changed:
 wrdDoc.Tables.Item(1).Columns.Item(2).SetWidth(170,wdAdjustNone);
to
 wrdDoc.Tables.Item(1).Columns.Item(2).SetWidth(70,wdAdjustNone);
it worked again!

>>also i noticed that you are using semicolons ';' that doesn't work i have to use commas ','

commas were in origial sample which didn't work for me so i changed it to semicolons

looks like thers more questions than answers:/

>>or better yet is there a way to loop through the fields in the document?

i'm afraid not using collections
wrdDoc.Tables.Item(...).Columns.Item(...)
is only way
but if it comes to datasource i think it's possible to use CSV as data source without creating it manually

ziolko.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now