DoCmd.SendObject 1

[davman2002]

I am currently working on a project for work that will send an email to all employees with their statistics. although I have sucessfully been able to write the code that loops thru a table and sends an email to each person listed in a database. The problem I am having is that I would like to be able to format the output in columns. The current statement that I have is

While Not rst.EOF
DoCmd.SendObject acSendNoObject, , , rst("email&quot, , , , _
"Name" & Chr$(9) & "Generic_Percent" & Chr$(9) & "Formulary Percent" & Chr$(9) & "Generic Max" & _
Chr$(10) & rst("Name&quot & Chr$(9) & rst("Gen_Percent&quot & Chr$(9) & rst("For_percent&quot & Chr$(9) & _
rst("Gen_Max&quot, False
rst.MoveNext
Wend

Please help

 

[VBslammer]

One solution is to use fixed-length strings to hold your record data:

Sub SendMail()
  Dim strOutput  As String
  ' use fixed-length strings for output.
  Dim strName   As String * 20
  Dim strGenP   As String * 20
  Dim strFor    As String * 20
  Dim strGenMax As String * 20
  Dim strDiv    As String * 80
  
  ' header divider.
  strDiv = String(80, "-")

  While Not rst.EOF
    
    ' assign values to fixed length strings.
    strName = rst("Name")
    strGenP = rst("Gen_Percent")
    strFor = rst("For_percent")
    strGenMax = rst("Gen_Max")
    
    ' format the output string.
    strOutput = _
      "Name                " & _
      "Generic_Percent     " & _
      "Formulary Percent   " & _
      "Generic Max         " & vbCrLf & _
      strDiv & vbCrLf & _
      strName & _
      strGenP & _
      strFor & _
      strGenMax
    
    ' send mail.
    DoCmd.SendObject ObjectType:=acSendNoObject, _
                      To:=rst("email"), _
                      MessageText:=strOutput, _
                      EditMessage:=False
    
    rst.MoveNext
  Wend
End Sub

Sample MessageText output:

Name                Generic_Percent     Formulary Percent   Generic Max         
--------------------------------------------------------------------------------
Bill Gates          80                  45                  98

VBSlammer

Unemployed in Houston, Texas

 

[davman2002]

Thanks for your help!