Export to email body in table format (1 Viewer)

Lennie · May 10, 2017

HI, need help please. I currently have a form with below code that puts records generated from a search into the body of an email. The problem with below code is the table with the record is not in proper tabular format. Can someone please let me know what i need to add to the code or if certain parts need to be re-arranged to get this. Many thanks for the help.

Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim olItem As Variant
Dim olMailItem As Variant
Dim olApp As Object

Dim sTable As String
sTable = "<HTML><Body><table border='2'"

Set olApp = CreateObject("outlook.application")
Set olItem = olApp.CreateItem(olMailItem)

olItem.To = "email address"
olItem.cc = "email address"
olItem.Subject = "Confirmation"
olItem.HTMLBody = "<html><font face=calibri>" & "Please confirm" & "<br>" & _
"<br>" & _
"or i will kick you" & "<br>" & _
"<br>"
olItem.Display

Set db = CurrentDb
Set rec = Me.RecordsetClone

With rec
If Not (rec.BOF And rec.EOF) Then
rec.MoveLast
rec.MoveFirst
intCount = rec.RecordCount
For intLoop = 1 To intCount
olItem.HTMLBody = olItem.HTMLBody & sTable & "<tr><td>" & rec("ContractNo") & "</td><td>" & rec("ClientName") & "</td><td>" & rec("RateSet Date") & "</td><td>" & rec("Pay/Rec") & "</td><td>" & rec("Currency") & "</td><td>" & Format(rec("Amount") & "</td></tr>", "$#,##0.00") & "<br>" & _
"<br>"
rec.MoveNext
Next intLoop

End If

End With

Set olApp = Nothing
Set olItem = Nothing

End Sub

June7 · May 10, 2017

Why is the Format function broken up by: & "</td></tr>" ?
Don't initiate sTable to the "<HTML><Body><table border='2'", don't want that repeated in the loop.
Don't include olItem.HTMLBody in the loop.
Need "</table></font></Body></HTML>" closing tags.
Should post code between CODE tags.

Code:

With rec
If Not (.BOF And .EOF) Then
    .MoveLast
    .MoveFirst
    intCount = .RecordCount
    For intLoop = 1 To intCount
        sTable = sTable & "<tr><td>" & !ContractNo & "</td><td>" & _
            !ClientName & "</td><td>" & ![RateSet Date] & "</td><td>" & _
            ![Pay/Rec] & "</td><td>" & !Currency & "</td><td>" & _
            Format(!Amount, "$#,##0.00") & "</td></tr>"
        .MoveNext
    Next intLoop
End If
End With
olItem.To = "email address"
olItem.cc = "email address"
olItem.Subject = "Confirmation"
olItem.HTMLBody = "<HTML><Body><font face='calibri'>Please confirm or I will kick you<table border='2'>" & _
    sTable & _
    "</table></font></Body></HTML>"
olItem.Display

Can't get the font tag to apply to the table in the email although it works in an html text file.

Advise no spaces or punctuation/special characters (underscore only exception) in any naming convention.

Export to email body in table format (1 Viewer)

Lennie

Registered User.

June7

AWF VIP

Similar threads

Users who are viewing this thread