Left Justify String (1 Viewer)

SkyCraw

Registered User.
Local time
Today, 11:35
Joined
Oct 9, 2013
Messages
100
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):

Code:
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:

Original.PNG

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:

Edit.PNG

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! :D
 

Cronk

Registered User.
Local time
Tomorrow, 01:35
Joined
Jul 4, 2013
Messages
2,770
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

Registered User.
Local time
Today, 10:35
Joined
Aug 16, 2011
Messages
191
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

Eledittingent Beliped
Local time
Today, 15:35
Joined
Nov 3, 2010
Messages
6,144
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:
Code:
YourLeftString & Right("                                                                         " & YourRightJustifiedString , 25) if your right-justified column is to be 25 characters
 

spikepl

Eledittingent Beliped
Local time
Today, 15:35
Joined
Nov 3, 2010
Messages
6,144
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

Registered User.
Local time
Today, 11:35
Joined
Oct 9, 2013
Messages
100
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

Super Moderator
Staff member
Local time
Today, 14:35
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 11:35
Joined
Oct 9, 2013
Messages
100
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?

Code:
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

Super Moderator
Staff member
Local time
Today, 14:35
Joined
Feb 19, 2013
Messages
16,553
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.

Code:
"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
Code:
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

Registered User.
Local time
Today, 11:35
Joined
Oct 9, 2013
Messages
100
Awesome! Thanks for your help!

Here's the end result :)

Code:
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
 

Users who are viewing this thread

Top Bottom