MS Access Macros (Send Email)

Posted on 2011-09-22
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
LVL 92

Accepted Solution

Patrick Matthews earned 200 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 = ""
    .CC = ""
    .BCC = ""
    .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 50 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...

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


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 92

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
CodeTwo Sync for iCloud ( automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

726 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