Emailing Access Report in Email Body

I’ve been doing a fair bit of work in Access lately, which is why I’m so irritable. I like Access for it’s rapid development characteristics, but I have specific user interface requirements that don’t happen in Access out of the box. By the time I implement those quirks, I feel like I should have just written the damn thing from scratch in C#.

The quirk du jure is sending an Access report via email not as an attachment, but rather in the body of the email. I thought this would be pretty darn easy. I would display the report and the user would use some built-in send function under the File menu to send it off. Oddly, to send an Access object via email, you have to look on the External Data tab, not the File menu. The Export Email function does not have the option of putting the report in the body of the email, only as an attachment. At least as far as I can see. Off to VBA, I guess.

Private Sub cmdEmailBlended_Click()
    
    Dim sFile As String, lFile As Long, sHtml As String
    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
        
    UpdateBlendedQuery
    
    'Output the report to HTML in the temp directory
    sFile = Environ$("TEMP") & "\Blended" & Format(Date, "yyyymmdd") & ".html"
    DoCmd.OutputTo acOutputReport, "BlendedRackReport", acFormatHTML, sFile
    
    'Read in the HTML File
    lFile = FreeFile
    
    Open sFile For Input As lFile
        sHtml = Input$(LOF(lFile) - 1, lFile)
    Close lFile
    
    'Put the file contents in the email body
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = "nobody@example.com"
    olMail.Subject = "Special Pricing"
    olMail.HTMLBody = sHtml
    olMail.Display
    
End Sub

Private Sub cmdEmailBlended_Click()

Dim sFile As String, lFile As Long, sHtml As String

Dim olApp As Outlook.Application

Dim olMail As Outlook.MailItem

UpdateBlendedQuery

'Output the report to HTML in the temp directory

sFile = Environ$("TEMP") & "\Blended" & Format(Date, "yyyymmdd") & ".html"

DoCmd.OutputTo acOutputReport, "BlendedRackReport", acFormatHTML, sFile

'Read in the HTML File

lFile = FreeFile

Open sFile For Input As lFile

sHtml = Input$(LOF(lFile) - 1, lFile)

Close lFile

'Put the file contents in the email body

Set olApp = New Outlook.Application

Set olMail = olApp.CreateItem(olMailItem)

olMail.To = "nobody@example.com"

olMail.Subject = "Special Pricing"

olMail.HTMLBody = sHtml

olMail.Display

End Sub

Ah, the ubiquitous DoCmd object. I hate DoCmd (I told you I was irritable). If I was building the Access Object Model, that line would read

ActiveReport.Export sFile, acFormatHTML

1	ActiveReport.Export sFile, acFormatHTML

In Excel, I like to write my own HTML converters, but not so in Access. Taking an Excel Range and turning into an HTML table is one thing. But taking a Recordset and creating an HTML report out of it is nuts. There’s calculated fields, headers and footers, and a whole bunch of other considerations. So I have no problem using the built-in HTML converter, even though my 18 record report produces 199 lines of HTML (actually less than I expected).

On the first go ’round, I encountered a

Input past end of file

1	Input past end of file

error. You might notice on my Input$ statement that I take the length of the file minus 1. That’s not how I normally do it. In fact, I have an AutoHotKey for when I type Input

:*:Input$(::
	sendinput Input$(lof(lfile),lfile)
	Return

:*:Input$(::

sendinput Input$(lof(lfile),lfile)

Return

I couldn’t figure out why I was getting this error. I’m using LOF to get the length of the file, what more could you possibly want. I subtracted one just to see what would happen and it worked. Then I opened the file in Notepad++ to see if I could see what was happening.

Look at that little guy at the end. What’s that about?

8 thoughts on “Emailing Access Report in Email Body”

Dick –

First thought is of a “null terminated string” apropos to the C programming language. Apropos to Access? I dunno.

… mrt

Excel can’t make a CSV file to spec. Access can’t make an HTML file to spec. I’m starting to see a trend.

So the NULL is flipping back and forth between nothing with a length of 1 and the word “NULL”?

No, that’s just how Notepad++ shows nulls. If you open it in Notepad, it converts it to char 32.

I think you should write your own HTML converter. It’s worth it.

I’d like to share what I’ve done recently.
I am just redoing my website from scratch (WordPress) and couldn’t adept to WordPress online editor. I like to use the best text editor in the world to write, that is, Word for Windows. So I tought “maybe if I export in HTML, I can just copy and paste the HTML in the source of WordPress”. Hahahah. The HTML has tons of overhead, and just a document with a small paragraph generates hundreds of HTML lines, and it is totally incompatible with WordPress.
So, whenever I want to write a new blog post, I do everything in Word, run the macro to genereate the WordPress-compatible HTML and just paste it there.

What if you use:

Sub M_snb()
 DoCmd.TransferText acExportHTML, , "BlendedRackReport", "G:\blended.html"

 - - - - - - - 
htmlbody=application.createobject("scripting.filesystemobject").opentextfile("G:\blended.html").readall
  - - - - - - - -

End Sub

Sub M_snb()

DoCmd.TransferText acExportHTML, , "BlendedRackReport", "G:\blended.html"

- - - - - - -

htmlbody=application.createobject("scripting.filesystemobject").opentextfile("G:\blended.html").readall

- - - - - - - -

End Sub

Here is a simple but untested method that worked for me using Excel, but completely untested in Access. Simply try copy and paste (as picture) your object into the body. If that works you can use VBA send Keys to automate the process in VBA. I don’t know if this will work but it’s a quick test to check and simple to write the code if it does.