Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    kierandobson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Posts
    15

    Send Email with multiple attachments using several fields as file name. All in same directory.

    Hello



    I am a complete Novice to access but so far managed to figure out how to compile a decent database. I now want to take it a step further.

    Below is one of my forms.
    Click image for larger version. 

Name:	Capture.PNG 
Views:	47 
Size:	38.3 KB 
ID:	29273
    When I click the home button (representing end of day) it applies a filter to only the orders that were placed today:
    Click image for larger version. 

Name:	Capture2.PNG 
Views:	48 
Size:	13.8 KB 
ID:	29274
    I have files matching the name of the SubNo column, all in the same directory.
    I was wondering if there is a way to open up a new email with all respective file names attached?

    Any help would be greatly appreciated and would save me a lot of time sending each individual file to the suppliers and looking for them.

    Thanks
    Kieran

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Google: Access VBA email attachment

    Review https://www.accessforums.net/showthread.php?t=64924

    You would need to open a recordset object in VBA for the filtered records, loop through the recordset to build the attachment set.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    kierandobson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Posts
    15
    Quote Originally Posted by June7 View Post
    Google: Access VBA email attachment

    Review https://www.accessforums.net/showthread.php?t=64924

    You would need to open a recordset object in VBA for the filtered records, loop through the recordset to build the attachment set.
    Thanks for your help, its a start but I don't even know what what you have just said means

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Review this https://stackoverflow.com/questions/...-directory-vba

    Ooops, this doesn't demonstrate looping through recordset.

    Do you want to send only one email with multiple attached files?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    kierandobson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Posts
    15
    Quote Originally Posted by June7 View Post
    Review this https://stackoverflow.com/questions/...-directory-vba

    Ooops, this doesn't demonstrate looping through recordset.

    Do you want to send only one email with multiple attached files?
    Ideally yes Just one email with all the filtered orders attached.

    Thanks

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you want to actually attach multiple files to email, something like:
    Code:
    Dim appOutLook As Outlook.Application, mailOutLook As Outlook.MailItem, rs As DAO.Recordset
    Set appOutLook = CreateObject("Outlook.Application")
    Set mailOutLook = appOutLook.CreateItem(olMailItem)
    Set rs = CurrentDb.OpenRecordset ("SELECT SubNo FROM tablename WHERE OrderDate = Date();")
    With mailOutLook
         Do While Not rs.EOF
             .Attachments.Add "file folder path\" & rs!SubNo & ".filename extension here"
             rs.MoveNext
         Wend
        .To = "email address here"
        .Subject = "subject text here"
        .HTMLBody = "message body here"
        .Display
    End With
    That could result in a lot of file attachments. An alternative is code to place the files into a Zip folder and then attach the single Zip folder.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    kierandobson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Posts
    15
    Quote Originally Posted by June7 View Post
    If you want to actually attach multiple files to email, something like:
    Code:
    Dim appOutLook As Outlook.Application, mailOutLook As Outlook.MailItem, rs As DAO.Recordset
    Set appOutLook = CreateObject("Outlook.Application")
    Set mailOutLook = appOutLook.CreateItem(olMailItem)
    Set rs = CurrentDb.OpenRecordset ("SELECT SubNo FROM tablename WHERE OrderDate = Date();")
    With mailOutLook
         Do While Not rs.EOF
             .Attachments.Add "file folder path\" & rs!SubNo & ".filename extension here"
             rs.MoveNext
         Wend
        .To = "email address here"
        .Subject = "subject text here"
        .HTMLBody = "message body here"
        .Display
    End With
    That could result in a lot of file attachments. An alternative is code to place the files into a Zip folder and then attach the single Zip folder.
    That 's an option if that would work. How would I go about that?

    Thanks

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Code:
    Dim appOutLook As Outlook.Application, mailOutLook As Outlook.MailItem, rs As DAO.Recordset, objApp As Object, strZip As String
    
    Set appOutLook = CreateObject("Outlook.Application")
    Set mailOutLook = appOutLook.CreateItem(olMailItem)
    Set rs = CurrentDb.OpenRecordset ("SELECT SubNo FROM tablename WHERE OrderDate = Date();")
    Set objApp = CreateObject("Shell.Application")
    
    'create empty zip folder
    'found this on web, no idea what the Print line does but if it isn't there, this won't work
    strZip = "folderpath\filename.zip"
    Open  strZip For Output As #1
    Print #1, "PK" & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1
    
    With mailOutLook
         Do While Not rs.EOF
               'double parens not in original example code but won't work without
               objApp.NameSpace((strZip)).CopyHere "file folder path\" & rs!SubNo & ".filename extension here"      'copy file into zip folder
               rs.MoveNext
         Wend
        .Attachments.Add strZip
        .To = "email address here"
        .Subject = "subject text here"
        .HTMLBody = "message body here"
        .Display
    End With
    Last edited by June7; 07-03-2017 at 06:52 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    kierandobson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Posts
    15
    Honestly I am so grateful for your help. I am going to have a go at both attempts but I am not entirely sure how to attach this to a button. I have tried just pasting this in the event on click, as below. Am I missing something?

    Thanks in advance

    Code:
    Private Sub EndDay_Click()
        Dim appOutLook As Outlook.Application, mailOutLook As Outlook.MailItem, rs As DAO.Recordset
        Set appOutLook = CreateObject("Outlook.Application")
        Set mailOutLook = appOutLook.CreateItem(olMailItem)
        Set rs = CurrentDb.OpenRecordset("SELECT SubNo FROM SupplierLookupQ WHERE OrderDate = Date();")
        With mailOutLook
            Do While Not rs.EOF
                .Attachments.Add "C:\Users\Ryan\Desktop\" & rs!SubNo & ".pdf"
                rs.MoveNext
             Wend
            .To = "email address here"
            .Subject = "subject text here"
            .HTMLBody = "message body here"
            .Display
        End With
     
    End Sub

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You need to replace "email address here", "subject text here", and "message body here" with whatever suits your situation.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    kierandobson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Posts
    15
    Good Morning
    I am receiving this error message when the button is clicked. Below is the debugger, any ideas?

    Thanks in advance
    Click image for larger version. 

