Character spacing in VBA

A

Artemis Hothmire

Registered User.
Local time
Today, 08:46
Joined
Nov 19, 2012
Messages
23
Good afternoon,

I am trying to figure out how to tab out data in VBA, to creat formatted messages through outlook. Instead of identifying the different fields with random number sapcing, i.e., 6, 7 or 10. Below is what I have for spacing in my code.

Code: 
 Public Function GetF6IMRLDATA()
Dim db As Database
Dim rs As Recordset
Dim DStr As String
Dim fld As Field
Dim i As Integer
Dim FldVal As String
Dim RecLine As String
Dim Spacer As String
Set db = CurrentDb
Spacer = " "
Set rs = db.OpenRecordset("select * from qryActivityF6StatusCodes_Message;")
         If rs.EOF = False Then
         For Each fld In rs.Fields
         If fld.Name = "AAI" Then
              FldVal = Left(fld.Name & Spacer, 8)
              ElseIf fld.Name = "PN" Then
              FldVal = FldVal & Left(fld.Name & Spacer, 15)
              ElseIf fld.Name = "CAGE" Then
              FldVal = FldVal & Left(fld.Name & Spacer, 7)
              ElseIf fld.Name = "EQSN" Then
              FldVal = FldVal & Left(fld.Name & Spacer, 22)
              ElseIf fld.Name = "STATUS" Then
              FldVal = FldVal & Left(fld.Name & Spacer, 8)
              ElseIf fld.Name = "NIIN" Then
              FldVal = FldVal & Left(fld.Name & Spacer, 11)
              ElseIf fld.Name = "TODAYS DATE" Then
              FldVal = FldVal & Left(fld.Name & Spacer, 13)
              ElseIf fld.Name = "DAYS F6" Then
              FldVal = FldVal & Left(fld.Name & Spacer, 9)
        End If
    Next
End If
DStr = FldVal
               While rs.EOF = False
               For Each fld In rs.Fields
                      If IsNull(fld.Value) Then
                      If fld.Name = "AAI" Then
                          FldVal = Left(Spacer, 8)
                      ElseIf fld.Name = "PN" Then
                          FldVal = Left(Spacer, 15)
                      ElseIf fld.Name = "CAGE" Then
                          FldVal = Left(Spacer, 7)
                      ElseIf fld.Name = "EQSN" Then
                          FldVal = Left(Spacer, 22)
                      ElseIf fld.Name = "STATUS" Then
                          FldVal = Left(Spacer, 8)
                      ElseIf fld.Name = "NIIN" Then
                          FldVal = Left(Spacer, 11)
                      ElseIf fld.Name = "TODAYS DATE" Then
                          FldVal = Left(Spacer, 13)
                      ElseIf fld.Name = "DAYS F6" Then
                          FldVal = Left(Spacer, 9)
                End If
           Else
                      If fld.Name = "AAI" Then
                         FldVal = Left(fld.Value & Spacer, 8)
                      ElseIf fld.Name = "PN" Then
                         FldVal = Left(fld.Value & Spacer, 15)
                      ElseIf fld.Name = "CAGE" Then
                         FldVal = Left(fld.Value & Spacer, 7)
                      ElseIf fld.Name = "EQSN" Then
                         FldVal = Left(fld.Value & Spacer, 22)
                      ElseIf fld.Name = "STATUS" Then
                         FldVal = Left(fld.Value & Spacer, 8)
                      ElseIf fld.Name = "NIIN" Then
                         FldVal = Left(fld.Value & Spacer, 11)
                      ElseIf fld.Name = "TODAYS DATE" Then
                         FldVal = Left(fld.Value & Spacer, 13)
                      ElseIf fld.Name = "DAYS F6" Then
                         FldVal = Left(fld.Value & Spacer, 9)
                   End If
                End If
          RecLine = RecLine & FldVal
       Next
   DStr = DStr & vbCrLf & RecLine
   RecLine = vbNullString
   rs.MoveNext
Wend
rs.Close
GetF6IMRLDATA = DStr
End Function

As you can see all the fields have random numbers to creat a spacing structure in the email this function creates. The problem I am running into, is that, while this works well for Windows XP it bugs in Windows 7 and throws the formatting off. How do I put Tabs in the spacing to create an even looking email in windows 7? Any help you have would be greatly appreciated.
 
Last edited:
I am surprised that this stuff ever did what it was supposed to, unless your Outlook is set up to use a proprotional font like Courier. Reliable column formatting is normally accomplished using HTML tables.

Anyhow, "it bugs in Windows 7 and throws the formatting off" is meaningless for all but yourself - we have no remote vision or mind-reading powers :D
 
Below is what the message looks like using Windows XP:

Feild 1: Feild 2:
123 456


In Windows 7, it looks like this:

Feild 1: Feild 2:
123 456


The sacing is inaccurate. I would like to tab out the formatting in the "spacer" instead of giving it individual digit spacing. This way all emails i send out will be crisp and properly formatted.
 
