Results 1 to 13 of 13
  1. #1
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77

    Attaching attachments to with current record to email???

    Currently, I have a code working that will attach my current record that is populated with data into an email in a PDF format. However, I can't get the attachments on the current recordset to go along in the email. Any suggestions? Below is my CODE:
    Code:
    Private Sub BTN_SUBMIT_Click()
    Dim strSPCcode As String
    Dim strReport As String
    Dim strEmail As String
    Dim strCC As String
    Dim strMSG As String
    strMSG = "DC_7"
    strCC = Me.ptEMAIL & "," & Me.dcxEMAIL & "," & "Email@email.com"
    strEmail = Me.TO
    strSPCcode = Me.conNum
    'Name of Report goes here
    strReport = "RPT_7"
    Me.Refresh
    DoCmd.OpenReport strReport, acViewPreview, , "ID= " & Me!ID
    Reports(strReport).Visible = False
    '''ADD New Record'''''''''''''''''''''''''''''''''''''''''''''''
    DoCmd.GoToRecord , , acNewRec
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''Structure of the Email ''''''''''''''''''''''''''''''''''''''
    DoCmd.SendObject acSendReport, _
                        strReport, _
                        acFormatPDF, _
                        strEmail, _
                        strCC, , _
                        strMSG & "=" & " " & strSPCcode
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    DoCmd.SendObject
    DoCmd.Close acReport, strReport
    End Sub


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Objects saved in table must first be saved out to external file location then attached to email. Cannot use SendObject method to attach files. Have to use Outlook automation.

    An attachment type field is a multi-value field. Manipulating multi-value fields in VBA requires using parent and child recordset objects.

    These are common topics and there are multiple threads discussing them. Search forum or web.

    Saving objects in table can quickly use up Access 2GB size limit.
    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
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by June7 View Post
    Objects saved in table must first be saved out to external file location then attached to email. Cannot use SendObject method to attach files. Have to use Outlook automation.

    An attachment type field is a multi-value field. Manipulating multi-value fields in VBA requires using parent and child recordset objects.

    These are common topics and there are multiple threads discussing them. Search forum or web.

    Saving objects in table can quickly use up Access 2GB size limit.
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''
    Is there anything that can be done with VBA code to pull the attachment into the email?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I already stated how that would be done with VBA. The file must first be saved to external folder. If the objects are in an attachment type field, VBA would manipulate multi-value field using parent and child recordset objects to export the file to folder, attach to email, delete the file from 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.

  5. #5
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77

    Question

    Quote Originally Posted by June7 View Post
    I already stated how that would be done with VBA. The file must first be saved to external folder. If the objects are in an attachment type field, VBA would manipulate multi-value field using parent and child recordset objects to export the file to folder, attach to email, delete the file from folder.
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''
    Code:
    Function SaveAttachment()
    Dim db As DAO.Database
     Dim rst As DAO.Recordset2
     Dim rstAttachment As DAO.Recordset2
     Dim fld As DAO.Field2
     Dim strPath As String
     Dim intz As Integer
     
    Set db = CurrentDb
         Set rst = db.OpenRecordset("Data_TABLE_TBL", dbOpenDynaset)
         rst.FindFirst "ID = " & Me!ID
          Set rstAttachment = rst.Fields("Attachments_FILES").Value
         Set fld = rstAttachment.Fields("Filedata")
         strPath = CurrentProject.Path & "\Attach\" & rstAttachment.Fields("Filename")
         On Error Resume Next
         Kill strPath & "\Attach\"
         On Error GoTo 0
     
         fld.SaveToFile strPath
     
     rstAttachment.Close
     rst.Close
     Set rstAttachment = Nothing
     Set rst = Nothing
     Set db = Nothing
     
    End Function
    could the above be a step in the right direction?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Suggest opening a filtered recordset instead of using FindFirst.

    Set rst = db.OpenRecordset("SELECT Attachments_Files FROM Data_TABLE_TBL WHERE ID = " & Me!ID, dbOpenDynaset)

    Are you going to call that procedure from another procedure with the Outlook automation code?
    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
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by June7 View Post
    Suggest opening a filtered recordset instead of using FindFirst.

    Set rst = db.OpenRecordset("SELECT Attachments_Files FROM Data_TABLE_TBL WHERE ID = " & Me!ID, dbOpenDynaset)

    Are you going to call that procedure from another procedure with the Outlook automation code?
    '''''''''''''''''''''''''''''''''''''''''''''''''' '

    The current desired direction is Current Record on Form to be printed to PDF format, (which I can do, and is working); Than have all the selected attachments move along with the record to the newly populated Email. Currently, I'm stuck. I can't seem to get the attachments to go to temp folder than called back into the newly populated email. At least thats process I'm trying right now. I'll will definitely try that suggestion above! I'll get back to you ASAP. I'm really swamped today.

  8. #8
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Also, on a related matter to this attachment issue. Is it also possible to include a digital Signature along with the attachments? I think the process could be fairly similar?? Maybe something/function like below??
    Code:
    Function SignText (ByVal Inputx as String, ByVal SignatureName As String) As String
     
       Dim Signer as Object
     
          Set Signer = CreateObject("Scripting.Signer")
          SignText = Signer.Sign(".VBS", Inputx, Signaturename)
     
    End Function
     
     
    Function VerifyText(ByVal Inputx As String) As Boolean
     
       Dim Signer as Object
     
          Set Signer = CreateObject("Scripting.Signer")
          VerifyText = Signer.Verify(".VBS", Inputx, False)
     
    End Function
    
    Private Sub Text0_GotFocus()
        Call SignText("Inputx", "SignatureName")
        
    End Sub
    Than call the function to the field where the digital signature is and have that populate into the for as it sent over to a PDF format?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So is the code saving file to external location working?

    You don't yet show any Outlook automation code for creating the email and attaching file.

    Google: Access VBA Outlook digital signature
    Here is one https://stackoverflow.com/questions/...ure-in-outlook
    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.

  10. #10
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by June7 View Post
    So is the code saving file to external location working?

    You don't yet show any Outlook automation code for creating the email and attaching file.

    Google: Access VBA Outlook digital signature
    Here is one https://stackoverflow.com/questions/...ure-in-outlook
    ''''''''''''''''''''''''
    Hello, many thanks again for your help! =)
    Yes, my email code works great! it also takes the current record and puts into a PDF format. However, to answer your question about the attachment attempt Its not working.... I can't figure out what the problem is. its giving me a Run-Time error 3345. Unknown or invalid field reference 'ID'.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Post your complete procedure.

    If it is saving the PDF, then why would attaching file to Outlook mail object need to reference ID?
    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.

  12. #12
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by June7 View Post
    Post your complete procedure.

    If it is saving the PDF, then why would attaching file to Outlook mail object need to reference ID?
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
    The code I posted on my first post is my main code. The send segment I post is what I'm trying to work into that code. As of now, I'm just getting error messages and not even able to debug it correctly. To try to explain why I'm utilizing the ID, with my limited ability to do so, is to make sure that the attachments are correctly bound from the correct Current Record state. I hope this make sense, I'm still trying to wrap my head around this VBA structures. In the form I have a field that allows attachments to the tables to be stored, as I populate the PDF with the current record, I want the attachments to go along to the email as well. Many thanks for continue to work on this with me!

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Cannot use SendObject. Must use Outlook automation. You have not posted any procedure that uses Outlook automation.

    Since cannot use SendObject, you will also have to save the report out as a PDF file along with the file from attachment field and then attach both files to the Outlook email object.

    If the data in the report is simple enough, might instead be able to build a string of text and data and use that as the email body.

    So your process has 3 stages.

    1. open report and save as PDF and close report OR build string of text and data from the record to use as email body

    2. save attachment from record to external file location

    3. open Outlook objects and attach files to email and send

    All 3 pieces can be within the button click procedure or can be separate procedures called by the button click. If you place all the code behind the form then the Me. prefix will work. If a procedure is in a general module then you should pass the ID value by argument.
    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.

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

Similar Threads

  1. Email a report of the current record.
    By jonudden in forum Reports
    Replies: 4
    Last Post: 08-21-2023, 07:37 AM
  2. Attaching Spreadsheet to email without saving it
    By MTSPEER in forum Import/Export Data
    Replies: 3
    Last Post: 10-11-2016, 10:00 AM
  3. how to send attachments on current record open
    By sspreyer in forum Programming
    Replies: 10
    Last Post: 11-18-2013, 12:05 PM
  4. attaching files to an email via a MACRO
    By bobandtom in forum Macros
    Replies: 22
    Last Post: 10-29-2013, 10:25 AM
  5. Email to Current Record Only
    By cbrsix in forum Programming
    Replies: 4
    Last Post: 09-27-2013, 08:14 AM

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