Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273

    Export to Excel, format and e-mail

    Hello again my friendly geniuses, I'm hoping you can help me again with another multi-part problem.


    I want to export the results of a query into Excel 2007, then e-mail the spreadsheet. Here's what I have so far...

    Private Sub cmdSearch_Click()


    DoCmd.OutputTo acOutputQuery, "qryPartsArrival", acFormatXLSX, "C:\Documents and Settings\All Users\Desktop\Parts_Arrival.xlsx", False
    End Sub

    Of course this just sends it to my desktop to make sure that it works. This part is working fine as far as all of the data is showing up, but I would like to be able to adjust one of the column sizes if possible.

    When the user clicks the command button, I would like the Excel sheet to be on an open MS Outlook e-mail page as an attachment. There would be no name in the "Send To" box (the user will select that manually). The "Subject" box would say "Parts are in", and nothing in the body of the e-mail. Then they would click "Send" and be done. No copies to delete from their desktop afterwards.

    I've been looking for this answer in the forums, but haven't quite stumbled on the right solution yet. Thank you for your help, and I'll keep looking in the mean time.

  2. #2
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Hi,

    Just so we don't end up talking at cross purposes let me reiterate what I think you want.

    1. You want to export the result set of a query to a named directory in Excel compatible format. (This is working.)
    2. You want to edit the Excel spreadsheet. (Manually - I hope - or through code?)
    3. You want to use Outlook to email the spreadsheet as an attachment.


    OK, I'm glad that 1 is working because I'm not familiar with DoCmd.OutputTo. For 2 it is possible to open an Excel session (or use the existing open session) for the spreadsheet from within Access. You could make this automatically follow the export or design it to be a separately invoked step. For 3, DoCmd.SendObject springs to mind but I don't think it's applicable for you because you want to send an object from outside the Access domain (i.e. the edited spreadsheet residing in a directory). By all means read up on SendObject. This leaves Office Automation as the solution. You can open a dedicated Outlook session or use the existing open session, compose the email (manually or code-assisted) and send the email. All this is done with Access being the driver/controller of the process.

    You may find someone in this forum has developed an Access-Outlook emailing object. If so a lot of the tedious hard work is done.

    Anyway shall we start with no. 2 above? Best if you publish the procedure that contains your DoCmd.OutputTo statement.
    Last edited by Rod; 08-11-2012 at 12:02 AM. Reason: Grammer!

  3. #3
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    For now, I can live without editing the (column width) spreadsheet. When the user clicks the button on the main menu to "Send Notification", the next thing he should see is an open e-mail page (with the spreadsheet as an attachment) ready to be sent (minus the name of who it's going to). If I can get this part working, I'm calling it a week! They'll be ok with a column that's a little out of whack.

  4. #4
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    OK, get back to you soonest. If the 'editing' is simply using Excel's Autofit and you know which columns need adjusting, then it can be done 'behind-the-scenes.'

    Look for a reply within 4 hours.

  5. #5
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Here we go - that took no time at all because I found a Microsoft site where all the hard work has been done. Plagiarise - that's the name of the game ("Let no one else's work escape your eyes ... but please to always call it research." Tom Lehrer)

    Two things to do:

    • From the VBA coding window click on Tools in the menu bar and then select References. Scroll down until you find an entry for Microsoft Outlook. Click the checkbox and then OK.


    • Copy and paste the following procedure into your own db. (Indentation has gone crazy no no matter.)


    Code:
    Sub NewMailMessage(Optional varSubject As Variant, _
                          Optional varAttachment As Variant)
        Dim objOutlook As Outlook.Application
        Dim objOutlookMsg As Outlook.MailItem
        Dim objOutlookAttach As Outlook.Attachment
        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")
        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        With objOutlookMsg
            ' Set the Subject of the message.
            If Not IsMissing(varSubject) Then
                .Subject = CStr(varSubject)
            End If
            ' Add attachments to the message.
            If Not IsMissing(varAttachment) Then
                Set objOutlookAttach = .Attachments.Add(varAttachment)
            End If
       End With
       
       objOutlookMsg.Display
       
       Set objOutlookMsg = Nothing
       Set objOutlook = Nothing
       
    End Sub
    I would suggest you place it in a module - it just makes things a little clearer. You may then use it from within your own db by:

    Code:
    <modulename>.NewMailMessage varSubject:="Once upon a time.", varAttachment:="C:\Users\Rod\Gnomes.xlsx"
    Replace the blue terms with your own names.

    Notes:

    • This opens a new Outlook session ignoring any other sessions that might be open at the time.
    • There is no validation of the arguments. Both are optional so you may pass none, one or both.
    • There is no error handling. Beware if a run-time error occurs it is likely to leave a hidden Outlook session running. Solve this by rebooting.


    Get back to me if you are interested in refining the process.


    PS. Does it create a new session? I'm now no longer sure and can't prove it either way.

  6. #6
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    Thanks so much Rod, it works great. It does not leave Outlook open after the message is sent, however it does leave a copy of the spreadsheet (icon) on my desktop (exactly like I asked it to do). Is there a way to keep the spreadsheet around while it opens, populates & sends, then disappear? I would hate for the users desktop to be filled with icons at the end of the day from sending multiple messages. Also, I think the autofit for the column width you mentioned in the previous post may do the trick if you can explain that a bit.

  7. #7
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    First: the icons. They persist because Outlook sends a copy of the spreadsheet and leaves the original behind. Now Access Scripting Runtime has a file delete function; I shall experiment to see whether this is powerful enough to delete the spreadsheet file, otherwise it will be necessary to use an API.

    You could, of course, always export the spreadsheet to a directory other than Desktop. Then you don't get the icons in the first place - but then maybe you want the icon 'just in case ...'

    Second: the columns. I need to know more about the infrastructure of the spreadsheet.

    • Does it contain only a single worksheet?
    • Which columns need autofit?
    • Are they always the same columns?
    • Do you want to do this hidden or 'hands-off watch what's happening?'


    I think you now need to put some error handling into the code; if a run-time error occurs when using Automation then it can sometimes screw up the system very badly.

    Right, I'm off for a couple of beers right now, so don't expect the same lightning service as before.

  8. #8
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    OK, here's an interim update. This deletes the spreadsheet from the desktop (and thus also the icon). I wanted to construct a prototype and test it before getting back to you as I had some concerns, susequently unfounded. Scripting Runtime is 'powerful' enough to delete the spreadsheet, at least for me with Administrator privileges; I was concerned that there may be issues with ownership and/or security. Further I have confirmed that it is possible to delete the source spreadsheet before the email is sent. I knew this, but seeing is believing.

    Again two things to do:

    • From the VBA coding window click on Tools in the menu bar and then select References. Scroll down until you find an entry for Microsoft WMI Scripting V 1.2 Library (or similar). Click the checkbox and then OK.



    • Copy and modify the following code into an appropriate place in your db.



    Code:
    Private Sub Command10_Click()
        Dim fso As Scripting.FileSystemObject
        Dim strFilePath As String
        strFilePath = "C:\Users\Rod\Desktop\Test.xlsx"
        DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLSX, strFilePath
        basOutlook.NewMailMessage , strFilePath
        Set fso = New Scripting.FileSystemObject
        fso.DeleteFile strFilePath
        Set fso = Nothing
    End Sub
    Notes:

    • I have introduced a string variable, strFilePath for the purpose of testing. How you get your file path is up to you; you could pass it as an argument.
    • basOutlook is where my copy of the procedure resides; use your own name.
    • This procedure is seamless in that it does not pause before deleting the spreadsheet. You could separate out the 'fso' bits into a new procedure if you want to split the process into two.
    • When used with the same file name, DoCmd.OutputTo simply overwrites the previous file with no warning whatsoever! You might use this feature such that there is only ever one icon on the desktop, the latest spreadsheet, and forget about deleting anything.



    I'll send you the Excel editing code as the next installment.

  9. #9
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    I've added the Microsoft WMI Scripting V 1.2 Library to the references, but I'm getting an error at Dim fso As Scripting.FileSystemObject (Compile error User-defined type not defined). Not sure if it's how I was supposed to do it, but I put it in my code so it looks like this

    Private Sub cmdSearch_Click()

    Dim fso As Scripting.FileSystemObject
    Dim strFilePath As String
    strFilePath = "C:\Documents and Settings\All Users\Desktop\Parts_Arrival.xlsx"
    DoCmd.OutputTo acOutputQuery, "qryPartsArrival", acFormatXLSX, strFilePath, False
    basOutlook.NewMailMessage , strFilePath
    Set fso = New Scripting.FileSystemObject
    fso.DeleteFile strFilePath
    Set fso = Nothing


    NewMailMessage.NewMailMessage varSubject:="Parts have arrived.", varAttachment:=strFilePath
    End Sub

    I didn't want to save a copy to the desktop in the final version, I just didn't know what else to do with it while I'm working on it. I've seen in other peoples code that they work on a spreadsheet as a .temp file instead of saving it to a path on the computer, like it never really exists except in the e-mail. That would be great, I just don't know how to do it.

    Here's the spreadsheet info;
    There is only one sheet.
    The only column that really needs to be adjusted (Column H) has alot of words, so it spread itself to 4 lines, thus making the rows 60.00 high instead of 12.75 like they are below the data. All of the columns could be shrunk a bit to make reading easier without so much scrolling, but I guess it's all semantics.
    If the spreadsheet can be cleaned up, it would be best if the user only saw it in its clean form when they open it, so I guess a behind the scenes would be best.

    Your lightning fast responses haven't gone unnoticed, and are much appreciated. I've only got 3 hours left in me tonight, then I won't be back until Thursday, so no rush for answers, and thanks for sticking with me this long!

  10. #10
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Check again that you have added Scripting to your references. The error message is typical if the library files are missing (or not referenced) since VBA assumes you are defining a new user varable. What happens with me, often, is that I find the entry on the list of libraries, highlight it but forget to click on the checkbox.

    Don't know about the .temp approach - something to follow up on before Thursday. I'll have the Excel bit for you by then also.

    Remove the basOutlook line, that's my module name.

    Don't know that NewMailMessage.NewMailMessage will work. Where did you put the NewMailMessage procedure?

  11. #11
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Sorry I've had one of those Doh! moments and given you bum info. The library you want to reference is Microsoft Scripting Runtime and not that WMI one! You may leave the WMI checked or uncheck it at your pleasure. Sorry.

  12. #12
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    I'll give it a try next week, thanks. In the mean time, I changed the code back to the way I originally had it, and changed the path to C:\TEMP\Parts_Arrival.xlsx. I don't think that's how the code was that I saw somewhere before, but it seems to be working ok. It's probably saving to a TEMP file somewhere on the computer, but it's not on the desktop, and I haven't searched for it. I look forward to working on the spreadsheet part of it on Thursday.
    Thanks again for your help, and have a great weekend remainder.

  13. #13
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    Two more things on this if you get a chance...
    Is there a way to turn the filter on in VBA before the Excel attachment is open so the column filter arrows are visible?
    Is there a way to not show some of the fields on the spreadsheet that are on the query? I have a report based off of this same query, so I can't un-select the "Show" box without it messing up the report (being asked to enter parameters).
    Thanks for the help thus far.

  14. #14
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Is there a way to turn the filter on in VBA before the Excel attachment is open so the column filter arrows are visible?
    Don't know. Probably.

    Is there a way to not show some of the fields on the spreadsheet that are on the query?
    Probably. Don't know.

    Forgive the facetious answers but it's one of those afternoons. I'm not an Excel man and I have no idea what column filter arrows are - but I will find out! For the second requirement I'm fairly sure you will have to abandon the Access macro type feature and achieve this field by field using VBA and Office Automation.

    I'll get back to you asap.

  15. #15
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Well, Iíve found out about Excelís filter arrows; itís a one-liner. Unfortunately like everything else there are many lines necessary to get to that one line. Iíve written a sub procedure that accepts a full path to an Excel workbook and sets the autofilter feature.

    Notes:

    1. The procedure acts like a toggle: first run switches on, second run switches off, and so on.
    2. A full path file spec. is required as the argument of the procedure.
    3. The procedure assumes that there is an entry in cell A1 and all adjacent column headings (i.e. A2, A3, etc.)


    Code:
    Public Sub WorksheetAutofit(rstrFullPath As String)
        Dim xlsApp As Excel.Application
        Dim xlsWkb As Excel.Workbook
        Dim xlsWks As Excel.Worksheet
        Dim binCloseWkb As Boolean
        
        'Get Excel session or create one
        On Error Resume Next
        Set xlsApp = GetObject(, "Excel.Application")
        Select Case Err.Number
        Case 0
            On Error GoTo WorksheetAutofit_Error
        Case 429
            On Error GoTo WorksheetAutofit_Error
            Set xlsApp = CreateObject("Excel.Application")
        Case Else
            Err.Raise Err.Number
        End Select
        
        For Each xlsWkb In xlsApp.Workbooks
            If xlsWkb.Path = rstrFullPath Then
                binCloseWkb = False
                GoTo WorkbookOpened
            End If
        Next
        
        Set xlsWkb = xlsApp.Workbooks.Open(FileName:=rstrFullPath)
        binCloseWkb = True
        
    WorkbookOpened:
        xlsWkb.Activate
        For Each xlsWks In xlsWkb.Worksheets
            Range("A1").Select
            Selection.AutoFilter
        Next
        
    CleanUp:
        xlsWkb.Save
        If binCloseWkb Then xlsWkb.Close
        Set xlsWkb = Nothing
        Set xlsApp = Nothing
            
    Exit_Procedure:
        On Error GoTo 0
        Exit Sub
        
    WorksheetAutofit_Error:
        If Not xlsApp Is Nothing Then
            xlsApp.Visible = True
        End If
        Err.Raise Err.Number
        Resume Exit_Procedure
        
    End Sub
    Your second question: can you not remove the columns from the query? The only other way I can think of is to construct the worksheet labouriously, element by element, via VBA.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-05-2012, 04:06 PM
  2. Report export to Excel with format
    By mrswarrick in forum Access
    Replies: 3
    Last Post: 02-29-2012, 03:10 PM
  3. Replies: 3
    Last Post: 08-18-2011, 05:04 PM
  4. export format
    By kstyles in forum Reports
    Replies: 2
    Last Post: 07-07-2011, 07:01 PM
  5. Replies: 1
    Last Post: 07-27-2010, 07:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums