Automate Emailing a Query in Excel Format without Truncating Memo Fields

[ctpoodle]

I am working with a database that's been in use for years (pre-dates me). This is a multi-user database in which the user updates (among other things) a "Comments" field on an invoice in question, which is a memo field to allow for as much room as possible, and for multiple comments over time.

On a daily basis, there are queries run, several including this Comments field, which are automated to be emailed out (via Outlook) to various people within our organization. This had been done by way of a macro and the EmailDatabaseObject command. The problem with this is that it truncates the memo field by the time it gets to Excel.

I've searched the forum and seen one solution was to Export and then copy and paste, but that doesn't help me in terms of automation. Can anyone help me? I tried just doing a simple conversion to VBA:

DoCmd.SendObject acQuery, "SENIORS DAILY REPORT", "Excel97-Excel2003Workbook(*.xls)", person@company.com, "", "", "SENIORS DAILY ESCALATION REPORT", "Good morning. Attached please find the Seniors Daily Report containing escalations for your review. Please be sure to prioritize the >30 day items. Thank you for your help.",, ""

But I'm getting the same results in terms of truncating.

Thank you for your help!

Lisa

 

[The_Doc_Man]

The difficulty is that normally, EXCEL truncates text fields. The "Export" function will convert MEMO fields into TEXT (255) fields. I don't know that I've ever gotten a spreadsheet to take more than 255 characters in a single sell anyway.

 

[ctpoodle]

I don't know that I've ever gotten a spreadsheet to take more than 255 characters in a single sell anyway.

Thank you for responding The_Doc_Man. I did try just exporting from Access into Excel and was successful in keeping all the characters, as long as it was a 97-2003 format. Anything newer than that, and it doesn't.

 

[Frothingslosh]

I just did some quick testing, and Excel 2003 will display up to 1024 characters per cell. (It can hold 32,767 per cell.) The testing was pretty simple - I filled a cell with 1023 of the letter a, then 2 of the letter b. I resized the cell to fit, and then looked at the end. It showed one b, but not both.

So your problem isn't with Excel itself, but rather the import/export. It's as if your export is trying to use the Excel 5.0/95 format, which DOES have a 255 character limit.

Let's try a different approach. Have you considered exporting the recordset to an Excel file using CopyFromRecordset, and then emailing the resulting Excel file?

Thank you for responding The_Doc_Man. I did try just exporting from Access into Excel and was successful in keeping all the characters, as long as it was a 97-2003 format. Anything newer than that, and it doesn't.

That's odd. I export larger data than that in Office 2013 pretty regularly.

 

[ctpoodle]

Frothingslosh you are awesome for going above and beyond and counting the number of visible characters! That is very interesting and definitely presents some food for thought. As for my results with Office 2013 vs your results, I'm not sure why I got something different, but I will definitely try again.

Have you considered exporting the recordset to an Excel file using CopyFromRecordset, and then emailing the resulting Excel file?

The hope was to keep this completely automated, especially as there are a number of reports that go out this way. Yes, each one could be exported and then someone could email the resulting file. However, we're trying to reduce the amount of manual tasks here. What was nice was that these were all layered into one morning process. One press of a button and about 40 macros and functions run in a matter of minutes.

 

[Frothingslosh]

Oh, there's nothing saying you can't keep it automated. You'll need to learn Automation, but it's completely possible to generate a series of recordsets, drop them into one or more Excel files, and then email those files to a pre-designated mailing list, all with the click of a single button. (In fact, we have a couple Access databases rattling around my workplace that do precisely that.)

One step at a time, though. First figure out a way to ENSURE your export works as planned. Then we can show you how to set up the emails - it's pretty easy once you get the hang of it.

 

[ctpoodle]

I'm running a couple of tests now and will keep you posted! Thank you so much for your help!

 

[ctpoodle]

I went back and tested just having the queries send in .xlsx (Excel 2013) format. Just that simple change worked! The only reason I can think of that I had problems before is that our whole company JUST switched over to Windows 7 and Office 2013 and we were having some glitches company-wide. I feel like such a dolt for not figuring this out sooner!

Thank you for your help! I have to admit, I am very curious about the Automation involved in emailing the Excel files. But for now, this simple fix did the trick!

 