Last edited:
Code: 
Feild 1: Feild 2:
123      456

See how code tags preserves the spacing

Brian
 
So at the end of the day you are going to pass the String to the email body is that right?

Have you tried using HTML body?
 
I am rather new to most of this, so no. I have yet to take a basic programing course through my university. I dont know about HTML script and am really just getting used to SQL/VBA
 
And yes, at the end of the day, the email should look like the attachment (Sorry, had to zip it to upload it). The greeting of the email is being called by a different piece of code. Its the messages body, the columns of data, that I need to structure.
 

Attachments

Not sure if this will meet your needs but have you tried using he Space function? e.g.

fld1 & space(20-len(fld1)) & fld2 & space(25-len(fld2))....etc

you could put this into a function as follows:

Code: 
Function Spacer(ColWidth as integer, Str as string) as string
 
    Spacer=Str & Space(ColWidth-len(Str))
 
End Function
 
If you are using HTML body, then you can use <table>.. Whihc will auto format the spaces for you.. In my opinion, might be better. Try this..
Code: 
Public Function GetF6IMRLDATA() As String
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim DStr As String, i As Long
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM qryActivityF6StatusCodes_Message;")
    DStr = "<table style='width:100%'>"
    If rs.RecordCount <> 0 Then
        DStr = DStr & "<tr>"
        For i = 0 To rs.Fields.Count - 1
            DStr = DStr & "<td>" & rs.Fields(i).Name & "</td>"
        Next
        DStr = DStr & "</tr>"
        Do While Not rs.EOF
            DStr = DStr & "<tr>"
            For i = 0 To rs.Fields.Count - 1
                DStr = DStr & "<td>" & rs.Fields(i) & "</td>"
            Next
            DStr = DStr & "</tr>"
            rs.MoveNext
        Loop
    End If
    DStr = DStr & "</table>"
    GetF6IMRLDATA = DStr
End Function
 
pr2-eugin said:
If you are using HTML body, then you can use <table>.. Whihc will auto format the spaces for you.. In my opinion, might be better. Try this..
Code: 
Public Function GetF6IMRLDATA() As String
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim DStr As String, i As Long
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM qryActivityF6StatusCodes_Message;")
    DStr = "<table style='width:100%'>"
    If rs.RecordCount <> 0 Then
        DStr = DStr & "<tr>"
        For i = 0 To rs.Fields.Count - 1
            DStr = DStr & "<td>" & rs.Fields(i).Name & "</td>"
        Next
        DStr = DStr & "</tr>"
        Do While Not rs.EOF
            DStr = DStr & "<tr>"
            For i = 0 To rs.Fields.Count - 1
                DStr = DStr & "<td>" & rs.Fields(i) & "</td>"
            Next
            DStr = DStr & "</tr>"
            rs.MoveNext
        Loop
    End If
    DStr = DStr & "</table>"
    GetF6IMRLDATA = DStr
End Function
Click to expand...


Paul, I hate to ask this as it might seem like your doing my work, but how would I use this code with the fields that I already have. In other words, when its finished, what would the code look like. Sorry, I hate to be that guy, but I am still learning.....
 
Not to worry Artemis Hothmire, The output would be a very long string (based on the number of records). that will look something like..
Code: 
<table style='width:100%'><tr><td>A_ID</td><td>FirstName</td><td>LastName</td><td>AgentName</td><td>Password</td><td>Daytarget</td><td>EMail</td><td>Theshold</td><td>Team</td><td>Campain</td><td>Active</td><td>UserName</td><td>LastUpdate</td><td>RecLimitStart</td><td>RecLimitEnd</td></tr><tr><td>1</td><td>Abigale</td><td>Walker</td><td>Abbie</td><td>Pa55w0rd</td><td></td><td></td><td></td><td>Manager</td><td></td><td>False</td><td>awalker</td><td>13/06/2013</td><td></td><td></td></tr><tr><td>2</td><td>Alex</td><td>Pattison</td><td>Alex</td><td>1234</td><td></td><td></td><td></td><td>Older-3</td><td></td><td>False</td><td>apattison</td><td></td><td></td><td></td></tr></table>
So when used in the HTML body (from VBA), it would be like..

attachment.php


So if you have your code to send email as..
Code: 
    With OutMail
        .Display
        .To = "someone@somedomain.com"
        .Subject = "Table Format"
        .HTMLBody = GetF6IMRLDATA & .HTMLBody
        '.Send
    End With
 

Attachments

  • tableEmail.png
    tableEmail.png
    4.7 KB · Views: 454
You must log in or register to reply here.

Similar threads

F
Solved DAO.field doesn't give the field name
Replies
9
Views
527
freuzo
F
B
VBA problem relating to SQL linked table
Replies
6
Views
1,071
CJ_London
C
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
780
CJ_London
C
J_Fo
Solved Add record if not found, still adding record
Replies
6
Views
733
J_Fo
J_Fo
K
Solved Why setting the backstyle in vba fails?
2
Replies
26
Views
636
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom