Mergemail IF Statements in VBA

[jjj2k]

I got a bunch of fields on mergemail as follows:

---

{ if { MERGEFIELD "Code" }="cahs" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" cahs!n_cahs \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ca" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" ca!n_ca \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ch" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" ch!n_ch \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ce" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" ce!n_ce \f 4 \h} "" }{ if { MERGEFIELD "Code" }="cp" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" cp!n_cp \f 4 \h} "" }{ if { MERGEFIELD "Code" }="cr" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" cr!n_cr \f 4 \h} "" }{ if { MERGEFIELD "Code" }="cs" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" cs!n_cs \f 4 \h} "" }{ if { MERGEFIELD "Code" }="h" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" h!n_h \f 4 \h} "" }{ if { MERGEFIELD "Code" }="lrc" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" lrc!n_lrc \f 4 \h} "" }{ if { MERGEFIELD "Code" }="mn" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" mn!n_mn \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ms" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" ms!n_ms \f 4 \h} "" }{ if { MERGEFIELD "Code" }="n" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" n!n_n \f 4 \h} "" }{ if { MERGEFIELD "Code" }="s" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" s!n_s \f 4 \h} "" }{ if { MERGEFIELD "Code" }="w" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" w!n_w \f 4 \h} "" }{ if { MERGEFIELD "Code" }="dc" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" dc!n_dc \f 4 \h} "" }{ if { MERGEFIELD "Code" }="gt" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Cost_Trend.xlsx" gt!n_gt \f 4 \h} "" }{ if { MERGEFIELD "Code" }="cahs" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" cahs!n_cahs \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ca" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" ca!n_ca \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ch" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" ch!n_ch \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ce" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" ce!n_ce \f 4 \h} "" }{ if { MERGEFIELD "Code" }="cp" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" cp!n_cp \f 4 \h} "" }{ if { MERGEFIELD "Code" }="cr" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" cr!n_cr \f 4 \h} "" }{ if { MERGEFIELD "Code" }="cs" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" cs!n_cs \f 4 \h} "" }{ if { MERGEFIELD "Code" }="h" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" h!n_h \f 4 \h} "" }{ if { MERGEFIELD "Code" }="lrc" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" lrc!n_lrc \f 4 \h} "" }{ if { MERGEFIELD "Code" }="mn" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" mn!n_mn \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ms" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" ms!n_ms \f 4 \h} "" }{ if { MERGEFIELD "Code" }="n" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" n!n_n \f 4 \h} "" }{ if { MERGEFIELD "Code" }="s" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" s!n_s \f 4 \h} "" }{ if { MERGEFIELD "Code" }="w" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" w!n_w \f 4 \h} "" }{ if { MERGEFIELD "Code" }="dc" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" dc!n_dc \f 4 \h} "" }{ if { MERGEFIELD "Code" }="gt" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Budget.xlsx" gt!n_gt \f 4 \h} "" }Mobile Usage { MERGEFIELD "RegionText" \* MERGEFORMAT}


{ if { MERGEFIELD "Code" }="cahs" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" cahs!n_cahs \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ca" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" ca!n_ca \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ch" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" ch!n_ch \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ce" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" ce!n_ce \f 4 \h} "" }{ if { MERGEFIELD "Code" }="cp" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" cp!n_cp \f 4 \h} "" }{ if { MERGEFIELD "Code" }="cr" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" cr!n_cr \f 4 \h} "" }{ if { MERGEFIELD "Code" }="cs" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" cs!n_cs \f 4 \h} "" }{ if { MERGEFIELD "Code" }="h" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" h!n_h \f 4 \h} "" }{ if { MERGEFIELD "Code" }="lrc" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" lrc!n_lrc \f 4 \h} "" }{ if { MERGEFIELD "Code" }="mn" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" mn!n_mn \f 4 \h} "" }{ if { MERGEFIELD "Code" }="ms" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" ms!n_ms \f 4 \h} "" }{ if { MERGEFIELD "Code" }="n" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" n!n_n \f 4 \h} "" }{ if { MERGEFIELD "Code" }="s" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" s!n_s \f 4 \h} "" }{ if { MERGEFIELD "Code" }="w" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" w!n_w \f 4 \h} "" }{ if { MERGEFIELD "Code" }="dc" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" dc!n_dc \f 4 \h} "" }{ if { MERGEFIELD "Code" }="gt" { LINK Excel.Sheet.8 "C:\\Documents and Settings\\All Users\\Shared Documents\\Dxxx\\Monthly\\xxx_Summary_Mobile_Usage.xlsx" gt!n_gt \f 4 \h} "" }

----

is there a way i can use vba macro to insert the IF statements such as this since to new word documents as I am trying to do it for 50 other companies?

[macropod]

Hi jjj2k,

If the issue is that the paths and/or filenames for the other companies differ, a simply Find/Replace with the field code display toggled 'on' should suffice.

Conversely, if you're trying to consolidate the data from all 50 companies into a single report, the better approach would be to create an Excel workbook that does that, then simply reference the appropriate ranges in the consolidation workbook. Better still, configure the consolidation workbook so that you can use it directly as the mailmerge data source without recourse to LINK fields.