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.

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


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

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

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

  1. Dick –

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

    … mrt

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

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

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

  5. What if you use:

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

  7. Your code helped me tremendously. I needed a way to send an access report in the body of the email.

    It worked flawlessly. The only issue is that the report is pasted in body with no vertical spacing, but that is not by any means a problem for me.

    Much appreciated. Lucky to have found your blog.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.