Results 1 to 9 of 9
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    email attachment


    All; using Access 2010. I have code to attach a report to an email. The following is the code for the report:

    Code:
    ' Create the report #1.
    strPathAndFilename_Report1 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedName & "_Report1.pdf"
    DoCmd.OutputTo acOutputReport, "rpt_1", acFormatPDF, strPathAndFilename_Report1, False
    DoEvents     ' Allow this operation to be fully completed before proceeding.
     
    ' Create the report #2.
    strPathAndFilename_Report2 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedName & "_Report2.PDF"
    DoCmd.OutputTo acOutputReport, "rpt_2", acFormatPDF, strPathAndFilename_Report2, False
    DoEvents     ' Allow this operation to be fully completed before proceeding.
    It works fine. It attaches the report in pdf form to the email. But I want report #2 to email in excel format. How do I do this? I tried to change PDF to XLSX i.e. acFormatXLSX and it's not working. Can this be done or what am I doing wrong please. Help

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,712
    Try:

    acFormatXLS

    without the second X.

    https://msdn.microsoft.com/en-us/lib...ffice.11).aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks this worked but the format is off. It gave me groupings of some sort. How do I get it to format to just a regular spreadsheet? Thanks

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,712
    Exporting reports to spreadsheet can often be unsatisfactory, especially if the report has grouping and/or subreports. Can't control that. The simpler the report structure the better the export results.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks for responding. I had to change acOutputRpt to acOutputQuery and change the report to a query. Is there a way to, when the query exports, to change the name of the worksheet. instead of the users seeing the name of the query? Thanks

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,712
    Not with OutputTo method. Would need code to open and manipulate the Excel file as object in VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    o ok. thanks

  8. #8
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    399

    YES you can...

    heres 2 bits of code i use to create timesheets, first one uses a query result and adds extra sheets to the excel workbook, the second adds req sheets and rows to workbook, they both use the range option of the transfer spreadsheet method to name the sheets

    Code:
    Private Sub Command94_Click()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim gg As String
        sdt = Format(start_date, "dd-mm-yy")
        edt = Format(End_date, "dd-mm-yy")
        gg = "C:\aaa\timesheets\Employee Time Report master.xls"
        aa = MsgBox("                 Do you also require Manaul Sheets ?", vbYesNoCancel + vbDefaultButton2, "Time Sheet Additions")
        If aa = vbYes Then
            CC = InputBox("Enter number of sheets required", "Excel time sheet generation", 1)
            bb = InputBox("Enter number of lines per sheet required", "Manual Time Sheet creation", 1)
            Else
            If aa = vbCancel Then Exit Sub
        End If
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("create excel time sheets for selected employees on main menu")
        With rs
            .MoveFirst
            Do While Not .EOF
                fn = rs.Fields("First Name")
                Ln = rs.Fields("Last Name")
                cd = rs.Fields("Code #")
                bc = rs.Fields("barcode")
                Me.barcode = bc
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "print time sheets for selected employees", gg, True, fn & " " & Ln & " " & cd
                DoEvents
                .MoveNext
            Loop
        End With
        If aa = vbYes Then
            DoCmd.RunSQL "DELETE * FROM manual_sheet"
            t = Forms![main menu].start_date
            For i = 1 To bb
                Set rstActual = db.OpenRecordset("manual_sheet", dbOpenDynaset)
                rstActual.AddNew
                rstActual!new_date = t
                rstActual.Update
                rstActual.Close
                t = t + 1
            Next i
            nRecords = DCount("*", "Employee Time Report Output with lunch for manual sheet")
            If nRecords = 0 Then
                MsgBox "No data for selected period, change report dates and try agian.", vbOKOnly, "Error"
                Exit Sub
            End If
            For i = 1 To CC
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output with lunch for manual sheet", gg, True, "FirstName" & i & " " & "LastName" & i
            Next i
        End If
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        Call format_sheets_now
        t = MsgBox("All requested Excel Time Sheets have been created" & vbCrLf & "          And saved in the following directory" & vbCrLf & vbCrLf & "                   C:\aaa\timesheets", vbOKOnly, "Automated Time Sheet Generation")
    End Sub                '***********************************************************************************************************************************************
    Private Sub Command147_Click()          'create manual time sheet
        Dim db As DAO.Database
        Set db = CurrentDb
        Dim rstActual As DAO.Recordset
        Set rstActual = db.OpenRecordset("manual_sheet")
        gg = "C:\aaa\timesheets\Employee Time Report master.xls"
        bb = InputBox("Enter number of sheets required", "Excel time sheet generation", 1)
        aa = InputBox("Enter number of lines per sheet required", "Manual Time Sheet creation", 1)
        fn = "FirstName"
        Ln = "LastName"
        DoCmd.RunSQL "DELETE * FROM manual_sheet"
       t = Forms![main menu].start_date
        For i = 1 To aa
            Set rstActual = db.OpenRecordset("manual_sheet", dbOpenDynaset)
            rstActual.AddNew
            rstActual!new_date = t
            rstActual.Update
            rstActual.Close
            t = t + 1
        Next i
        For i = 1 To bb
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output with lunch for manual sheet", gg, True, "FirstName" & i & " " & "LastName" & i
        Next i
        Call format_sheets_now
        t = MsgBox("All requested Excel Time Sheets have been created" & vbCrLf & "          And saved in the following directory" & vbCrLf & vbCrLf & "                   C:\aaa\timesheets", vbOKOnly, "Automated Time Sheet Generation")
    End Sub
    

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,712
    Okay, I forgot about TransferSpreadsheet (but I was correct about OutputTo method). So either TransferSpreadsheet or VBA object should get you what you want.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Email Attachment
    By aelyashi in forum Access
    Replies: 1
    Last Post: 04-01-2014, 04:44 AM
  2. Replies: 13
    Last Post: 12-05-2013, 06:04 PM
  3. how do I add the attachment to an email?
    By LanieB in forum Forms
    Replies: 2
    Last Post: 11-01-2013, 10:19 AM
  4. Email with attachment
    By Ganymede in forum Access
    Replies: 2
    Last Post: 06-01-2012, 01:59 PM
  5. email attachment from Access
    By Gandalf in forum Queries
    Replies: 0
    Last Post: 01-22-2009, 10:03 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
  •  
Tech Forums: Microsoft Office Forums