Email Merge

[cswa860]

Hello,

We have an aging report in Excel that shows invoice approvers and how many invoices they have yet to approve. There could be up to 6000 invoices, but not 6000 approvers as each approver could have 1 or 50 invoices they have not approved. We have a Word template and use an email merge, but that is a one to one merge resulting in one approver getting multiple emails...one for each outstanding invoice. Is there a way to group all their invoices like shown below so they only get 1 email? I've searched the web and did find a mail merge grouping using the type directory, but not email. We do have duplicate names, but their employee id is unique.

I tried concatenating all invoice data for each approver and doing a find/replace using Ctrl+J so each invoice is on it's own line making it look like an address block, but the problem is that the data doesn't line up. Supplier names vary in length as do invoice numbers. If there was a way to use the concat method but align the invoice data then that would work for our needs

Appreciate any help!
Cathy

EXAMPLE1 - this would be our ideal format/layout

SUPPLIER INVOICE AMOUNT #DAYS
----------------------------------------------------
ABC TECH 12345 100.00 10
TECH ONE 23456 200.00 15
A-1 TECH 34567 300.00 25

EXAMPLE2 - this layout would also work, but example1 would be nice to have

SUPPLIER: ABC TECH INVOICE: 12345 AMOUNT: 100.00 #DAYS: 10
SUPPLIER: TECH ONE TWO INVOICE: 23456 AMOUNT: 200.00 #DAYS: 15
SUPPLIER: A-1 TECH INVOICE: 34567 AMOUNT: 300.00 #DAYS: 25

[SlipEternal]

Find the maximum length of each field. Then create "padding" functions. For instance, "SUPPLIER" has 8 characters. "ABC TECH" and "TECH ONE" each have 8 characters. "A-1 TECH" has only 7 characters. So, 8 is your maximum.
"INVOICE" has 7 characters.
"AMOUNT" has 6 characters
"#DAYS" has 5 characters

Suppose these numbers of characters are stored in the following variables:
maxSupplierLength
maxInvoiceLength
maxAmountLength
maxNumDaysLength

Then, you can have:
Header row:
CONCATENATE(LEFT("SUPPLIER"&SPACE(maxSupplierLength),maxSupplierLength)," ",LEFT("INVOICE"&SPACE(maxInvoiceLength),maxInvoiceLength)," ",LEFT("AMOUNT"&SPACE(maxAmountLength),maxAmountLength)," ",LEFT("#DAYS"&SPACE(maxNumDaysLength),maxNumDaysLength))
Divider Row:
CONCATENATE(STRING("-",maxSupplierLength)," ",STRING("-",maxInvoiceLength)," ",STRING("-",maxAmountLength)," ",STRING("-",maxNumDaysLength))
Table Row:
CONCATENATE(LEFT(supplier#Name&SPACE(maxSupplierLength),maxSupplierLength)," ",LEFT(invoice#&SPACE(maxInvoiceLength),maxInvoiceLength)," ",LEFT(amount#&SPACE(maxAmountLength),maxAmountLength)," ",LEFT(#days#&SPACE(maxNumDaysLength),maxNumDaysLength))

I'm using pseudocode rather than actual code, so I hope it makes sense what I am suggesting. Basically, I am suggesting that you pad the values on the right. You can also use left padding if you want.

[Tim_x]

using this post to go advance

[Tim_x]

vdhhhh )