Go Premium for a chance to win a PS4. Enter to Win


MS Access Macros (Send Email)

Posted on 2011-09-22
Medium Priority
Last Modified: 2013-11-27
I want to send multiple reports from my database on one email by using a macro. I know I can attach one report to an email and send it by using a macro, but if I have five reports I don't want to send five different emails. Is this possible? If so, how?
Question by:Ennistymon
  • 5
  • 4
  • 2
  • +1
LVL 93

Accepted Solution

Patrick Matthews earned 800 total points
ID: 36581759

You would have to export your five reports as files (such as Excel, RTF, PDF, or SnapShot), and then use Outlook automation (or whatever your email client is) to attach the files and send the email.  For example:

' assume you already have the OutputTo to export the 5 reports

Dim olApp As Object
Dim olMsg As Object

Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0)
With olMsg
    .To = "X@z.com"
    .CC = "y@z.com"
    .BCC = "z@z.com"
    .Subject = "subject"
    .Body = "body"
    .Attachments.Add "c:\folder\subfolder\report1.xls"
    .Attachments.Add "c:\folder\subfolder\report2.xls"
    .Attachments.Add "c:\folder\subfolder\report3.xls"
    .Attachments.Add "c:\folder\subfolder\report4.xls"
    .Attachments.Add "c:\folder\subfolder\report5.xls"
End With

Set olMsg = Nothing
Set olApp = Nothing

Open in new window

You might also want to have a look at this article:



Author Comment

ID: 36581824
This application will reside on a web server, i.e. no VBA allowed. That is why I asked about macros. Is there a way to do this with a macro?
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 36581902
Make Patrick's code a Public function.

Public Function YourFunction()
    'Patrick's code
End function

Put it in a standalone code module.

Then you can "call" it from a macro:
Action: Run Code
Function: YourFunction()

The kicker here is that this will all work fine if everything is "Hardcoded"
If you need Variables, then this gets more complex...

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Closing Comment

ID: 36583000
Thanks to both of you, Patrick and Jeff.
LVL 26

Expert Comment

ID: 36583102
You could put the code in a function and call it from a macro...but does that get around the 'No VBA' prohibition?

<This application will reside on a web server>
That does not necessarily mean no VBA.
I've help at least 2 posters lately who have automated Access from C# ASP.NET applications.

Or are you doing something benighted like SharePoint or web-published Access 2010 applications?
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36583154
Glad to help :)
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36583710
Nick, not being sure of the scope of the "No VBA" restriction (I know nothing about web servers), I simply suggested creating the function then calling it form a macro...

who knew?
LVL 26

Expert Comment

ID: 36583995
If it worked, it's all good.
Having gone much more than the extra mile here
I am aware that, within limits, you can do much of the automation we are used to in VBA, from C# and VB.NET
<I know nothing about web servers>
Not that I know that much about them either!

Author Comment

ID: 36584070
This db will be uploaded to a Share Point service.
LVL 26

Expert Comment

ID: 36584193
You may then still be in the weeds.
There is VBA code that your macro is calling.
That may not work

I haven't played extensively with the Access 2010 Macros, because, with a half million lines of VBA that I am not abandoning, they are a dead-end to me.
If you wind up posting another question, state explicitly that you are using Access 2010 and uploading to SharePoint, and therefore your solution must be strictly 2010 Macro based.
It may very well be doable, because the new macros have come a long way.

Author Comment

ID: 36584296
I am using Access 2010 and uploading to SharePoint.
LVL 26

Expert Comment

ID: 36584305
<I am using Access 2010 and uploading to SharePoint.>
You have accepted an answer.
Did you completely test it?
Does it work?

Author Comment

ID: 36590033
I'm working on it now.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question