Emailing 8 – Convert Report To HTML

In this post, we will be demonstrating how to use a report as the basis for the text in the body of an email.

Db Emailing 08

The Code

There are some significant changes in this code so please pay attention to all sections.

Public Function convertReportToHTMLAndSend(strSQL As String, strEmailField As String, strReport As String, _
                                    strSubject As String, Optional lngID As Long = -1)
On Error GoTo ErrorHandler
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''      1        ''''''''''''''''''''
    ''''''''''''''''''' Declarations '''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim olApp As Object
    Dim olMail As Outlook.MailItem
    Dim olRecipient As Outlook.Recipient
    Dim olAttachment As Outlook.Attachment
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strPath As String
    Dim strFileName As String
    Dim strSendName As String
    Dim strHtml As String
    Dim strLine As String
    Dim strWhere As String
    
    ' Create the Outlook session.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'if outlook is open it will need to be run as administrator
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
  
    ' Create the message.
    Set olMail = olApp.CreateItem(olMailItem)
    
    'path where html file will be written
    strPath = "C:\AccessTemp"
    strFileName = "File"
    strSendName = "FileToSend"
    
    On Error Resume Next
        MkDir strPath
    On Error GoTo ErrorHandler
    
      
    'print to html
    strWhere = ""
    If lngID <> -1 Then
        strWhere = "[ID]=" & lngID
    End If
    
    Application.Echo False
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
    DoCmd.OutputTo acOutputReport, "", acFormatHTML, strPath & "\" & strSendName & ".html", False
    DoCmd.Close acReport, strReport
    Application.Echo True
    
    
    Open strPath & "\" & strSendName & ".html" For Input As 1
    Do While Not EOF(1)
        Input #1, strLine
        strHtml = strHtml & strLine
    Loop
    
    Close 1    
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''      2        ''''''''''''''''''''
    ''''''''''''''''''' Recipients '''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
     
    'if strSQL is not a select statement then add the where clause
    If InStr(strSQL, "SELECT") = 0 Then
        strSQL = "SELECT * FROM " & strSQL & " WHERE " & strWhere
    End If
     
     
      
    'open up a recordset and move through it
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    
    With rs
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            
            While (Not .EOF)
                ' Add the To recipient(s) to the message.
                Set olRecipient = olMail.Recipients.Add(.Fields(strEmailField))
                olRecipient.Type = olTo
                .MoveNext
            Wend
        End If
    End With
   
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''      3        ''''''''''''''''''''
    ''''''''''''''''''''' Message'''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    With olMail
         ' Set the Subject, Body, and Importance of the message.
         .Subject = strSubject
         .HTMLBody = strHtml
         .Importance = olImportanceHigh  'High importance
             
    
         ' Resolve each Recipient's name.
         For Each olRecipient In .Recipients
             olRecipient.Resolve
         Next
                 
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''
        '''''''''''''''''''      4        ''''''''''''''''''''
        ''''''''''''''' Display, Save, Send'''''''''''''''''''
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
        .Display

      End With

convertReportToHTMLAndSend = True

ExitFunction:
    Set olRecipient = Nothing
    Set olMail = Nothing
    Set olApp = Nothing
    Application.Echo True
    Exit Function
ErrorHandler:
    convertReportToHTMLAndSend = False
    Resume ExitFunction
End Function

The Explanation

Public Function convertReportToHTMLAndSend(strSQL As String, strEmailField As String, strReport As String, _
                                    strSubject As String, Optional lngID As Long = -1)
...
End Function

strReport is the name of the report that we will be reading from.

lngID is the ID that we will be using to filter the report.

Note: Don’t worry if you are not sure as to what we are doing. It will become clear as you read on.

    ...
    Dim strHtml As String
    Dim strLine As String
    Dim strWhere As String
    ...

Pay attention to these extra declarations that we will be using.

    'path where html file will be written
    strPath = "C:\AccessTemp"
    strFileName = "File"
    strSendName = "FileToSend"
    
    On Error Resume Next
        MkDir strPath
    On Error GoTo ErrorHandler

In the previous post, we used a similar concept to save a report as a pdf to the local computer before attaching it to an email. In this post, we will need to save the report to the local computer in order to read it into the email. The difference here is that we will be saving the report as an html file rather than a pdf (code below).

   'print to html
    strWhere = ""
    If lngID <> -1 Then
        strWhere = "[ID]=" & lngID
    End If

The lngID argument is optional so we don’t necessarily have to pass it in. If lngID has been passed, it will not equal -1 and if it does not equal -1, we construct a simple SQL statement (“[ID]=2”).

    Application.Echo False
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
    DoCmd.OutputTo acOutputReport, "", acFormatHTML, strPath & "\" & strSendName & ".html", False
    DoCmd.Close acReport, strReport
    Application.Echo True

As mentioned above, we are looking to save the report to the local computer as an html document. We need to open it up but we don’t need to see it open so we use…

Application.Echo False

We open the report and use the where clause that we created…

DoCmd.OpenReport strReport, acViewPreview, , strWhere

…and we save the report to the local computer as an html document…

DoCmd.OutputTo acOutputReport, "", acFormatHTML, strPath & "\" & strSendName & ".html", False

…finally, we close the report…

DoCmd.Close acReport, strReport

…and turn visuals on…

Application.Echo True

…just like that!

    Open strPath & "\" & strSendName & ".html" For Input As 1
    Do While Not EOF(1)
        Input #1, strLine
        strHtml = strHtml & strLine
    Loop

    Close 1

Now we need to open the newly created report (html document) and read all of its contents into strHtml. The code above may seem a little confusing but the beauty of code is that often you don’t need to know how the code works, just that it does and how to utilise it. Fundamentally, you pass the code above the full path of a file like this…

    Open strPath & "\" & strSendName & ".html" For Input As 1

…(strPath & “\” & strSendName & “.html” is the file path) and then print the contents of that file to a string variable (in this case strHtml)…

    Do While Not EOF(1)
        Input #1, strLine
        strHtml = strHtml & strLine
    Loop

…like that. All we need to know, right?

     With olMail
         ' Set the Subject, Body, and Importance of the message.
         .Subject = strSubject
         .HTMLBody = strHtml
         .Importance = olImportanceHigh  'High importance

So, strHtml contains the text of the message. We just need to assign it to the Body property of olMail. As the text is in html format we will use HTMLBody, rather than Body.

Testing The Function

As we are writing a letter regarding student’s absence from college, we have changed the table from Customers to Students.

The form looks like this:

Note: We have used a combo-box for the student name. Remember with combo-boxes, we see (in this case) a name but the combo-box stores a number.

The result of clicking Test is:

The Conclusion

In this post, we have demonstrated how to take the text from an existing report and use it as the message of an email.

In the next post, we will be giving you a script that you can use with Lotus notes instead of Outlook.