[Frothingslosh]

Okay, Automation is just the way you run other Office programs from one another. As one example, you could use Access to pull some data from an Excel spreadsheet, create a number of PowerPoint slides, generate a summary document in Word, email the created files via Outlook, and finally add a log entry to another spreadsheet.

For emails, this is my most recent version of an Outlook automation routine. It was written for a specific purpose rather than being general-purpose, so it has a few limitations such as only accepting one attachment, but those can all be modified.

I would recommend going through it and seeing if you can figure out how and why I did each step. (I over-comment, so what is generally a non-issue!) Feel free to ask any questions you may have.

Public Function SendOutlookEmail(ByVal strRecipient As String, _
                                 ByVal strSubject As String, _
                                 ByVal strBody As String, _
                                 Optional strCC As String, _
                                 Optional strBC As String, _
                                 Optional strAttachment As String) As Boolean
[COLOR="SeaGreen"]' ************************************************************
' Created by:       Scott L. Prince
' Parameters:       Semicolon-delimited list of email recipients
'                   String containing the email subject line
'                   String containing the body of the email
'                   Optional semicolon-delimited list of CC recipients
'                   Optional semicolon-delimited list of BCC recipients
'                   Optional full path to any file to be attached
' Results:          Sends email to listed recipients using Outlook
' Returns:          Success or failure
' Date:             10-13-2015
' Remarks:          In order to use this function, go to Tools -> References and enable 'Microsoft Outlook
'                   XX Object Library', with the version number replacing XX (there should be only one visible).
' Changes:
' ************************************************************[/COLOR]

On Error GoTo SendOutlookEmail_Err

Dim oOutlook As Outlook.Application
Dim oOutlookMsg As Outlook.MailItem

    [COLOR="seagreen"]'Defaults[/COLOR]
    SendOutlookEmail = False
    DoCmd.Hourglass True
    
    [COLOR="seagreen"]'Assign the running instance of Outlook to oOutlook.[/COLOR]
    Set oOutlook = GetObject(, "Outlook.Application")
    
[COLOR="seagreen"]    'Have any pending events execute.[/COLOR]
    DoEvents
    
[COLOR="seagreen"]    'Create the email.[/COLOR]
    Set oOutlookMsg = oOutlook.CreateItem(olMailItem)
    
    With oOutlookMsg
[COLOR="seagreen"]        'Assign email recipients.[/COLOR]
        .To = strRecipient
[COLOR="seagreen"]        'Assign CC recipients.[/COLOR]
        If strCC <> "" Then .CC = strCC
[COLOR="seagreen"]        'Assign BCC recipients.[/COLOR]
        If strBC <> "" Then .BCC = strBC
[COLOR="seagreen"]        'Assign subject text.[/COLOR]
        .Subject = strSubject
[COLOR="seagreen"]        'Include the body text.[/COLOR]
        .Body = strBody & vbCrLf & vbCrLf
[COLOR="seagreen"]        'Add attachment if any.[/COLOR]
        If strAttachment <> "" Then .Attachments.Add strAttachment
[COLOR="seagreen"]        'Send the message.[/COLOR]
        .Send
    End With
    
[COLOR="seagreen"]    'Run any pending events.[/COLOR]
    DoEvents
    
[COLOR="seagreen"]    'Return a TRUE value indicating success.[/COLOR]
    SendOutlookEmail = True
    
SendOutlookEmail_Exit:
    On Error Resume Next
    DoCmd.Hourglass False
    If Not oOutlookMsg Is Nothing Then Set oOutlookMsg = Nothing
    If Not oOutlook Is Nothing Then Set oOutlook = Nothing
    Exit Function
    
SendOutlookEmail_Err:
    DoCmd.Hourglass False
    MsgBox "Error occurred" & vbCrLf & vbCrLf & _
    "In Function:" & vbTab & "SendOutlookEmail" & vbCrLf & _
    "Err Number: " & vbTab & Err.Number & vbCrLf & _
    "Description: " & vbTab & Err.Description, vbCritical
    Resume SendOutlookEmail_Exit

End Function

 

[ctpoodle]

This is awesome! Thank you! I'll try to figure it out! Thanks so much!!