none
Can I attach more then one report in Excel format to an email started with MS Access 2013 RRS feed

  • Question

  • Hi

    Today I send monthly reports to our auditor, its a number of ten. He wants them in Excel format.

    The only solution I have found is to send ten emails, one by one.

    Is it possible to "bundle or attach" all ten excel files into one email by a simple pushbutton executed by VBA code?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Thursday, September 24, 2015 11:28 AM

Answers

  • Press ALT + F11, set the checkbox 'Microsoft Outlook xx.x' in Tools -> References and click OK.

    Insert -> Module and paste the code below, adapting to your case.

    Sub AddAttachment() 
    
     Dim myItem As Outlook.MailItem 
     Dim myAttachments As Outlook.Attachments 
     
     Set myItem = Application.CreateItem(olMailItem) 
     Set myAttachments = myItem.Attachments 
    
     myAttachments.Add "D:\Documents\Q496.xlsx", _ 
     olByValue, 1, "4th Quarter 1996 Results Chart" 
    
     myItem.send
    
    End Sub


    Thursday, September 24, 2015 12:27 PM
  • Hi Peter. There's a step missing. Create the Outlook application first before creating the mail item. For example:

    Set olApp = Application.CreateObject("Outlook.Application")

    Set olMail = olApp.CreateItem(olMailItem)

    Hope that helps...

    Thursday, September 24, 2015 1:49 PM

All replies

  • You can use automation against the email client, for example Outlook. You would create an Outlook object and then use the Outlook object model to create the email and attach multiple files.

    Paul

    Thursday, September 24, 2015 11:53 AM
  • Press ALT + F11, set the checkbox 'Microsoft Outlook xx.x' in Tools -> References and click OK.

    Insert -> Module and paste the code below, adapting to your case.

    Sub AddAttachment() 
    
     Dim myItem As Outlook.MailItem 
     Dim myAttachments As Outlook.Attachments 
     
     Set myItem = Application.CreateItem(olMailItem) 
     Set myAttachments = myItem.Attachments 
    
     myAttachments.Add "D:\Documents\Q496.xlsx", _ 
     olByValue, 1, "4th Quarter 1996 Results Chart" 
    
     myItem.send
    
    End Sub


    Thursday, September 24, 2015 12:27 PM
  • Hi André

    Did as you suggested. When compile I get an error

    Set myItem = Application.CreateItem(olMailItem)

    "Method or data member not found (Error 461)


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Thursday, September 24, 2015 1:11 PM
  • Hi Peter. There's a step missing. Create the Outlook application first before creating the mail item. For example:

    Set olApp = Application.CreateObject("Outlook.Application")

    Set olMail = olApp.CreateItem(olMailItem)

    Hope that helps...

    Thursday, September 24, 2015 1:49 PM
  • Perfectly.

    It is working now?

    Thursday, September 24, 2015 2:45 PM
  • Yes, you can do that.  I loaded a few samples to my server.  You can download them from here.

    https://www.mediafire.com/folder/5vhm49mkbbx8r/Access_Email


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, October 11, 2015 1:23 PM