none
ADD EXISTING EXCEL SPREADSHEET TO OUTOOK MESSAGE AS ATTACHMENT. RRS feed

  • Question

  • I am using Access to create shipping documents to send to customers via Outlook when an order ships.  I am already using the OutputReport feature to create a .pdf for product specification details, which creates an Outolook Message and attaches the .pdf, but I would also like to add a pre-existing Excel Spreadsheet as a Packing List/Shipping document.  Can anyone help me?  My current procedure code below:

    Private Sub Command115_Click()
    Dim objOutlook As Object
        Dim objOutlookMsg As Object
        Dim objOutlookRecip As Object
        Dim objOutlookAttach As Object
        Dim strFileName As String

        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")

        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(0) ' olMailItem

        With objOutlookMsg
            ' Add the To recipient(s) to the message.
            If Not IsNull([Forms]![SALES ORDER]![SoEmailTo]) Then
                Set objOutlookRecip = .Recipients.Add([Forms]![SALES ORDER]![SoEmailTo])
                objOutlookRecip.Type = 1 ' olTo
            End If

            ' Add the CC recipient(s) to the message.
            If Not IsNull([Forms]![SALES ORDER]![SoCCTo]) Then
                Set objOutlookRecip = .Recipients.Add([Forms]![SALES ORDER]![SoCCTo])
                objOutlookRecip.Type = 2 ' olCC
            End If

           ' Add the BCC recipient(s) to the message.
            If Not IsNull([Forms]![SALES ORDER]![SoBCCTo]) Then
                Set objOutlookRecip = .Recipients.Add([Forms]![SALES ORDER]![SoBCCTo])
                objOutlookRecip.Type = 3 ' olBCC
            End If
            
            ' Set the Subject and Body of the message.
            .Subject = "UWC CERT " & [orderid] & " " & Forms![SALES ORDER].Form!Text82
            .Body = Forms![SALES ORDER].Form!Text82 & ", Attached please find Material Certification of your Purchase Order#: " & Me.[CUST #] & ".  Please alert us to any discrepancies.  Sincerely, UWC." & vbCrLf & vbCrLf

            ' Create PDF
                strFileName = "S:\Material Cert PDF Files\UWC CERT " & _
                Me.orderid & " " & Forms![SALES ORDER].Form!Text82 & " SHIPMENT " & [Forms]![SALES ORDER]![SHIPMENTS SUBTABLE].[Form]![ShipmentID] & ".PDF"
                
           DoCmd.OpenReport "cert report1", acViewReport, , "orderid =" & Me.orderid
           DoCmd.OutputTo acOutputReport, , acFormatPDF, strFileName, True
            ' Add attachment to the message.
            Set objOutlookAttach = .ATTACHMENTS.Add(strFileName)

           ' Use one of the following, not both
            .Display ' to display the message so that the user can review it
        End With
        Set objOutlook = Nothing
    End Sub

    Wednesday, January 29, 2020 2:26 PM

Answers

  • If you can add one attachment, you can add many, right?

    Just add another " .ATTACHMENTS.Add" line, with the path to the XL file.


    -Tom. Microsoft Access MVP

    • Marked as answer by jswan1001 Wednesday, January 29, 2020 5:04 PM
    Wednesday, January 29, 2020 3:33 PM

All replies

  • If you can add one attachment, you can add many, right?

    Just add another " .ATTACHMENTS.Add" line, with the path to the XL file.


    -Tom. Microsoft Access MVP

    • Marked as answer by jswan1001 Wednesday, January 29, 2020 5:04 PM
    Wednesday, January 29, 2020 3:33 PM
  • Thanks Tom, I had tried that previously, but perhaps wasn't using the variable/object properly.  I added a second variable for second attachment (strFileName2) and added additional ".attachments.add" line and it worked!!  Functional code snippet below.

        ' Create PDF
            strFileName = "S:\Material Cert PDF Files\UWC CERT " & _
                Me.orderid & " " & Forms![SALES ORDER].Form!Text82 & " SHIPMENT " & [Forms]![SALES ORDER]![SHIPMENTS SUBTABLE].[Form]![ShipmentID] & ".PDF"
            strFileName2 = "s:\packing lists\" & Me.orderid & " " & Me.Controls![Text469] & ".xlsx"
           DoCmd.OpenReport "cert report1", acViewReport, , "orderid =" & Me.orderid
           DoCmd.OutputTo acOutputReport, , acFormatPDF, strFileName, True
            ' Add attachment to the message.
            Set objOutlookAttach = .ATTACHMENTS.Add(strFileName)
            .ATTACHMENTS.Add (strFileName2)

    Wednesday, January 29, 2020 5:06 PM