+ Reply to Thread
Results 1 to 16 of 16

How to Mail Merge with output that matches formatting in Excel source file

  1. #1
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226

    How to Mail Merge with output that matches formatting in Excel source file

    Hi,

    I have a problem that I hope someone will be able to give me instructions to solve fairly easily.

    I have coloured text in a spreadsheet, which I need to also be coloured in the mail merged Word doc.

    However, the mail merged document text is coming out in the default black text, when it is red/blue/green in the spreadsheet.

    Is there a simple work around that will keep the coloured text in the mail merged Word document?

    Thanks for looking!
    Last edited by 6StringJazzer; 11-28-2021 at 05:33 PM. Reason: clarified title

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to Mail Merge with coloured text

    There is no simple workaround. When doing mail merge, Word does not receive formatting information from Excel.

    In your Excel data, is the same field different colors in different records, or is the same field always the same color?

    I have not tried to solve this before but I think the answer is going to be something like develop a custom mail merge with VBA.

    If you attach a sample Excel file (with private data removed or masked) and the corresponding Word file, we might be able to give you more ideas.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226

    Re: How to Mail Merge with coloured text

    6StringJazzer,

    Many thanks for replying. I have attached some dummy data and the corresponding word file.

    I hope you can make some sense of it.

    The names and form need to be mail merged in the colours they are in the excel file, if possible.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226
    Quote Originally Posted by 6StringJazzer View Post
    There is no simple workaround. When doing mail merge, Word does not receive formatting information from Excel.

    In your Excel data, is the same field different colors in different records, or is the same field always the same color?

    I have not tried to solve this before but I think the answer is going to be something like develop a custom mail merge with VBA.

    If you attach a sample Excel file (with private data removed or masked) and the corresponding Word file, we might be able to give you more ideas.

    Hi, 6StringJazzer.

    Many thanks for your reply. I have attached a spreadsheet and word document in the original thread.

    I hope it is helpful.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to Mail Merge with coloured text

    Helpful but this Word document does not seem to be set up for a mail merge.

  6. #6
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226

    Re: How to Mail Merge with coloured text

    Sorry, is the attached any better?

    I'm a beginner with Mail Merge
    Attached Files Attached Files

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to Mail Merge with coloured text

    That file seems to be trying to connect to a database, which doesn't exist.




    However, the mail merged document text is coming out in the default black text
    How did you do this?

  8. #8
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226
    Quote Originally Posted by 6StringJazzer View Post
    That file seems to be trying to connect to a database, which doesn't exist.





    How did you do this?
    I have used the mail merge wizard in MS Word 2016 and the excel file is where the merged data is linked to.

    I’m not sure if this is what you mean by a mail merge word document.

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to Mail Merge with coloured text

    I spent some time on this and could not figure out how to create VBA that would interact with the merge process and change formatting dynamically. I am not saying it's impossible, but I could not find it in a reasonable amount of time. Hopefully someone will drop by who has already dealt with a similar problem. It would take far too much time for me to figure this out from scratch, or to write VBA from scratch that would effectively perform the mail merge.

    Meantime I have attached your file which is now configured for mail merge with the Excel file.
    Attached Files Attached Files

  10. #10
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: How to Mail Merge with output that matches formatting in Excel source file

    Cross-posted at: https://www.msofficeforums.com/mail-...ured-text.html
    Please read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  11. #11
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226

    Re: How to Mail Merge with coloured text

    Many thanks for looking at this. If I find a solution, I will post it on here.

  12. #12
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226

    Re: How to Mail Merge with output that matches formatting in Excel source file

    Sorry, I never knew this had to be referenced.

    Is this because the two forums are linked in some way?

    I will adhere to the rules if I cross post in the future.
    Last edited by cosmica67; 11-29-2021 at 10:13 AM.

  13. #13
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to Mail Merge with output that matches formatting in Excel source file

    Quote Originally Posted by cosmica67 View Post
    Is this because the two forums are linked in some way?
    If you read the rule (as macropod suggested) then it will become clear.

  14. #14
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226
    Quote Originally Posted by 6StringJazzer View Post
    If you read the rule (as macropod suggested) then it will become clear.

    I have read the rule, as macropod suggested.

    I was just asking a question, that’s all.

  15. #15
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to Mail Merge with output that matches formatting in Excel source file

    Quote Originally Posted by cosmica67 View Post
    Is this because the two forums are linked in some way?
    They are not linked in any way. In fact it's because they are not linked and people on one forum do not know what people on the other forum are working on. We want to avoid having people spend time here on something that is already solved somewhere else.

  16. #16
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226

    Re: How to Mail Merge with output that matches formatting in Excel source file

    Thank you for keeping me informed.

    I will ensure that I reference if I cross post in the future.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Mail Merge - Create separate Mail merge pdf for each sheets of an excel file
    By sureshbvs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2021, 12:38 PM
  2. Mail Merge (SKIPIF Static text??)
    By jasendill in forum Word Formatting & General
    Replies: 8
    Last Post: 09-04-2018, 02:36 PM
  3. mail merge/table text direction
    By Okii in forum Word Formatting & General
    Replies: 1
    Last Post: 08-19-2016, 08:17 PM
  4. Replies: 0
    Last Post: 01-05-2015, 07:35 AM
  5. VBA code to perform mail merge using text file
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2013, 04:46 AM
  6. Replies: 2
    Last Post: 07-12-2012, 08:11 PM
  7. [SOLVED] Truncation of text when using mail merge.
    By Macy in forum Excel General
    Replies: 5
    Last Post: 09-14-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1