MS Access - Email Multiple Reports

shrimpfork
shrimpfork used Ask the Experts™
on
I am trying to program a button which will create an email and send out two reports as attachments.  A catch is that I would like the email to remain open for the user to modify the email text if required.

I have used the DoCmd.SendObject command in the past and it words will for a single report.   But now that I have two reports to attach AND I want the user to be able to edit the email before it is sent, I need a suggestion on how to handle this.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Best approach is to use the Outlook Object to automate your process and to have more control on attachments and to Edit your e-mail.

See below thread:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27037237.html?cid=748#a35771953

HTH,
Daniel
MIS Liason
Most Valuable Expert 2012
Commented:
Using the info in the link:
http://support.microsoft.com/kb/161088

You would have to edit the code to something roughly like this :
Sub SendMessage()
          Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          Dim objOutlookAttach As Outlook.Attachment

          ' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg  = objOutlook.CreateItem(olMailItem)

          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
              objOutlookRecip.Type = olTo

             ' Set the Subject, Body, and Importance of the message.
             .Subject = "This is an Automation test with Microsoft Outlook"
             .Body = "This is the body of the message." &vbCrLf & vbCrLf
             .Importance = olImportanceHigh  'High importance

             ' Add attachments to the message.
                 Set objOutlookAttach = .Attachments.Add(AttachmentPath1)
                 Set objOutlookAttach = .Attachments.Add(AttachmentPath2)
           
            ' Resolve each Recipient's name.
             For Each ObjOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next

             'Display the message for editing
                  .Display
           
          End With
          Set objOutlook = Nothing
      End Sub


Here it is presumed that the attachment is already created and is stored to a disk.


JeffCoachman
Top Expert 2010
Commented:
shrimpfork,

You might also like to review my article here, which includes an Access sample file:

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

Patrick
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
It looks like these examples all require the attachments to be precreated and saved somewhere on the computer.  I don't currently have it programmed this way.  The two attachements are sort of real time reports within access.  Is there an easy way to do this?
Most Valuable Expert 2012
Top Expert 2014

Commented:
The easy way is have the attachments to be precreated and saved somewhere on the computer.  If you want real-time reports you would have to use SQL Server Reporting Services tied directly to the SQL Server whereby a link is sent via email and they click on it and get data real-time.  

With Access all you can do is print out the reports as the data is in that point in time and then send the report - which in essence all a report is....because people can change the data after the report is run - doesn't mean its going to refresh.

I usually export mine to a pdf to a c:\local temp location and then do as the above Outlook VBA coding that the experts have shown...

To export as PDF with Access 2007 (be sure you have all your Service Packs for Office 2007 SP2)-

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_23597321.html

Author

Commented:
Experts:
Sorry, I was not very clear with my last post.  I have not yet played with saving reports to the computer.  I just need to make sure that there are not issues with overwriting these temporary attachement files when the code is run a second time.  Let me play around with this information and get back.

Author

Commented:
I am using a couple suggestions above (primarily boag2000's suggestion) and have it working except for a couple items.  I have a string of 2-3 emails that is in the TO field and 2 more in the CC field of the email.  These are all address and not true "recipients".  I am separating all the emails with a semi-colon.  When the program (outlook) resolves the addresses (or when the send button is clicked), it returns the "Check Names" dialog box for only the TO field.  It reads the string of emails as a single email address.  If I click in the TO field and back out of it, Outlook separates all the address correctly.  Any ideas what is wrong?
Most Valuable Expert 2012
Top Expert 2014
Commented:
The To is a collection so you need to add each address using .Add method.
Top Expert 2010

Commented:
CodeCruiser,

To is not a collection; rather, it is a delimited string.

Recipients, OTOH, is a collection.

:)

Patrick
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
< I have a string of 2-3 emails that is in the TO field and 2 more in the CC field of the email.>

These are new requirements, not mentioned in your original post.


If the Recipients and the CC will always be the same people, I would create a distribution list for the Recipients and another for the CCs.

The you could just reference them with syntax something like so:
Set objOutlookRecip = .Recipients.Add("RecipientsDistributionListName")

You would do the same for the CCs as well...

JeffCoachman

Author

Commented:
The addresses are project specific.  I can not use a distribution name for this.  I have it configured as a string.

I've played around with it and it seam like only the CC field is resolving correctly.  The TO string is fine, because if I change the code to insert the TO string into the CC field the CC field resolves fine, and if I put the CC string into the TO field it will not resolve the TO field.  It looks like it is an outlook problem.

Author

Commented:
I think I have to add each email address seperatly as CodeCruiser mentioned.
Top Expert 2010

Commented:
shrimpfork,

If you are using the Outlook automation class I put into my article http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_4316-Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html, you can pass in a semicolon-delimited string for the To, Cc, or Bcc properties.

Patrick

Author

Commented:
Thank you Experts!  I got it up and running.  Boag2000, your response was the quickest and easiest to get me going down the right road, while Matthewspatrick, your article was a ton of information and I did not really have the time to dissect what I needed.  You all contributed to this issue and I hope I fairly distribute the points.  Thanks again guys, it is great to have this resource.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Glad we were able to help.

enjoy the weekend

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial