Results 1 to 12 of 12
  1. #1
    Baldeagle is offline Competent Performer
    Windows 10 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136

    Attach a report to emails

    We have been having trouble sending a series of emails to various clients from Access 2013 (using Outlook 2013). Our original coding scrolled through the Database picking up the selected clients and emailed a simple email with the specific report for each client attached. For some years this worked well. But then last year Outlook started to ask for permission to send each email because it said that 'A program is trying to access e-mail address information.......'. This is obviously a pain if multiple emails are being sent.
    I have now discovered the following sample coding which gets round this problem -
    Code:
    Sub SendMail()
    Dim outl As outlook.Application
    Set outl = New outlook.Application
    Dim mi As outlook.MailItem
    Set mi = outl.CreateItem(olMailItem)
    mi.Body = “test message”
    mi.Subject = “message from access”
    mi.To = “”
    mi.Send
    Set mi = Nothing
    Set outl = Nothing
    End Sub
    However I need it to pick up the relevant Report and attach it to each email. Can someone please suggest what coding would do that? After scrolling through on each loop the Report is called 'strDoc'.


    Our Charity is about to send out its annual mailing and we would appreciate help.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What was your original code ? It should be simple to incorporate bits of both codes.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Baldeagle is offline Competent Performer
    Windows 10 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Thanks for the response. The following is the code as adapted using the 'mi' code. The original 'DoCmd.SendObject' line is still there towards the very end but with a mark in front of it to exclude it.
    Code:
    Private Sub Command62_Click()  Dim varItem As Variant      'Selected items
       Dim strWhere As String      'String to use as WhereCondition
       Dim strDescrip As String    'Description of WhereCondition
       Dim lngLen As Long          'Length of string
       Dim strDelim As String      'Delimiter for this field type.
       Dim strDoc As String        'Name of report to open.
       Dim db As Database
       Dim rs As DAO.Recordset
       Dim ToVar As String
       Dim sql As String
       Dim strEmail As String
       Dim outl As outlook.Application
       Set outl = New outlook.Application
       Dim mi As outlook.MailItem
       Set mi = outl.CreateItem(olMailItem)
       
       
       strDelim = """"            'Delimiter appropriate to field type.
       strDoc = "July Cheque Email"
       
       'Loop through the ItemsSelected in the list box.
       With Me.lstCategory
           For Each varItem In .ItemsSelected
               strWhere = ""
               strDescrip = ""
               
               If Not IsNull(varItem) Then
                   'Build up the filter from the bound column (hidden).
                   strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                   'Build up the description from the text in the visible column.
                   strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
                   strEmail = .Column(1, varItem)
      
                End If
               
                   'Remove trailing comma. Add field name, IN operator, and brackets.
                       lngLen = Len(strWhere) - 1
                       If lngLen > 0 Then
                         strWhere = "[Name of Mission] IN (" & Left$(strWhere, lngLen) & ")"
                           lngLen = Len(strDescrip) - 2
                           If lngLen > 0 Then
                               strDescrip = "Email: " & Left$(strDescrip, lngLen)
                           End If
                        End If
                       
                       'Report will not filter if open, so close it.
                       If CurrentProject.AllReports(strDoc).IsLoaded Then
                           DoCmd.Close acReport, strDoc
                       End If
                       
                   DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
                   
                   mi.Body = "Attached is a Notice of money sent to the Bangor Worldwide Missionary Convention for your Organisation."
                   mi.Subject = "Payment from Bangor Worldwide Missionary Convention"
                   mi.To = strEmail
                   'mi.Attachments = strDoc
                   mi.Send
                   Set mi = Nothing
                   Set outl = Nothing
                   
                   
                   'DoCmd.SendObject acSendReport, strDoc, acFormatPDF, strEmail, , , "Payment from Bangor Worldwide Missionary Convention", "Attached is a Notice of money sent to the Bangor Worldwide Missionary Convention for your Organisation.  ", False
                   DoCmd.Close acReport, strDoc
           Next
           
       End With
       DoCmd.Close acReport, strDoc
    
    
    End Sub
    I look forward to your guidance.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    To use automation you'd need to create the file and then attach it. OutputTo would be a common method to create the file. You may be able to use your original method:

    https://support.microsoft.com/en-us/...half-warning-i
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Baldeagle is offline Competent Performer
    Windows 10 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Thanks for pointing me to that web page, Paul. I have been through that process before - my Trust Center options were greyed out. I have however used the sign-in as Administrator route and have been able to select the 3rd option but it now refuses to send an email.

    I would prefer if it was possible to leave that as it was but to get whatever coding would pick up the appropriate report in each loop and attach it to each email. Using the 'mi.' approach seemed to work OK to get past the Outlook block so getting it to attach the appropriate report would be the final answer. Can you or Minty adapt my code to do that?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In general you'd add:

    Code:
    Dim myattachments         As Variant
    Dim strPath As String
    
    strPath = "DesiredOutputPathHere"
    DoCmd.OutputTo acOutputReport, strDoc, acFormatPDF, strPath
    
    Set myattachments = mi.Attachments
            myattachments.Add strPath
    Do the output after you've opened the report filtered, then close it after.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Baldeagle is offline Competent Performer
    Windows 10 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Thanks for your guidance. Could I ask for clarification of what is meant by strPath = "DesiredOutputPathHere"? What is the path that I am supposed to put into the inverted commas please?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Wherever you want. In order to attach the file, it must exist somewhere. For example:

    strPath = "C:\Users\pbaldy\Report.pdf"

    So you save it somewhere and then attach it. You can delete it afterwards.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Baldeagle is offline Competent Performer
    Windows 10 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    I have returned to this this morning but I think I am getting into a bit of a mess!!

    In my previous approach the Reports were not saved as files, the attachments were simply the Reports that were created within Access and attached without being saved anywhere. That, to my mind, is the simplest approach if only I could get them attached to the emails!

    I have however tried to incorporate Paul's last suggestion into my code but I am getting the message "The OutputTo action was cancelled". My code as it stands now is
    Code:
    Private Sub Command62_Click()  Dim varItem As Variant      'Selected items
       Dim strWhere As String      'String to use as WhereCondition
       Dim strDescrip As String    'Description of WhereCondition
       Dim lngLen As Long          'Length of string
       Dim strDelim As String      'Delimiter for this field type.
       Dim strDoc As String        'Name of report to open.
       Dim db As Database
       Dim rs As DAO.Recordset
       Dim ToVar As String
       Dim sql As String
       Dim strEmail As String
       Dim outl As outlook.Application
       Set outl = New outlook.Application
       Dim mi As outlook.MailItem
       Set mi = outl.CreateItem(olMailItem)
       Dim myattachments As Variant
       Dim strPath As String
      
       
       strDelim = """"            'Delimiter appropriate to field type.
       strDoc = "July Cheque Email"
       
       'Loop through the ItemsSelected in the list box.
       With Me.lstCategory
           For Each varItem In .ItemsSelected
               strWhere = ""
               strDescrip = ""
               
               If Not IsNull(varItem) Then
                   'Build up the filter from the bound column (hidden).
                   strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                   'Build up the description from the text in the visible column.
                   strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
                   strEmail = .Column(1, varItem)
      
                End If
               
                   'Remove trailing comma. Add field name, IN operator, and brackets.
                       lngLen = Len(strWhere) - 1
                       If lngLen > 0 Then
                         strWhere = "[Name of Mission] IN (" & Left$(strWhere, lngLen) & ")"
                           lngLen = Len(strDescrip) - 2
                           If lngLen > 0 Then
                               strDescrip = "Email: " & Left$(strDescrip, lngLen)
                           End If
                        End If
                       
                       'Report will not filter if open, so close it.
                       If CurrentProject.AllReports(strDoc).IsLoaded Then
                           DoCmd.Close acReport, strDoc
                       End If
                       
                   DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
                   strPath = "c:\Our Folders\BWMC"
                   DoCmd.OutputTo acOutputReport, strDoc, acFormatPDF, strPath
                   Set myattachments = mi.Attachments
                       myattachments.Add strPath
                   
                   mi.Body = "Attached is a Notice of money sent to the Bangor Worldwide Missionary Convention for your Organisation."
                   mi.Subject = "Payment from Bangor Worldwide Missionary Convention"
                   mi.To = strEmail
                   mi.Send
                   Set mi = Nothing
                   Set outl = Nothing
                   
                   
                   'DoCmd.SendObject acSendReport, strDoc, acFormatPDF, strEmail, , , "Payment from Bangor Worldwide Missionary Convention", "Attached is a Notice of money sent to the Bangor Worldwide Missionary Convention for your Organisation.  ", False
                   DoCmd.Close acReport, strDoc
           Next
           
       End With
       DoCmd.Close acReport, strDoc
    
    
    End Sub
    Could someone untangle this for me or revert to the original approach?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    SendObject creates the attachment on the fly. Other methods require it to exist already, just as if you were sending it manually. Your path string should be a complete path including desired file name and extension. Other than the file name, the path must exist and you must have write permission to it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Baldeagle is offline Competent Performer
    Windows 10 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Many thanks for your help with this, Paul. My final struggle was trying to find out how to delete the file! I have eventually found the 'Kill' function and have used that successfully.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. attach more than one file
    By josros60 in forum Programming
    Replies: 1
    Last Post: 05-29-2016, 06:47 PM
  2. Replies: 5
    Last Post: 04-27-2015, 12:26 PM
  3. Replies: 30
    Last Post: 09-27-2013, 01:34 PM
  4. Attach Report to Email
    By teebumble in forum Reports
    Replies: 5
    Last Post: 11-28-2012, 05:42 PM
  5. Replies: 2
    Last Post: 12-28-2011, 09:32 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums