Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

EmailDatabaseObject "Multiple Reports"

Posted on 2012-03-16
7
Medium Priority
?
1,955 Views
Last Modified: 2012-03-23
I have this code within a module that emails specific reports to client. The code calls a function that opens up Outlook, calls a wait function and waits 20 seconds for Outlook to send/receive. It then sends an email with one report, waits 3 seconds, then another, and another, and Outlook does an automatic send/receive before exiting. The problem is all these emails are going to the same person.

What I would like to do is make it so that all three reports, are sent in the same email. I have tried various combinations of the code and they all fail. This is the code that sends the emails:

    OpenOL
    WaitSeconds (20)
    DoCmd.SendObject acReport, "CustStmt", "PDFFormat(*.pdf)", "rick@kilduff.com", "", "", "CustStmt", "", False, ""

    WaitSeconds (3)

    DoCmd.SendObject acReport, "CustAdjmts", "PDFFormat(*.pdf)", "rick@kilduff.com", "", "", "CustAdjmts", "", False, ""

    WaitSeconds (3)

    DoCmd.SendObject acReport, "CustStmt Summary (Roche)", "PDFFormat(*.pdf)", "rick@kilduff.com", "", "", "CustStmt Summary (Roche)", "", False, ""
   
CloseOL

Any help would be greatly appreciated
0
Comment
Question by:dawber39
[X]
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
7 Comments
 
LVL 58
ID: 37729777
SendObject simply can't do that, so you'll need to find another way to send e-mails.  There are a couple of basic methods:

1. Use a command line utility such as BLAT.

2. Use software that talks directly to a e-mail server (SMTP) such as vbSendMail

3. Using OLE automation, talk to Outlook and have it send the e-mail.

 All three of the above will allow for multiple attachments in a single e-mail.  However with that said, that's the other piece of this; you need to run and save the reports to disk first in some format (PDF, Snapshot, etc) before you can send as a single e-mail.

Jim.
0
 
LVL 58
ID: 37729785
You might also want to consider commercial software, such as FMS, inc's Total Access Mailer.

Jim.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37729870
Not sure, but you may be able to do this is you instantiate Outlook, using this fairly common code:
http://support.microsoft.com/kb/161088

You would probably need to change the top to this:

    Sub SendMessage(DisplayMsg As Boolean)

...and change the "Add Attachment" section to something like this:
             ' Add attachments to the message.
                      Set objOutlookAttach = .Attachments.Add(AttachmentPath1)
                      Set objOutlookAttach = .Attachments.Add(AttachmentPath2)
                      Set objOutlookAttach = .Attachments.Add(AttachmentPath3)

(I am not quite sure why your "Delays" are needed though...)


JeffCoachman
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 37730008
If you can create the pdf's to a folder, this code will email them'
Option Compare Database
Option Explicit

Public Sub CreateAnEmail(reportcaption As String, ClientEmail As String, AttachmentPath As String)
'reportcaption is a semicolon delimited string of report file names, without file extensions
'eg "rptReport1;rptReport2"
'ClientEmail is a semicolon delimited string of recipients
'eg "someone@somewhere.com;anyone@anywhere.com"
'AttachmentPath is a sting pointing to where your PDFs are
'eg "C:\temp"

On Error GoTo myerr
Dim DisplayMsg As Boolean
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim objOutlookExplorers As Outlook.Explorers
Dim myarray() As String
Dim myaddresses() As String
Dim x As Integer
Dim fs As Object
Dim BuiltPath As String
Dim response As Integer
Dim wasOpen As Boolean


myarray = Split(reportcaption, ";")
'MsgBox UBound(MyArray, 1)


DisplayMsg = True
'AttachmentPath = "c:\temp\" & ReportCaption & ".pdf"

MsgBox "The email is about to be created!" & vbCrLf & _
        "If nothing appears to be happening, the Outlook security box may be hiding behind an open window." & vbCrLf & _
        "Click the Outlook icon on the taskbar to bring it to the front, if necessary."

Set objOutlook = GetObject(, "Outlook.Application")
'MsgBox Err.Number & " " & Err.Description
If Err.Number = 429 Then
    Err.Clear
    wasOpen = False
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    
Else
    wasOpen = True
End If
    Dim ns As Outlook.NameSpace
    Dim Folder As Outlook.MAPIFolder
    Set ns = objOutlook.GetNamespace("MAPI")
    Set Folder = ns.GetDefaultFolder(olFolderInbox)
    Set objOutlookExplorers = objOutlook.Explorers
    
If wasOpen = False Then
    objOutlook.Explorers.Add Folder
    Folder.Display
    'done opening
End If

AppActivate "Microsoft Outlook"

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

'Who at Baker Oil Tools gets the email depends on Location/Cost Code


With objOutlookMsg
    If Not ClientEmail Like "*@*" Then
        MsgBox "You haven't supplied a valid email!"
        Exit Sub
    End If
    
    If Nz(ClientEmail, "") <> "" Then
    ' Add the To recipient(s) to the message.
    myaddresses = Split(ClientEmail, ";")
    For x = LBound(myaddresses) To UBound(myaddresses)
        Set objOutlookRecip = .Recipients.Add(myaddresses(x))
        objOutlookRecip.Type = olTo
    Next x
    End If
    

    
    'can't do this, the mail never gets sent
    ' Add the from recipient(s) to the message.
    'Set objOutlookRecip = .Recipients.Add("Someone@SomeWhere.com")
    'objOutlookRecip.Type = olOriginator
    
    ' Add the CC recipient(s) to the message.
    'Set objOutlookRecip = .Recipients.Add("Someone@SomeWhere.com")
    'objOutlookRecip.Type = olCC

   ' Add the BCC recipient(s) to the message.
    'Set objOutlookRecip = .Recipients.Add(""Someone@SomeWhere.com"")
    'objOutlookRecip.Type = olBCC
    

   ' Set the Subject, Body, and Importance of the message.
         .Subject = "Requested reports " & IIf(Len(reportcaption) < 161, reportcaption, Left(reportcaption, 159) & "...")

   .Body = "This is an automated sending by XXXXXXXXXXX as requested." & vbCrLf & vbCrLf & _
   "Please respond to Someone@SomeWhere.com with any inquiries"
    
   ' Add attachments to the message.
   'reportcaption is passed in and split into myarray
   'one attachment needs to be added for each element in myarray

    For x = LBound(myarray, 1) To UBound(myarray, 1)
        AttachmentPath = AttachmentPath & "\" & myarray(x) & ".pdf"
        If Not IsMissing(AttachmentPath) Then
            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        Else
            MsgBox "The attachment was not found or attached!"
        End If
    Next x
    
            
   ' Resolve each Recipient's name.
   For Each objOutlookRecip In .Recipients
       objOutlookRecip.Resolve
   Next

   ' Should we display the message before sending?
   If DisplayMsg Then
       .Display
   Else
       .Save
       .Send
   End If
End With


Set objOutlook = Nothing

Exit Sub
myerr:
    If Err.Number = 5 Then
Resume Next
Else
    MsgBox Err.Number & " " & Err.Description & " occurred.  exiting!"
End If

End Sub

Open in new window


Working sample attached.
Note that a reference to the Microsoft Outlook 1x.0 Object Library is required
(11.0 for Access 2003, 12.0 for Access 2007, 14.0 for Access 2010)
email.mdb
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37730870
I cleaned that up from working code and tested it.
There are, looking at now, some extraneous declarations and comments in it
:0
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 37732561
Hi Jim, Thanks for mentioning Total Access Emailer:
http://www.fmsinc.com/MicrosoftAccess/Email.asp

It is a Microsoft Access add-in that lets you automatically attach multiple filtered reports as PDF to a message. You can also compress all your attachments into a zip file. The zip file can also be password protected using your phrase or a value from a field in your datasource.

Total Access Emailer also has a programmatic interface to send emails from VBA. A free trial version is available. It uses SMTP to send emails, so it bypasses all the Outlook and MAPI security limitations. Free trial here: http://www.fmsinc.com/MicrosoftAccess/Email/free-trial.html

Here's a paper we wrote entitled: DoCmd SendObject Command in Microsoft Access: Features and Limitations for Sending Emails
http://www.fmsinc.com/MicrosoftAccess/Email/SendObject.html
0
 

Author Closing Comment

by:dawber39
ID: 37758693
Haven't tried it yet - but the sample works - had shoulder surgery, and everything is on hold for now - thank you all
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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