mail merge with individually customised background

[StrawS]

I believe I have a particularly tricky question.

is it possible to do a mail merge from excel to word where the background colour/image of each resulting letter in word is determined by a data field in excel?

What I want to do is be able to customise the background of the resulting letters according to each individual record from the excel database.

Perhaps this could be done through choosing an image or colour to put in the background, or making excel choose from a selection of word templates for each letter.

I hope I've explained myself clearly enough. Also, this is a conceptual question at this stage so I have no examples or half-written code to quote.

Please tell me if you believe this cannot be done or if you have any ideas of how to accomplish it.

 

[BrianB]

Why make life difficult when the whole thing can be done in Excel ?
(probably why I am not a Word expert )

Have a look at this example setup :-

'========================================================================
'- EXCEL MAILMERGE CODE EXAMPLE
'- works by incrementing database row number in cell A1 of the mail sheet
'- which updates =INDIRECT() formulas in that sheet to do the lookup.
'- Brian Baulsom  July 2007 - using Excel 2000
'========================================================================
'- DATABASE SHEET
'- Column headings A1:E1 - First Name, Last Name,Address,Town,PostCode
'-
'- MAILMERGE WORKSHEET
'- Mailsheet has Database row number in cell A1
'- Cell A2 - First + Last Name formula
'-           =INDIRECT("Database!A" &A1) &" " &INDIRECT("Database!B" &A1)
'- Cell A3 - Address formula        =INDIRECT("Database!C" & $A$1)
'- Cell A4 - Town formula           =INDIRECT("Database!D" & $A$1)
'- Cell A5 - PostCode formula       =INDIRECT("Database!E" & $A$1)
'========================================================================



'========================================================================
'- CODE
'========================================================================
Sub MailMerge()
    Dim MailSheet As Worksheet
    Dim DataSheet As Worksheet
    Dim DataRow As Long
    '--------------------------------------------------
    Set MailSheet = Worksheets("MailSheet")
    MailSheet.Activate
    '--------------------------------------------------
    For DataRow = 2 To 5
        MailSheet.Range("A1").Value = DataRow
        MailSheet.PrintPreview
    Next
End Sub
'-----------------------------------------------------

 

[StrawS]

Hi BrianB,
Thanks for replying, I don't see where your code chooses a background for each letter based on a data column, so I don't understand how this answers my question.
Please explain.

Thanks
Straws

 

[BrianB]

This is just the basis of something that may be of interest - not a fully working application.
The rest of your requirements were too vague to write any code.
If you are interested in going forward I suggest you record some macos of your own & post the code, or be more explicit on how you would wish to proceed.

 

[StrawS]

ahh, ok.

here's a macro: while recording, I created a new document from a template.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/27/2007 by Preferred Customer
'
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    Documents.Add Template:= _
        "C:\Program Files\Microsoft Office\Templates\1033\ELEGMLTR.DOT", _
        NewTemplate:=False, DocumentType:=0
End Sub

my question is: is it possible to make the code choose the template for each record based on a variable of that record?

For example, if the customers in my database live in 3 different cities, and the appropriate template depends on where the customer lives, how do you code the macro so that it chooses the right template for each individual customer?

Please let me know if you need any more details.

Thanks
Straws

 

[BrianB]

Getting there. This is the normal process of development. Need to think about how you actually do the job. Here it runs through the whole database printing sheets as it goes.

Thinking of how to ease the setting up of the templates. Depends on layout. Need to have the same cell on each for the data row. Might be easier to have a master sheet with all fields and delete the ones you don't want. I have used Town as a convenient field. It may be better to have another special column in the database with the template name.

Here is the code adapted if you have a sheet for each Town.

'========================================================================
'- CODE
'- changed to select a worksheet depending on Town in database
'========================================================================
Sub MailMerge()
    Dim MailSheet As Worksheet
    Dim DataSheet As Worksheet
    Dim DataRow As Long
    Dim Town As String
    '-------------------------------------------------
    Set DataSheet = Worksheets("Database")
    For DataRow = 2 To 5
        '- select template according to Town
        Town = DataSheet.Cells(DataRow, 4).Value
        Set MailSheet = Worksheets(Town)
        '- update sheet
        MailSheet.Range("A1").Value = DataRow
        MailSheet.PrintPreview
    Next
End Sub
'-------------------------------------------