E-mail only records with e-mail adresses, snail mail the rest

[jco]

I have a database where some names (records) have e-mail adressese and others
don't (they only have snail mail adresses). Using mail merge I would like to
make a procedure that sends the letter as an e-mail to those who have the
e-mail adress registered, while the rest are sent to the printer for posting.
The procedure should be as automatic as possible, preferrably only one click
to do both.

Any suggestions to how this could best be solved?

 

[Peter Jamieson]

You will need a macro that does two merges. Each merge will need to be able
to select the appropriate records - that may or may not be easy, depending
on the data source and whether it is easy to test whether a record should be
e-mailed or printed . For example, if the data source is Access, it may make
sense to create a query that selects the "e-mail" records and a query that
selects the "printer" records.

If you want to use the same mail merge main document for both merges (which
may or may not be a good idea depending on whether you are sending the
document as an attachment or plain text), you can probably do it using a
macro that does something along the following lines:

With ActiveDocument.MailMerge
' The following statement disconnects and existing data source
' and discards filters and sorts
.MainDocumentType = wdNotAMergeDocument
.MainDocumentType = wdFormLetters
.Destination = wdSendToEmail
' You will need to adapt the following to your data source. One way to find
out
' what is needed is to record a macro while setting up a data source, then
' look at the values of ActiveDocument.MailMerge.Name, .ConnectString and
..QueryString
' In Word 2002 you may not be able to do that, so save the document in HTML
format then
' reopen it in Notepad and have a look for the mail merge block near the
top.
' You may need to check WHERE [myemailfield] IS NOT NULL as well

' There is no subtype parameter in Word 2000 and earlier
.OpenDataSource _
Name:="c:\mydata\mydb.mdb", _
SQLStatement:="SELECT m.* FROM [mytable] WHERE [myemailfield] <> ''", _
SubType:=wdMergeSubTypeAccess
.Execute

' You may not need the following two lines
.MainDocumentType = wdNotAMergeDocument
.MainDocumentType = wdFormLetters
.Destination = wdSendToPrinter

' You will need to adapt the following to your data source as well.
' In some cases you may instead be able to change the Query string
successfully
' by setting .QueryString, e.g.
' .QueryString = "SELECT m.* FROM [mytable] WHERE [myemailfield] =''"

.OpenDataSource _
Name:="c:\mydata\mydb.mdb", _
SQLStatement:="SELECT m.* FROM [mytable] WHERE [myemailfield] =''", _
SubType:=wdMergeSubTypeAccess
.Execute

Peter Jamieson

 

[jco]

Thank you! This was helpful, I have now fixed it.

You will need a macro that does two merges. Each merge will need to be able
to select the appropriate records - that may or may not be easy, depending
on the data source and whether it is easy to test whether a record should be
e-mailed or printed . For example, if the data source is Access, it may make
sense to create a query that selects the "e-mail" records and a query that
selects the "printer" records.

If you want to use the same mail merge main document for both merges (which
may or may not be a good idea depending on whether you are sending the
document as an attachment or plain text), you can probably do it using a
macro that does something along the following lines:

With ActiveDocument.MailMerge
' The following statement disconnects and existing data source
' and discards filters and sorts
.MainDocumentType = wdNotAMergeDocument
.MainDocumentType = wdFormLetters
.Destination = wdSendToEmail
' You will need to adapt the following to your data source. One way to find
out
' what is needed is to record a macro while setting up a data source, then
' look at the values of ActiveDocument.MailMerge.Name, .ConnectString and
..QueryString
' In Word 2002 you may not be able to do that, so save the document in HTML
format then
' reopen it in Notepad and have a look for the mail merge block near the
top.
' You may need to check WHERE [myemailfield] IS NOT NULL as well

' There is no subtype parameter in Word 2000 and earlier
.OpenDataSource _
Name:="c:\mydata\mydb.mdb", _
SQLStatement:="SELECT m.* FROM [mytable] WHERE [myemailfield] <> ''", _
SubType:=wdMergeSubTypeAccess
.Execute

' You may not need the following two lines
.MainDocumentType = wdNotAMergeDocument
.MainDocumentType = wdFormLetters
.Destination = wdSendToPrinter

' You will need to adapt the following to your data source as well.
' In some cases you may instead be able to change the Query string
successfully
' by setting .QueryString, e.g.
' .QueryString = "SELECT m.* FROM [mytable] WHERE [myemailfield] =''"

.OpenDataSource _
Name:="c:\mydata\mydb.mdb", _
SQLStatement:="SELECT m.* FROM [mytable] WHERE [myemailfield] =''", _
SubType:=wdMergeSubTypeAccess
.Execute

Peter Jamieson

I have a database where some names (records) have e-mail adressese and
others
don't (they only have snail mail adresses). Using mail merge I would like
to
make a procedure that sends the letter as an e-mail to those who have the
e-mail adress registered, while the rest are sent to the printer for
posting.
The procedure should be as automatic as possible, preferrably only one
click
to do both.

Any suggestions to how this could best be solved?