mail merge with individually customised background

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Why make life difficult when the whole thing can be done in Excel ?
(probably why I am not a Word expert :biggrin: :biggrin: )

Have a look at this example setup :-
Code:
'========================================================================
'- 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

Board Regular
Joined
Jul 31, 2006
Messages
123
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

Board Regular
Joined
Jul 31, 2006
Messages
123
ahh, ok.

here's a macro: while recording, I created a new document from a template.
Code:
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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:
'========================================================================
'- 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
'-------------------------------------------
 

Forum statistics

Threads
1,181,362
Messages
5,929,535
Members
436,676
Latest member
Mavri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top