MS Access Macros (Send Email)

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?
EnnistymonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
Ennistymon,

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"
    .Send
End With

Set olMsg = Nothing
Set olApp = Nothing

Open in new window


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

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_4316-Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html

Patrick
0
 
EnnistymonAuthor Commented:
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?
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
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...

JeffCoachman
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
EnnistymonAuthor Commented:
Thanks to both of you, Patrick and Jeff.
0
 
Nick67Commented:
@boag2000
You could put the code in a function and call it from a macro...but does that get around the 'No VBA' prohibition?

@Ennistymon
<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?
0
 
Patrick MatthewsCommented:
Glad to help :)
0
 
Jeffrey CoachmanMIS LiasonCommented:
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?
0
 
Nick67Commented:
If it worked, it's all good.
Having gone much more than the extra mile here
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_27217263.html?cid=1573#a36561425
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!
0
 
EnnistymonAuthor Commented:
This db will be uploaded to a Share Point service.
0
 
Nick67Commented:
You may then still be in the weeds.
There is VBA code that your macro is calling.
That may not work
http://www.thorprojects.com/blog/archive/2010/01/10/sharepoint-2010-and-access-services-place.aspx

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.
0
 
EnnistymonAuthor Commented:
I am using Access 2010 and uploading to SharePoint.
0
 
Nick67Commented:
<I am using Access 2010 and uploading to SharePoint.>
You have accepted an answer.
Did you completely test it?
Does it work?
0
 
EnnistymonAuthor Commented:
I'm working on it now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.