Solved

MS Access Macros (Send Email)

Posted on 2011-09-22
13
725 Views
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?
0
Comment
Question by:Ennistymon
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 200 total points
ID: 36581759
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
 

Author Comment

by:Ennistymon
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?
0
 
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...

JeffCoachman
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Closing Comment

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

Expert Comment

by:Nick67
ID: 36583102
@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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36583154
Glad to help :)
0
 
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?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36583995
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
 

Author Comment

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

Expert Comment

by:Nick67
ID: 36584193
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
 

Author Comment

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

Expert Comment

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

Author Comment

by:Ennistymon
ID: 36590033
I'm working on it now.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

810 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