Left Justify String

[SkyCraw]

Hello everyone!

I have the following string of text that I currently push out to the body of an Outlook email (using the DoCmd.SendObject function with an Access report):

sMsgBody = "Please find attached the specified Final Costing Report for WO# " & WO & vbCr & vbCr
sMsgBody = sMsgBody & "Dealer: " & rs.Fields(0).Value & vbCr
sMsgBody = sMsgBody & "Model: " & rs.Fields(1).Value & " : " & rs.Fields(2).Value & " : " & rs.Fields(3).Value & " : " & rs.Fields(4).Value & vbCr
sMsgBody = sMsgBody & "Margin $: " & rs.Fields(5).Value & vbCr
sMsgBody = sMsgBody & "Margin %: " & rs.Fields(6).Value & vbCr

Which is great and works fine! It appears in the email like so:



Now, it has been requested that the data to be left justified to the widest line... so, in this scenario, it would look like this:



I've searched for ways this might be accomplished so it's always left justified to the widest line, but haven't come across any definitive solutions yet.

Any help would be greatly appreciated as always!

 

[Cronk]

I'm glad you included a picture of the desired result because I would have called that right justified.

There is limited formatting options from Access VBA. vbTab will insert a tab space but I don't believe there is any right justified tab.

However, you could use automation to generate your right justified text in Word then copy/paste into an email. I have no suggestion though as to handling varying length strings, unless you use a font that has a fixed width of characters.

Alternatively, prepare your text as formatted html, provided your email client can handle html formatted body messages.

 

[Privateer]

My first guess, and I did not test this, is to take the length of each recordset field and subtract from the total length of the box, say 30 charactures. So if the field is ten charactures long, you have to add twenty spaces to the middle.

So "Dealer: " & chr(32) & chr(32) & chr(32) ... & rs.Fields(0).Value & vbCr

I don't know of any way to left and right justify text in the same string. Good Luck.

 

[spikepl]

VBA is not as emasculated as you might think. To right-justify text see http://support.microsoft.com/kb/217012

So for example you do:

YourLeftString & Right("                                                                         " & YourRightJustifiedString , 25) if your right-justified column is to be 25 characters

 

[spikepl]

This is not likely to look good though, becasue only fixed-pitch fonts, like Courier, will allign all the characters under each other.

To align things better (still not perfect with proportional fonts) you need html (so also .Htmlbody) and Outlook object, not SendObject.

 

[SkyCraw]

Thanks for all the quick replies everyone! All your suggestions will definitely help!

spikepl, could you provide an example using .Htmlbody within an Outlook object? I've used the Outlook object in another instance where I needed to attach multiple reports in PDF form to one email, but never the .Htmlbody function.

I also might need some guidance in HTML as well... I know some generic things about it but alas, I'm still a HTML noob :3

 

[CJ_London]

the other function you can use is the space function

mystr1="Hello" & space(20-len(myField1)) & myField1
mystr2="Hi There" & space(17-len(myField2)) & myField2

 

[SkyCraw]

Thanks CJ!

So, taking this logic and then building upon it, could I get the length of each string and left justify each to the longest one?

Kinda like so?

Dim s1 As String
Dim s2 As String
Dim s3 As String
Dim s4 As String
Dim LongestS As String

s1 = Len(rs.Fields(0).Value)
s2 = Len(rs.Fields(1).Value & " : " & rs.Fields(2).Value & " : " & rs.Fields(3).Value & " : " & rs.Fields(4).Value)
s3 = Len(rs.Fields(5).Value)
s4 = Len(rs.Fields(6).Value)

LongestS = "insert function to determine which one is longest here"

sMsgBody = "Please find attached the specified Final Costing Report for WO# " & WO & vbCr & vbCr
sMsgBody = sMsgBody & "Dealer: " & Space(LongestS - s1) & rs.Fields(0).Value & vbCr
sMsgBody = sMsgBody & "Model: " & Space(LongestS - s2) & rs.Fields(1).Value & " : " & rs.Fields(2).Value & " : " & rs.Fields(3).Value & " : " & rs.Fields(4).Value & vbCr
sMsgBody = sMsgBody & "Margin $: " & Space(LongestS - s3) & rs.Fields(5).Value & vbCr
sMsgBody = sMsgBody & "Margin %: " & Space(LongestS - s4) & rs.Fields(6).Value & vbCr

 

[CJ_London]

yes - although you could always just choose a number that is big enough - at least all your emails would be consistent in appearance.

You would also need to adjust for the initial string or standardise them e.g.

"Dealer  : "
"Model   : "
"Margin $: "

LongestS would be an integer, not a string and you might want to add a few spaces to increase separation

Also, in your code, you do not need the .value anywhere - it is the default property returned

If you want to calculate LongestS, it would be easier as you go

s1 = Len(rs.Fields(0))
longests=len(s1)
s2 = Len(rs.Fields(1) & " : " & rs.Fields(2) & " : " & rs.Fields(3) & " : " & rs.Fields(4))
if len(s2)>longests then longests=len(s2)
s3 = Len(rs.Fields(5))
if len(s3)>longests then longests=len(s3)
s4 = Len(rs.Fields(6))
if len(s4)>longests then longests=len(s4)

 

[SkyCraw]

Awesome! Thanks for your help!

Here's the end result

Dim s1 As Integer
Dim s2 As Integer
Dim s3 As Integer
Dim s4 As Integer
Dim LongestS As Integer

s1 = Len(rs.Fields(0))
LongestS = s1
s2 = Len(rs.Fields(1) & " : " & rs.Fields(2) & " : " & rs.Fields(3) & " : " & rs.Fields(4))
If s2 > LongestS Then LongestS = s2
s3 = Len(rs.Fields(5))
If s3 > LongestS Then LongestS = s3
s4 = Len(rs.Fields(6))
If s4 > LongestS Then LongestS = s4

sMsgBody = "Please find attached the specified Final Costing Report for WO# " & WO & vbCr & vbCr
sMsgBody = sMsgBody & "Dealer  : " & Space(LongestS - s1) & rs.Fields(0) & vbCr
sMsgBody = sMsgBody & "Model   : " & Space(LongestS - s2) & rs.Fields(1) & " : " & rs.Fields(2) & " : " & rs.Fields(3) & " : " & rs.Fields(4) & vbCr
sMsgBody = sMsgBody & "Margin $: " & Space(LongestS - s3) & rs.Fields(5) & vbCr
sMsgBody = sMsgBody & "Margin %: " & Space(LongestS - s4) & rs.Fields(6) & vbCr