Mergefield Help required

[lochdara]

Hi Guys

I have a excel 2010 mail merge setup for a roster system.
In the past 80 odd emails had to be sent out to each user on our list.
Now the mail merge can easily send the bulk email.

We have 2 types of person. A supervisor and an ordinary volunteer.
Everyone gives dates of when they are free to volunteer

What i would like to do is that everyone still gets an email but they only get an email of their date. They cannot see other volunteers dates. The mail merge sorts this..

I would like the supervisor to receive the same email but with the added part where they know what volunteers are on duty that night.
I have added a supervisor field of Y and N to try use this as code but i cant work it into the file.

How can i combine the code of this in the mergefield box

I dont see an attachment upload but the excel and word doc file are located https://www.dropbox.com/sh/o7qfzzcy29giarz/AACKWVrOHbj_UchBaFjriKpBa?dl=0

Thanks in advance

Malcolm

 

[Macropod]

You can do this with a field coded as:

{IF{MERGEFIELD Supervisor}= "Y" "Your team list will be {DATABASE \d "{FILENAME \p}/../roster_test.xlsx" \s "SELECT [Fname] & ' ' & [Lname] FROM [Sheet1$] WHERE FORMAT([Date], 'YYYYMMDD') = {MERGEFIELD Date \@ YYYYMMDD} AND [Supervisor] = 'N' ORDER BY [Lname] " \l "0" \b "0"}"}

where 'roster_test.xlsx' is the workbook you're using for the mailmerge and that workbook is stored in the same folder as the mailmerge main document. The list is output as a table. If you want to apply auto-fitting to the table, change:
\b "0"
to:
\b "16"

Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. The spaces represented in the field construction are all required.

 

[lochdara]

Thank you for that. I will break it down in small pieces to understand it more. I did try it out and it did not give me the list of names .
Here is a picture of the code when i Toggle field codes. I have loaded a few picture of what i am getting on screen.

I have your code copied and pasted at the bottom of the page for easy reading.
Photos are located in the same folder as the files which is here https://www.dropbox.com/sh/o7qfzzcy29giarz/AACKWVrOHbj_UchBaFjriKpBa?dl=0

 

[Macropod]

You seem to have some field coding errors. For example, after:
FORMAT([Date]
you have a spurious }, thus:
FORMAT([Date]}
You also appear to have hard-coded the date to test against, as 12122015, instead of testing the date in the data source.

FWIW, I've created a macro to convert the field code in post #2 to a working field code and posted it in the Mailmerge Tips and Tricks threads at:
Mailmerge Tips & Tricks
and:
Word Mailmerge Tips & Tricks | Windows Secrets Lounge
See under Convert Text Representations of Fields to Working Fields

 

[lochdara]

Hi Paul.

I did spot some mistakes after i posted and i corrected them. The dates was not hard coded but appeared that way, When i click the date and click toggle field code it comes in as you described how to write it. Then sometimes the code disappears all together and i have to retype. I did manage to get it reading from the your instruction by clickung update but then it stopped again. I have been busy with work to try again but i will get back to it tomorrow. Many thanks for your input

Malcolm

 

[Macropod]

sometimes the code disappears all together and i have to retype.

You may find it easier to do your field code construction by forcing Word to display the codes rather than their results. You can toggle the field code display on/off via Alt-F9. You may even find you have multiple copies of the fields in your document if, as you say, you've re-typed them.