How to automate MSWord's mail merge using php

It’s two days now I’m trying to find an effective solution to do a mail merge using php (receiver’s details will come from mysql and the message body will come from a textarea).

I know this can be done manually, I can get a csv file for all the contacts and then someone creates the mail merge manually using word, but what I was interested in, was to automate this using php.

User will fill up the textarea and on submit, he will get the word documents, with one word page for each recipient.

The normal methods are not working since I have thousands of receivers here.

Any help will be appreciated.
Thanks.

I’ve never worked with MS Office in PHP before, as it’s something more suited to ASP.NET.

Are you firm that it needs to be a Word document? PHP could create a PDF or a HTML email instead, which would be easier.

Well, the word files will later be posted to different recipients. The advantage of using word is that the sender can do minor modifications to individual’s letters before finally printing this.

Actually I posted in another thread to get a good method to do the job, where many solutions (cron based, etc) came up. CUPS gave me the idea of using a mail merge, but he spoke about manual mail merge. After googling, I found that it’s possible to automate the task, but yet to get a solution here.

:frowning:

I understand your target to be to create a simple form which, when saved, goes on to create a csv file which can then be downloaded and used as the source of a mail-merge.

Am I defining your problem correctly?



<input type=radio id=users name=users value=all>All users
<textarea>this is the message</textarea>



if ( $_POST['users'] === "all" ){

select all users from your database

}

foreach user in database write a line in a csv file


so you end up with a csv file like this:

“name” , “address”, “message”
“Joe Bloggs”, “1 big road” , “this is the message”
“John Doe”, “1 small road” , “this is the message”

OR

do you mean you simply write “this is the message” in a word template and squirt into it this type of csv file:

“name” , “address”
“Joe Bloggs”, “1 big road”
“John Doe”, “1 small road”

What are the “minor modifications” you talk of, are they dependent upon things in the database?

Are the columns in the CSV file always in the same order?

If so, they can save it as a .rtf file through Word (rather than .doc) and type ‘{colname}’ for each column (i.e. {companyname} for the companyname column) and then use the following code:

<?php
$columns = array('id', 'companyname', 'etc'); //replace with column names, in the same order as the CSV file.
$rep = array();
foreach($columns as $key => $column){
    $rep[$key] = '{' . $column . '}';
}
$contents = file_get_contents('yourfile.rtf');
$csvhandle = fopen('csvfile.csv', 'r');
$csv = fgetcsv($csvhandle);
foreach($csv as $row){
    $tmpcontents = str_replace($rep, $columns, $contents);
    //do what you want with $tmpcontents...
}

Cups, actually what I need is:

  1. User fills up a form (containing a text field, a text area and a few hidden fields containing information on the person).
  2. Once the user hits submit, the following things should take place:
  1. Catch a csv file from a subfolder (containing recipients list) (I don’t need to create the csv file each time probably, but will replace an existing csv file each time a new member is added, or an old member updates his information, that will save download time, isn’t it?)[/LIST]
    [LIST=]Catch a word template from the same subfolder) (challenge: how to make the word template)[/LIST]
    [LIST=
    ]From these two, the csv file and word template, the php code generates the mail-merged file, with one word page for each recipient.[/LIST]

This will be the better way to achieve this goal, isn’t it?

Hmmm … thanks Arkinstall … will try this … hope a 1000 word page won’t take infinite time to download … :slight_smile:

So you have the csv file already.

That file is the mail-merge source.

Now the secretary working connected to a printer creates the word template and uses the mail-merge source she downloads from your site, when done she must throw that csv file away, because it may be out of date next time s/he need to do another mailshot.

I’m repeating what I said in another thread, so I am going to stop now, in the hope that someone else can help you.

Actually cups, I was trying to let my php do what the secretary in your story is supposed to do.

arkinstall, your code is working, but how to rewrite the word template file with new value taken from the csv file?


	foreach($csv as $row){
		$tmpcontents = str_replace($rep, $columns, $contents);
		fputs($csvhandle, $tmpcontents);
	}

gives the template file as it is …

This might come in handy then [fphp]lick_stamps/fphp :smiley:

My bad :slight_smile:

