Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-20-2018, 10:14 AM
mgsjarvis mgsjarvis is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2018
Posts: 2
mgsjarvis is on a distinguished road
Default MAIL Merge - Site Address / Invoice address

Hi all,



Just after some help. Need to create a mail merge so by default it picks up the invoice address if populated which I have already set up but having difficulty figuring out how to get it so if the invoice address columns are blank it will skip these and go to the columns where the site address details are held and use this address instead.

Many thanks in advance

M
Reply With Quote
  #2  
Old 09-20-2018, 03:05 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,468
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

For that you'd use a field coded like:
{IF{MERGEFIELD Inv_Addr1}= "" "{MERGEFIELD Site_Addr1}↵
{MERGEFIELD Site_Addr2}↵
{MERGEFIELD Site_Loc}{MERGEFIELD Site_State}{MERGEFIELD Site_PCode}" "{MERGEFIELD Inv_Addr1}↵
{MERGEFIELD Inv_Addr2}↵
{MERGEFIELD Inv_Loc}{MERGEFIELD Inv_State}{MERGEFIELD Inv_PCode}"}
or:
{IF«Inv_Addr1»= "" "«Site_Addr1»↵
«Site_Addr2»↵
«Site_Loc» «Site_State» «Site_PCode»" "«Inv_Addr1»↵
«Inv_Addr2»↵
«Inv_Loc» «Inv_State» «Inv_PCode»"}

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. Likewise, the chevrons (i.e. '« »') are part of the actual mergefields - which you can insert from the 'Insert Merge Field' dropdown (i.e. you can't type or copy & paste them from this message, either). The spaces represented in the field constructions are all required. Instead of the ↵ symbols, you should use real line breaks.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 09-25-2018, 05:56 AM
mgsjarvis mgsjarvis is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2018
Posts: 2
mgsjarvis is on a distinguished road
Default

Thanks for the above. I've worked out a way using the dataset I hold. The issue that I'm having now is that there are line breaks say if Address Line 4 is missing. It's not always the one address line missing. It could be Line 3 on one letter and then line 2 the next.

See below for what I am using at present which does what I need it to do.

{ IF { MERGEFIELD "Use_Billing_Address"
\*charformat}="No""{ MERGEFIELD "Site_Name"
\*charformat}
{ MERGEFIELD "Site_Add_1" \*charformat}
{ MERGEFIELD "Site_Add_2" \*charformat}
{ MERGEFIELD "Site_Add_3" \*charformat}
{ MERGEFIELD "Site_Add_4" \*charformat}
{ MERGEFIELD "Site_Add_Post_Code" \*charformat}" "{MERGEFIELD
"Billing_Address_Contract_Name" \*charformat}
{ MERGEFIELD "Billing_Address_Line_1" \*charformat}
{ MERGEFIELD "Billing_Address_Line_2" \*charformat}
{ MERGEFIELD "Billing_Address_Line_3" \*charformat}
{ MERGEFIELD "Billing_Address_Line_4" \*charformat}
{ MERGEFIELD "Billing_Address_Postcode" \*charformat}"}
Reply With Quote
  #4  
Old 09-25-2018, 02:43 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,468
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

First, get rid of all those \*charformat switches - you don't need them.

Second, make sure there's a space before and after "No".

You should how have:
{IF{MERGEFIELD "Use_Billing_Address"}= "No" "{MERGEFIELD Site_Name"}¶
{MERGEFIELD Site_Add_1}¶
{MERGEFIELD Site_Add_2}¶
{MERGEFIELD Site_Add_3}¶
{MERGEFIELD Site_Add_4}¶
{MERGEFIELD Site_Add_Post_Code}" "{MERGEFIELD Billing_Address_Contract_Name}¶
{MERGEFIELD Billing_Address_Line_1}¶
{MERGEFIELD Billing_Address_Line_2}¶
{MERGEFIELD Billing_Address_Line_3}¶
{MERGEFIELD Billing_Address_Line_4}¶
{MERGEFIELD Billing_Address_Postcode}"}
where the ¶ is a paragraph break or line break.

For the empty line suppression, see the corresponding topic in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum:
http://www.msofficeforums.com/mail-m...ps-tricks.html
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge Envelope Address Box Rob S Mail Merge 1 06-05-2017 06:23 AM
Return Address mail merge GeorgeLawshe Mail Merge 2 06-03-2016 11:04 AM
How to forward all e-mails from one address to five (5) other e-mail address? adi2012 Outlook 1 09-09-2012 06:41 PM
mail merge address list disappeared janm Mail Merge 1 08-06-2012 11:41 PM
Mail merge and address book CDTom Mail Merge 0 01-17-2012 09:08 AM


All times are GMT -7. The time now is 11:46 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft