Trouble with space padding in VBA OutMail

[sailor_liam]

Good day,

I am trying to convert data from an excel spreadsheet into an e-mail via an excel macro (OutMail). The purpose of this e-mail is to inform individuals of their outstanding invoices, with the top line being categories of days outstanding:

"|CURRENT |31-60 |61-90 |91-120 |151-180 |180+ |"

And the line below it inputting the amount they owe for each category, for example:

"|0 |$2,000.00 |0 |0 |$555.55 |0 |"

The above example is to tell the recipient that $2,000 is 31-60 days late, and $555.55 is 151-180 days late.

The numbers in the second line are pulled from the spreadsheet, and the goal is to have the "|" (aka pipes) line up for the first row and second row.

I've been using code similar to the pseudo code below to pad the spaces to line up the pipes:

Line2 = "|" & AmountOwed & Space(25 - Len(AmountOwed)) & "|"

However due to the different point sizes between the numbers, separators and periods, these lines do not line up well at all.

Question: Is there a method to pad the empty space after the amount owed to have each pipe line up perfectly?

Thanks!

 

[mole999]

in all honesty, it maybe quicker to select / grab an image of the area, that is in the grid, and paste that in the post, you then won't need the pipes, and it will line up

not sure the best way to code that though

 

[Tinbendr]

Here's an article about sending a range.