none
How to Send Multiple Report in one E-mail RRS feed

  • Question

  • I would like to create a VBA Module where I can place several reports on a single
    email. Currently, I have to send each report on a seperate email when I use
     the SendObject action. Below is my Code

    Option Compare Database

    Function Send_E_mail()
    On Error GoTo Send_E_mail_Err
    DoCmd.SendObject acReport, "ABC Report 1", "PDFFormat(*.pdf)", "abc@sample.com", "", "", "Testing  Report", "Please find attached Sample report","",True, ""
     DoCmd.SendObject acReport, "ABC Report 2", "PDFFormat(*.pdf)", "abc@sample.com", "", "", "Testing Report", "Please find attached Sample report","",True, ""       

    Exit Function

    Send_E_mail_Err:
        MsgBox Error$
        Resume Send_E_mail_Exit

    End Function

     How I Can send above two reports in one e-mail using 


    • Edited by adcz250 Monday, February 18, 2013 4:13 PM
    Monday, February 18, 2013 3:16 PM

Answers

  • Hi adcz250,

    We can use outlook automation to implement this. Please refer to the following code:

    Option Explicit
    
    Sub SendMessage(DisplayMsg As Boolean)
    
        DoCmd.OutputTo acOutputReport, "rptBook", "PDFFormat(*.pdf)", "D:\rptBook.pdf", False
        DoCmd.OutputTo acOutputReport, "rptReport", "PDFFormat(*.pdf)", "D:\rptReport.pdf", False
        
        
        Dim objOutlook As Outlook.Application
        Dim olNs As Outlook.Namespace
        Dim objOutlookMsg As Outlook.MailItem
        Dim objOutlookRecip As Outlook.Recipient
        Dim objOutlookAttach As Outlook.Attachment
        
        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")
        
        
         Set olNs = objOutlook.GetNamespace("MAPI")
         olNs.Logon
    
        
        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        
        With objOutlookMsg
            ' Add the To recipient(s) to the message.
            Set objOutlookRecip = .Recipients.Add("v-yoyj@microsoft.com")
            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.      
          .Attachments.Add ("D:\rptBook.pdf")
          .Attachments.Add ("D:\rptReport.pdf")
        
           ' 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
        
        olNs.Logoff
        Set objOutlook = Nothing
    End Sub
    

    Have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 20, 2013 9:07 AM
    Moderator
  • I have sample code that uses late binding at my site, That'll Do IT.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Proposed as answer by smarbar Thursday, February 21, 2013 8:01 AM
    • Marked as answer by Dummy yoyoModerator Thursday, February 28, 2013 3:22 AM
    Wednesday, February 20, 2013 3:10 PM
  • Jeeezzzz.  After all that time I spent on this, you can't spend 5 seconds to click on a button.  After this one, I'm not going to help you anymore!!

    Sending multiple attachments is one of the limitations of using SendObject:

    http://www.granite.ab.ca/access/email/sendobject.htm

    There is a work-around here: 'EmailSenate'

    http://www.granite.ab.ca/access/email.htm


    Ryan Shuell

    • Proposed as answer by smarbar Thursday, February 21, 2013 8:01 AM
    • Marked as answer by Dummy yoyoModerator Thursday, February 28, 2013 3:23 AM
    Thursday, February 21, 2013 6:03 AM

All replies

  • Please take a look at this:

    http://www.mediafire.com/?j7och9ohnim8zcz


    Ryan Shuell

    Monday, February 18, 2013 4:47 PM
  • Hi Ryan,

    I tried several times and still failed to download the file. Would you please upload the file via SkyDrive? Thank you.


    Wednesday, February 20, 2013 3:52 AM
  • Wednesday, February 20, 2013 4:16 AM
  • Thanks.
    Wednesday, February 20, 2013 8:40 AM
  • Hi adcz250,

    We can use outlook automation to implement this. Please refer to the following code:

    Option Explicit
    
    Sub SendMessage(DisplayMsg As Boolean)
    
        DoCmd.OutputTo acOutputReport, "rptBook", "PDFFormat(*.pdf)", "D:\rptBook.pdf", False
        DoCmd.OutputTo acOutputReport, "rptReport", "PDFFormat(*.pdf)", "D:\rptReport.pdf", False
        
        
        Dim objOutlook As Outlook.Application
        Dim olNs As Outlook.Namespace
        Dim objOutlookMsg As Outlook.MailItem
        Dim objOutlookRecip As Outlook.Recipient
        Dim objOutlookAttach As Outlook.Attachment
        
        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")
        
        
         Set olNs = objOutlook.GetNamespace("MAPI")
         olNs.Logon
    
        
        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        
        With objOutlookMsg
            ' Add the To recipient(s) to the message.
            Set objOutlookRecip = .Recipients.Add("v-yoyj@microsoft.com")
            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.      
          .Attachments.Add ("D:\rptBook.pdf")
          .Attachments.Add ("D:\rptReport.pdf")
        
           ' 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
        
        olNs.Logoff
        Set objOutlook = Nothing
    End Sub
    

    Have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 20, 2013 9:07 AM
    Moderator
  • smarbar-

    Please click 'Propose As Answer' if my solution helped you.  I didn't actually develop the code, or the concept, I just found it online a while back and saved it, cause I thought I (or someone else) would find it very useful, someday.


    Ryan Shuell

    Wednesday, February 20, 2013 1:38 PM
  • I have sample code that uses late binding at my site, That'll Do IT.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Proposed as answer by smarbar Thursday, February 21, 2013 8:01 AM
    • Marked as answer by Dummy yoyoModerator Thursday, February 28, 2013 3:22 AM
    Wednesday, February 20, 2013 3:10 PM
  • Hi Ryan,

    I'm really appreciate for you taking the trouble to upload the database one more time. However, I'm afraid that the database you provided shows way to email one report to multiple recipients, but not multiple reports in a single email. So I didn't "propose" as an answer, regarding the original question posted by OP. But it do help me to solve my problem in a certain way. Thanks for sharing. :)

    Best Regards,



    • Edited by smarbar Thursday, February 21, 2013 5:18 AM
    Thursday, February 21, 2013 5:00 AM
  • Jeeezzzz.  After all that time I spent on this, you can't spend 5 seconds to click on a button.  After this one, I'm not going to help you anymore!!

    Sending multiple attachments is one of the limitations of using SendObject:

    http://www.granite.ab.ca/access/email/sendobject.htm

    There is a work-around here: 'EmailSenate'

    http://www.granite.ab.ca/access/email.htm


    Ryan Shuell

    • Proposed as answer by smarbar Thursday, February 21, 2013 8:01 AM
    • Marked as answer by Dummy yoyoModerator Thursday, February 28, 2013 3:23 AM
    Thursday, February 21, 2013 6:03 AM
  • Ryan, I've voted a help for your second reply, since it do help me for my problem. But I am not the OP, and in my humble opinion, if a reply helps to solved the original question posted by OP, I will propose the reply of course.

    Sorry if I offended you unconsciously.

    Also, I want to let you know that with all my heart, I respect you and all the other old birds so much for taking the time to share their knowledge and help others in the forum.

    All best wishes,

    smarbar


    • Edited by smarbar Thursday, February 21, 2013 8:11 AM
    Thursday, February 21, 2013 8:10 AM