<?php
$columns = array('id', 'companyname', 'etc'); //replace with column names, in the same order as the CSV file.
$contents = file_get_contents('yourfile.rtf');
$csvhandle = fopen('csvfile.csv', 'r');
$csv = fgetcsv($csvhandle);
foreach($csv as $row){
	foreach($columns as $column => $name){
		$tmpcontents = str_replace('{'.$name.'}', $row[$column], $contents);
	}
	//do what you want with $tmpcontents...
}

Well, the whole code stands now as:

$columns = array('firstname', 'lastname', 'address', 'zip', 'city');
	$contents = file_get_contents('rtf/template.rtf');
	
	$handle = fopen("rtf/maquette.csv", "r");
	$file = '/path/to/file/rtf/final.rtf';
	if (file_exists($file)) { unlink($file); }
	$word = fopen("rtf/final.rtf", "w");
	while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
		foreach($columns as $column => $name) {
			$tmpcontents = str_replace('{'.$name.'}', $data[$column], $contents);
			fwrite($word, $tmpcontents);
		}
	}
	
	fclose($word);
	fclose($handle);
		
	header('Content-type: application/octet-stream');
	header('Content-Disposition: attachment; filename="final.rtf"');
	readfile('rtf/final.rtf');

Result, I get a word file that looks similar to the template file (including the place holders) with a difference that this one is much bigger in size. 15000 kb in stead of 5 kb of the template.

Try this:

<?php
$columns = array('firstname', 'lastname', 'address', 'zip', 'city');
$contents = file_get_contents('rtf/template.rtf');
$handle = fopen("rtf/maquette.csv", "r");
$file = '/path/to/file/rtf/final.rtf';
if (file_exists($file)) { unlink($file); }
$word = fopen("rtf/final.rtf", "w");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
	foreach($columns as $column => $name) {
		$tmpcontents = str_replace('{'.$name.'}', $data[$column], $contents);
	}
	fwrite($word, $tmpcontents);
}
fclose($word);
fclose($handle);
header('Content-type: application/octet-stream');
header('Content-Disposition: attachment; filename="final.rtf"');
readfile('rtf/final.rtf');

Nope … for some reason this is not working my friend. All what I’m getting is a copy of the original csv file (strangely with a much larger size).

One way to get this work is to use substr_replace in lieu of str_replace, I’m getting a word file here, contains only the city name (last variable), without any page set up information, (means the rtf codes are all gone, and all information are clustered together).

The following gives the closest result. Means, all place holders are rightly replaced, but they are placed right one after another. No page set up. So for 500 rows of csv, I’m getting everything in 8 pages, in stead of 500 pages.


	while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
		foreach($columns as $column => $name) {
			$tmpcontents = substr_replace('{'.$name.'}', $data[$column], $contents);
			fwrite($word, $tmpcontents);
		}
	}

As I’ve found, the str_replace does not replace the place holders. substr_replace does, but at the same time it takes away all the rtf codes.

Don’t fwrite() inside the foreach loop, just the while loop.

Hmmm … if I fwrite outside the foreach loop (inside the while loop), I only get the last placeholder’s information, city in this example.

I think a bigger problem here is the substr_replace vs str_replace. For some reason, str_replace is not replacing anything. substr_replace is replacing the placeholders, but also everything else …

preg_replace works same as str_replace, replaces nothing …

So after much hit and try, it seems that I’m almost there.

Two cases are there in front of me, with their own problems:

	while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
		$tmpcontents = $contents;
		foreach($columns as $column => $name) {
			$tmpcontents = str_replace('\\{'.$name.'\\}', $data[$column], $tmpcontents);
		}
		fwrite($word, $tmpcontents);
	}

This one just shows the first page. Seeing the size of the uploaded file, it seems that I’m having the full file, just that I can’t see them. All what I see is the first page.

	while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
		$tmpcontents = $contents;
		foreach($columns as $column => $name) {
			$tmpcontents = str_replace('\\{'.$name.'\\}', $data[$column], $tmpcontents);
		}
fwrite($word, ' ');		
fwrite($word, $tmpcontents);
	}

If I add anything before writing the file, say a space, I get all the pages. However, then I don’t see the rtf file as it should be, but row rtf codes only.

:shifty::shifty: so what next … :shifty::shifty: