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
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.
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 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
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?
