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

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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...

Answer 5

Perfectly.

It is working now?

Answer 6

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.