Name:	error.PNG 
Views:	34 
Size:	7.5 KB 
ID:	29314
    Click image for larger version. 

Name:	Debugger.PNG 
Views:	34 
Size:	17.5 KB 
ID:	29315

  12. #12
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    can you paste the code so I can copy?

  13. #13
    kierandobson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Posts
    15
    Quote Originally Posted by Homegrownandy View Post
    can you paste the code so I can copy?
    Code:
    Private Sub EndDay_Click()
            Dim appOutLook As Outlook.Application, mailOutLook As Outlook.MailItem, rs As DAO.Recordset
        Set appOutLook = CreateObject("Outlook.Application")
        Set mailOutLook = appOutLook.CreateItem(olMailItem)
        Set rs = CurrentDb.OpenRecordset("SELECT SubNo FROM SupplierLookupQ WHERE OrderDate = Date();")
        With mailOutLook
            Do While Not rs.EOF
                .Attachments.Add "Z:\Purchasing\Private\Kieran\SUBS\PO_" & rs!SubNo & "_c1.pdf"
                rs.MoveNext
             Wend
            .To = "k.dobson93@gmail.com"
            .Subject = "subject text here"
            .HTMLBody = "message body here"
            .Display
        End With
    End Sub
    Thanks in advance

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You are using early binding, which requires the Outlook reference be selected in Tools/References.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    kierandobson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Posts
    15
    Quote Originally Posted by pbaldy View Post
    You are using early binding, which requires the Outlook reference be selected in Tools/References.
    Thanks, would you mind explaining a bit further please?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-16-2017, 08:34 PM
  2. Send Multiple csv attachments by email
    By shaunacol in forum Import/Export Data
    Replies: 16
    Last Post: 07-20-2015, 12:22 PM
  3. Send email with attachments not empty
    By JJCHCK in forum Programming
    Replies: 3
    Last Post: 09-23-2011, 10:29 AM
  4. Send email with attachments
    By rbiggs in forum Programming
    Replies: 12
    Last Post: 07-23-2011, 12:50 PM
  5. Send Report and Attachments in Email
    By Pimped in forum Programming
    Replies: 1
    Last Post: 06-21-2011, 02:51 AM

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