none
Sending an Excel file in Outlook, from an Access db and adding a signature block RRS feed

  • Question

  • Hi there,
    I want to send an Excel spreadsheet from a table in Access using Outlook.  I want the body of the email to include the signature block.  
    Here is the code I have (the code works, only it creates 2 Outlook messages:  1 with the signature block and 1 that has the Excel spreadsheet attached.  I need both of these in 1 email).

    I am running the code in a macro.   The macro opens the table:  tblPOItems, filters based on vendor nbr and then runs the Function for the below code.
    Thanks in advance for your help!

    Private Sub GetSignature()
       
    Dim OApp As Object, OMail As Object, signature As String
    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)
       
        With OMail
        .Display
        End With
             
        signature = OMail.body
       
        DoCmd.SendObject acSendTable, "tblPOItems", acFormatXLSX, _
        "Past Due Purchase Orders", "Dear Supplier Partner", True
       
         
    Set OMail = Nothing
    Set OApp = Nothing

    End Sub
    Friday, July 28, 2017 5:57 PM

Answers

  • Hi,

    It's because you're using two separate methods. You can use the first one but will have to manually send the email out. For example:

    ...

    With OMail

    DoCmd.OutputTo acOutputTable, "tblPOItems", acFormatXLSX, "c:\temp\POItems.xlsx",False

    .Attachments.Add "c:\temp\POItems.xlsx"

    .Display

    End With

    ...

    (untested - please double check the syntax for the OutputTo method)

    Hope it helps...

    • Marked as answer by Liz25mc Friday, July 28, 2017 6:19 PM
    Friday, July 28, 2017 6:02 PM

All replies

  • Hi,

    It's because you're using two separate methods. You can use the first one but will have to manually send the email out. For example:

    ...

    With OMail

    DoCmd.OutputTo acOutputTable, "tblPOItems", acFormatXLSX, "c:\temp\POItems.xlsx",False

    .Attachments.Add "c:\temp\POItems.xlsx"

    .Display

    End With

    ...

    (untested - please double check the syntax for the OutputTo method)

    Hope it helps...

    • Marked as answer by Liz25mc Friday, July 28, 2017 6:19 PM
    Friday, July 28, 2017 6:02 PM
  • Yes,  thank you, that seems to have worked!

    Quick response is very appreciated!

    Friday, July 28, 2017 6:10 PM
  • Hi,

    You're welcome! Glad to hear it worked for you. Good luck with your project.

    Friday, July 28, 2017 7:05 PM