Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-20-2018, 10:14 AM
mgsjarvis mgsjarvis is offline MAIL Merge - Site Address / Invoice address Windows 10 MAIL Merge - Site Address / Invoice address Office 2013
Novice
MAIL Merge - Site Address / Invoice address
 
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 online now MAIL Merge - Site Address / Invoice address Windows 7 64bit MAIL Merge - Site Address / Invoice address Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-25-2018, 05:56 AM
mgsjarvis mgsjarvis is offline MAIL Merge - Site Address / Invoice address Windows 10 MAIL Merge - Site Address / Invoice address Office 2013
Novice
MAIL Merge - Site Address / Invoice address
 
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 online now MAIL Merge - Site Address / Invoice address Windows 7 64bit MAIL Merge - Site Address / Invoice address Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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:
https://www.msofficeforums.com/mail-...ps-tricks.html
__________________
Cheers,
Paul Edstein
[Fmr 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
MAIL Merge - Site Address / Invoice address 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 - Site Address / Invoice address 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:07 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft