Results 1 to 8 of 8
  1. #1
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87

    Question HTML Table in Outlook - empty cells not appearing correctly

    Hello All!

    I have a new question, and you all were so very helpful with my last set of questions a few months ago.

    I run a day camp. Each unit has around 15 people, though a few have much fewer and one has many more.

    I usually send an e-mail to the unit leaders with their unit information, including contact information, in a table with-in the e-mail.

    I would dearly love to be able to automate this.

    I have been playing around and i have a pretty good start, but I'm running into a few issues, not the least of which is that if a field should be empty (i.e. adults don't have a parent/guardian and campers don't have a unit position), then the information is copied from the table row above it. When I create multiple e-mails, the fields copy from the e-mail created before it!

    Example: a unit has three people, Sarah Smith, unit leader, smith@aol.com, adult #1 {blank}, cell #1 is 123-456-7896; Holly Hobby, {no position, she's a kid}, hobby@att.net, Amanda Hobby, 456-789-1234; Emily Evans, {no position}, evans@orb.com, Tracey Evans, 789-123-4567

    The table should look like:
    Name Position Adult #1 cell #1
    Sarah Smith UL 123-456-7896
    Holly Hobby Amanda Hobby 456-789-1234
    Emily Evans Tracey Evans 789-123-4567

    Instead I get:
    Name Position Adult #1 cell #1
    Sarah Smith UL ** 123-456-7896 **if there was another e-mail before this one, then this would be the parent from the last record
    Holly Hobby UL Amanda Hobby 456-789-1234
    Emily Evans UL Tracey Evans 789-123-4567

    So .... how do I keep the blank fields blank?

    My code is: OH! and I forgot to mention, I must use late binding ... hence the "objects."



    Code:
    Dim objOutlook As Object
    Dim objEmailItem As Object
    Dim dbs As DAO.Database
    Dim strPath As String
    Dim NewFileName As String
    Dim UnitName As String
    Dim ULName As String
    Dim ULemail As String
    Dim BodyText As String               'this is typed into a form
    Dim SubjText As String                'this is typed into a form
    Dim varItem As Variant
    Dim lst As Access.ListBox
    Dim tblRow(1 To 9) As String
    Dim tblHeader(1 To 9) As String
    Dim tblBody() As String
    Dim LineCnt As Long
    Dim rstTable As DAO.Recordset
    Dim strQry As String
    
     Set lst = Me.lstULs
    
    'Check to make sure at least one unit is selected
    If lst.ItemsSelected.Count = 0 Then
        MsgBox "No units selected. Please select a unit.", vbOKOnly
    Exit Sub
    End If
    
    'prevent 429 error if Outlook is not open
    On Error Resume Next
        Err.Clear
        'See if Outlook is open
        Set objOutlook = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then
        'Should open Outlook
        Set objOutlook = CreateObject("Outlook.Application")
    End If
            
    BodyText = Me.txtEmailBody
    SubjText = Me.txtSubjLine
    
    
    ' olMailItem is the Outlook Application's constant,
    ' therefore define it here explicitly
    Const olMailItem As Long = 0
    
    
    
        For Each varItem In lst.ItemsSelected
            
            'Build the table of unit info
            'Create Header Row
            tblHeader(1) = "First"
            tblHeader(2) = "Last"
            tblHeader(3) = "Unit Position"             'campers don't have a unit position
            tblHeader(4) = "Days"                        'Days attending
            tblHeader(5) = "E-mail"
            tblHeader(6) = "Adult #1"                  'adults don't have an adult #1
            tblHeader(7) = "Cell #1"                    'everyone has a cell #1
            tblHeader(8) = "Adult #2"                  'adults and some campers don't have an adult #2
            tblHeader(9) = "Cell #2"                    'adults and some campers don't have a cell #2
            
            'set up count of body rows
            LineCnt = 1
            ReDim tblBody(1 To LineCnt)
            tblBody(LineCnt) = "<HTML><body><table border='1'><tr><th>" & Join(tblHeader, "</th><th>") & "</th></tr>"
    
    
            'Create each table row
            strQry = "SELECT [qry Roster].FirstName, [qry Roster].LastName, [qry Roster].PositionName, [qry Roster].Days, " & _
                     "[qry Roster].email, [qry Roster].ParentGuard1, [qry Roster].cellnumber, [qry Roster].parentguard2, " & _
                     "[qry Roster].Cell2 FROM [qry Roster] WHERE ((([qry Roster].UnitID) = " & lst.ItemData(varItem) & ")) " & _
                     "ORDER BY [qry Roster].UnitID, [qry Roster].Type, [qry Roster].Position, [qry Roster].LastName, " & _
                     "[qry Roster].FirstName;"
    
    
            Set dbs = CurrentDb
            Set rstTable = dbs.OpenRecordset(strQry, dbOpenDynaset, dbReadOnly)
            
                If Not (rstTable.BOF And rstTable.EOF) Then
                    Do While Not rstTable.EOF
                        LineCnt = LineCnt + 1
                        ReDim Preserve tblBody(1 To LineCnt)
                        tblRow(1) = rstTable("FirstName")
                        tblRow(2) = rstTable("LastName")
                        tblRow(3) = rstTable("PositionName")
                        tblRow(4) = rstTable("Days")
                        tblRow(5) = rstTable("Email")
                        tblRow(6) = rstTable("ParentGuard1")
                        tblRow(7) = rstTable("cellnumber")
                        tblRow(8) = rstTable("ParentGuard2")
                        tblRow(9) = rstTable("cell2")
                        tblBody(LineCnt) = "<tr><td>" & Join(tblRow, "<td>")   'I originally had /td and /tr end tags, but the tags appeared in the actual table, so I took them out
    
    
                        rstTable.MoveNext
                    Loop
                End If
                
              tblBody(LineCnt) = tblBody(LineCnt) & "</table></body></html>"
              
    
    
            'Build e-mail message
            
            'pull UL FirstName
            ULName = DLookup("FirstName", "qry UL List", "UnitID=" & lst.ItemData(varItem))
            'pull UL e-mail address
            ULemail = DLookup("email", "qry UL List", "UnitID=" & lst.ItemData(varItem))
            
            Set objEmailItem = objOutlook.CreateItem(olMailItem)
        
                With objEmailItem
                    'Who the e-mail is to ...   use .CC for carbon copy and .bcc for blind carbon copy
                    .to = ULemail
                    'What the subject line is
                    .Subject = SubjText
                    'using HTML so the font and size can be changed. <br> is a carriage return
                    'original htmlbody
                    'eventually I want the table to be in between the BodyText and my signature 
    '                .htmlbody = "<BODY style=font-size:12pt;font-family:Calibri>" & ULName & ",<br><br>" & _
    '                            BodyText & "<br><br>Susie Gilson<br>Girl Scouts<br>" & _
    '                            "Day Camp Business Manager" & _
    '                            "<br>SU 678<br>email@att.net</BODY>"
                    'html table test
                    .htmlbody = Join(tblBody, vbNewLine)
                    'Display the e-mail rather than sending it
                    .Display
        
                End With
        
            'have the e-mail item be visible
            objOutlook.visble = True
        
            Set objEmailItem = Nothing
            rstTable.Close
            Set rstTable = Nothing
        
        'Move to next item in the list
        Next
    The building of the e-mail itself ... finding the leader's name, e-mail and doing the original HTML body work just fine.


    Thank you!

    Susie
    A weary Girl Scout in Kansas

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Possible issue is declaring arrays as String type. String cannot hold Null, only Variant can. Declare as Variant.

    Arrays default to 0 for index base unless use Option Base 1 in module header.

    Not sure arrays even needed. Just concatenate data and literal text HTML tags.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    I changed the three arrays to variants (tblRow, tblHeader, and tblBody) and the column headers showed up but nothing else did. I tried each array in turn and the the only one that could be a variant was the tblHeader.

    How would I do it by concatenating the data? I can't wrap my head around how to cycle through the different number of records in each unit and then the fields ...

    Thank you for your help.

    Susie

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    In the recordset loop like:
    Code:
    strTable = strTable & "<tr><td>" & _
               rstTable!FirstName & "</td><td>" & _
               rstTable!LastName & "</td></tr>"
    Be sure to set the strTable variable back to empty string for next listbox item.

    The header row is all literal text. No array needed. Just type one long string.

    Can eliminate all the array code.

    If you would like to set table font and highlight header:
    Code:
    "<table border='1' style='font-family:calibri'><tr style='background:yellow;mso-highlight:yellow'>"
    Last edited by June7; 06-17-2018 at 02:43 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    June7,

    THANK YOU! With some tweaking on my part ... um, I *may* have mis-typed a few things and had to debug ... I get exactly what I want!

    Now, of course, I want to do a bit more.

    I have done a bunch of googling on HTML tags and tables ... trying to learn the correct formatting and syntax.

    I see that I could use border-collapse to eliminate the space between the cell borders. And that I could use cell padding to add some space around the cell contents. Also, my borders are gray and I'd like them to be black. I'd also like to left align the headings.

    I have found:

    text-align: left
    padding: 15px
    border: 1px solid black
    border-collapse: collapse


    but I can't figure how to put it in my code! I have tried with an equals sign, with a colon, with a single quote mark around it, without the single quote mark. Any ideas?

    Here is my header code:

    Code:
    tblHeader = "<HTML><table border='1' style='font-size:12pt;font-family:Calibri'>" & _
                      "<tr><th>First</th><th>Last</th><th>Position</th><th>Days</th>" & _
                      "<th>E-mail</th><th>Adult #1</th><th>Cell #1</th><th>Adult #2</th>" & _
                      "<th>Cell #2</th>"

    Susie

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Those are CSS style codes.

    <table style='text-align:left;border:1px solid black;font-family:calibri;border-collapse:collapse;padding:15px'>

    Text-align only works on data rows not header and left is default. Black is also default. At least i get those effects even if I don't use those codes.

    Visit https://www.w3schools.com/html/html_styles.asp
    Last edited by June7; 06-19-2018 at 10:11 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    In addition to June's excellent advice, make sure all your values are not null values use something like NZ(YourField,"") as they tend to mess with the HTML formatting.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    Thank You June7 and Minty!

    My e-mails now look exactly like I'd envisioned! I am SO excited!

    Susie
    Girl Scout
    Kansas

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Hiding Empty Cells in Form
    By jaystewart86 in forum Forms
    Replies: 6
    Last Post: 01-20-2018, 08:53 PM
  2. Replies: 12
    Last Post: 08-03-2017, 03:29 PM
  3. Replies: 7
    Last Post: 07-15-2015, 03:42 PM
  4. Replies: 6
    Last Post: 05-07-2014, 11:15 AM
  5. Replies: 1
    Last Post: 04-15-2014, 02:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums