cdo message format

[ck1999]

I am trying to send an email using objmessage but I cannot get the columns to line up. I am using plain text email. Any ideas?

Sub sendmass()

    Dim dbs As Database, tdf As TableDef, rstOrders As Recordset, rstemail As Recordset, rstserver As Recordset

    Const cdoSendUsingMethod = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing"[/URL]
    Const cdoSendUsingPort = 2
    Const cdoSMTPServer = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver"[/URL]
    Const cdoSMTPServerPort = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport"[/URL]
    Const cdoSMTPConnectionTimeout = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"[/URL]
    Const cdoSMTPAuthenticate = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"[/URL]
    Const cdoBasic = 1
    Const cdoSendUserName = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusername"[/URL]
    Const cdoSendPassword = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendpassword"[/URL]


    'Return reference to current database.
    Set dbs = CurrentDb
    'define recordsets
    Set rstemail = dbs.OpenRecordset("tblemail")
    Set rstserver = dbs.OpenRecordset("tblserverinfo")


    Set objmessage = CreateObject("CDO.Message")

    ' Set config fields we care about
    With objmessage.Configuration.Fields
        .Item(cdoSendUsingMethod) = 2
        .Item(cdoSMTPServer) = rstserver("smtpserver")
        .Item(cdoSMTPServerPort) = rstserver("portnumber")
        .Item(cdoSMTPAuthenticate) = cdoBasic
        .Item(cdoSendUserName) = rstserver("username")
        .Item(cdoSendPassword) = rstserver("password")
        .Update
    End With
    objmessage.Subject = rstemail("subject")
    objmessage.Sender = rstserver("emailaddress")
    objmessage.To = rstserver("emailtoaddress")
    objmessage.To = rstemail("recipient")


    objmessage.textbody = Space(30) & "Customer Orders" & vbTab & Format(Now(), "mm/dd/yyyy") & vbNewLine & String(90, "-") & vbNewLine & vbNewLine

    objmessage.textbody = objmessage.textbody & "Outstanding Orders" & String(50, "  ") & "Requested Ship Date"

    counter = 0
    Set rstOrders = dbs.OpenRecordset("Select * from tblcustomerorder where carl=true and shippingtoday=false and shippedyesterday=false order by customer")
    On Error GoTo nextstep1
    rstOrders.MoveFirst
    lastcompany = "-"
    Do While Not rstOrders.EOF
        If rstOrders("Customer") <> lastcompany Then
            objmessage.textbody = objmessage.textbody & vbNewLine & Space(15) & rstOrders("customer") & vbCrLf & Space(30) & rstOrders("product") & Space(25 - Len(rstOrders("product"))) & rstOrders("qty") & Space(15 - Len(rstOrders("qty"))) & rstOrders("reqshipdate") & vbNewLine
        Else: objmessage.textbody = vbNewLine & objmessage.textbody & Space(30) & rstOrders("product") & Space(25 - Len(rstOrders("product"))) & rstOrders("qty") & String(15 - Len(rstOrders("qty")), " ") & rstOrders("reqshipdate") & vbNewLine
        End If
        lastcompany = rstOrders("Customer")
        counter = counter + 1

        rstOrders.MoveNext
    Loop

    '******************************* ORDERS SHIPPED PREVIOUS WORKDAY *******************************************************
    
    objmessage.textbody = objmessage.textbody & vbNewLine
    objmessage.textbody = objmessage.textbody & vbNewLine & "Orders Shipped Previous Workday"
    Set rstOrders = dbs.OpenRecordset("Select * from tblcustomerorder where carl=true and shippedyesterday=true order by customer")
    rstOrders.MoveFirst
    lastcompany = "-"
    Do While Not rstOrders.EOF
        If rstOrders("Customer") <> lastcompany Then
            objmessage.textbody = objmessage.textbody & vbNewLine & String(15, " ") & rstOrders("customer") & vbCrLf & String(30, " ") & rstOrders("product") & String(25 - Len(RTrim(rstOrders("product"))), " ") & LTrim(rstOrders("qty")) & vbNewLine
        Else: objmessage.textbody = vbNewLine & objmessage.textbody & String(30, " ") & rstOrders("product") & String(25 - Len(RTrim(rstOrders("product"))), ".") & LTrim(rstOrders("qty")) & vbNewLine
        End If
        lastcompany = rstOrders("Customer")
        counter = counter + 1

        rstOrders.MoveNext
    Loop
    comments = InputBox("Any Comments")
    objmessage.textbody = objmessage.textbody & vbNewLine & "Comments: " & comments & vbNewLine & vbNewLine & rstemail("footer") & vbNewLine & vbNewLine
      MsgBox objmessage.textbody
    objmessage.send
    dbs.Close
    Set objmessage = Nothing
    Set dbs = Nothing
    Set rstOrders = Nothing
    Set rstemail = Nothing
    Set rstserver = Nothing
    MsgBox "Email has been sent"
End Sub

I have tried to use the space and string to generate the needed spacing. But either way I could not get the qty column to be left aligned.

I also tried tab but it did not help align the column. The column I am trying to align in the qty column since its horizontal location is based off the size of the product name

Any help would be appreciated.

ck1999

 

[Remou]

Is HTMLBody an option? If so, you could create a table in your code.

 

[Remou]

That is, "an option for you". HTML email do not always suit.

 

[ck1999]

I was trying to use a plain text email vs. html. Since I do not know if the end users use html emails.


ck1999

 

[Remou]

I have noticed companies using this format:

PAYMENT DETAILS
********99.99 EUR Things
*********9.99 EUR Other things

 

[JerryKlmns]

You could write the whole body message to a txt file and attach that to your CDO message!