Results 1 to 13 of 13
  1. #1
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    114

    How to retrieved data in access from Outlook-E-mail attachment

    Hi,



    I am hoping to find out the best way or the easiest way to update a certain table.field in my 2010 access database, I have just heard of MS Infopath but never used it, or is there another easier to implement solution. eg Ms.Word


    The scenario we have at the moment is our local office sends an e-mail request to our Head Officer(remotely) requesting for H/Office to auto-generate/ create an invoice number within there H/O Finance-System, for a work-order that is created within our local database. I need a way of getting this Auto-Generated number into our local MS Access database.

    What I would like is for this to be fully automated:

    So far I have created a "Form_NewWorkOrder" that then from a button on the form creates a "pdf" that holds all the details off the current open form= "Form_NewWorkOrder" , then this PDF, gets attached to an E-mail in Outlook-2010, and then gets sent on to head Office requesting for this unique H.O Number, this situation happens all day every day, so its to cumbersome to cut and paste the head office number into the relevant record with each occurrence (although this is whats been done at the moment till a better solution is found).

    Any thoughts or similar examples on the best approach to solving this would be much appreciated!

    thanks in advance
    Last edited by gint32; 05-15-2017 at 10:01 PM. Reason: Attempted to edit the header spelling typo but will not let me

  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
    48,855
    VBA manipulation of PDF is tricky because it is not a MS product and not part of Office automation. Probably easier to program the email body to include the workorder info then code extract from the email body.

    A lot of users disappointed that MS removed the Collect Data utility with Access 2013. Sounds like your situation could have used it.
    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
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    114
    Thanks for that, Hence I have amended my "send mail with attachment" rather than a PDF as below, so I can better manage within MS Word, Then hopefully someone can suggest a viable solution, Although I now have an issue with the below as it now pulls all the records into the generated worddoc that gets attached to the mail...any suggestion on how to just get one record? ie. from the current record displayed on the open form.
    Note : previously "RFW_rpt_single_request" ran on a query whereas the RTF seems to pull it from somewhere else! as it retrieves all the records(page by page). So I end up with 100's of pages in the RTF
    Thanks in advance again!



    DoCmd.SendObject acSendReport, "RFW_rpt_single_request", acFormatRTF, _....

  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
    48,855
    Have to apply filter criteria to form or report so only the one record is exported.

    DoCmd.OpenReport ...
    DoCmd.SendObject ...
    DoCmd.Close ...
    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
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    114

    correct syntax

    Quote Originally Posted by June7 View Post
    Have to apply filter criteria to form or report so only the one record is exported.

    DoCmd.OpenReport ...
    DoCmd.SendObject ...
    DoCmd.Close ...
    any chance you can elaborate a little for me as i m still struggling,
    as with one permutation(syntax)I get an option for filtering and the other i don't, and oddly enough the place I really need it is where I don't have the filtering option

    Code:
    DoCmd.OpenReport "RFW_rpt_single_request", acViewPreview, , strWhere1
    DoCmd.Minimize
    this works but and pulls only one record but not editable( so I need it in RTF )

    Code:
    DoCmd.SendObject acSendReport, "RFW_rpt_single_request", acFormatRTF, _
        strEmail, , , "For " & strAll & ",.... " & "Please find attached an initial request for a Dep-Invoice Num from : " & myname & " " & myphone & " Our System ID # " & Me.Prefix & vbCrLf & MymessageBody & vbCrLf & MyCCmessage & vbCrLf & vbCrLf & Mydownload & vbCrLf & vbCrLf & "                 " & URLAddress
    the above gives me a rtf attachment just as I need but multiple sheet of the same record

    Code:
    DoCmd.SendObject acSendForm, "RFW_frmOrders", acFormatRTF, strEmail, _
    , , "For " & strAll & ",.... " & "Please find attached an initial request for a Dep-Invoice Num from : " & myname & " " & myphone & " Our System ID # " & Me.Prefix & vbCrLf & MymessageBody & vbCrLf & MyCCmessage & vbCrLf & vbCrLf & Mydownload & vbCrLf & vbCrLf & "                 " & URLAddress
    the above gives me all the records in the datebase from the specific table...with no filter for one record

    Code:
    DoCmd.SendObject acSendReport, "RFW_rpt_single_request", acFormatPDF, strEmail, _
    , , "For " & strAll & ",.... " & "Please find attached an request for a final invoice number for fullpayment from : " & myname & " " & myphone & " Our System ID # " & Me.Prefix & vbCrLf & MymessageBody & vbCrLf & MyCCmessage & vbCrLf & vbCrLf & Mydownload & vbCrLf & vbCrLf & "                 " & URLAddress
    The above gives me a pdf with again multiple sheet of the dame record(and is not an RTF) so its of no use either

    So if you don't mind , could you help with the correct syntax for pulling one filtered record in RTF attached to an e-mail ..thanks ever so much

  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
    48,855
    I did a test of SendObject as acFormatRTF of filtered report and it works for me. Only the single record is exported. The Word document is editable after I click the "Enable Editing" on the Protected View warning.

    Example:

    Docmd.OpenReport "Compensation", acViewPreview, , "UmpID='Bailey'"
    DoCmd.SendObject acSendReport, "Compensation", acFormatRTF, "email here", , , "Test", "Test"
    Docmd.Close acReport, "Compensation"
    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
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    114

    How to retrieved data in access from Outlook-E-mail attachment

    Quote Originally Posted by June7 View Post
    I did a test of SendObject as acFormatRTF of filtered report and it works for me. Only the single record is exported. The Word document is editable after I click the "Enable Editing" on the Protected View warning.

    Example:

    Docmd.OpenReport "Compensation", acViewPreview, , "UmpID='Bailey'"
    DoCmd.SendObject acSendReport, "Compensation", acFormatRTF, "email here", , , "Test", "Test"
    Docmd.Close acReport, "Compensation"
    Sorry But the closest I can get to what I want to achieve is an RFT with a duplicate/repeated of what I want, but repeated for each record in the table within that word doc, which is vba option 3 from below

    Code:
      Stop
          
       'appended '''''May17th2017 ref https://www.accessforums.net/showthread.php?t=66136&p=357784#post357784
    
       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       'Option 1     '''when report is open and minimised i get an PDF with Blank fields in the attachment
        
        DoCmd.OpenReport "RFW_rpt_single_request", acViewPreview, , Prefix
          DoCmd.Minimize '''when report is open and minimised i get an PDF with Blank fields in the attachment
            DoCmd.SendObject acSendReport, "RFW_rpt_single_request", acFormatRTF, "email@here.Com", , , "Test", "Test_Mailing" 'DoCmd.OpenReport "RFW_rpt_single_request", acViewReport, , Prefix
       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
      
    
          'Option 2 '''when report is open and minimized i get an RTF with Blank fields in the attachment
          
          DoCmd.OpenReport "RFW_rpt_single_request", acViewPreview, , Prefix
            DoCmd.Minimize '''when report is open and minimized i get an RTF with Blank fields in the attachment
             DoCmd.SendObject acSendReport, "RFW_rpt_single_request", acFormatPDF, "email@here.Com", , , "Test_Email", "Test_Mailing"
                '''The above line gives me a pdf with the details I need(but is PDF)
       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
           
           'Option 3  'if I don't run the docmd.openreport and skip to (step over), I get an RTF with the details I wish but 100 of Pages)...which is great!!! of the same within repeated 
           
           'DoCmd.OpenReport "RFW_rpt_single_request", acViewPreview, , Prefix
             'if I don't run the above docmd.openreport, I get an RTF with the details i wish but 100 of Pages) of the same within
    
               DoCmd.SendObject acSendReport, "RFW_rpt_single_request", acFormatRTF, "email@here.Com", , , "Test_Email", "Test_Mailing"
                '''The above line gives me a Email with an RTF with only one sheet(record) but fields are empty
                
                
                
       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

  8. #8
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    114

    Help needed with attaching a record to an e-mail in RTF

    I have taken the liberty to attach my sample Database for anyone to look at and advise of what I am doing wrong, so far nobody seems to know whats wrong with my code.

    Thanks for any suggestions that anyone has on this forum!
    Attached Files Attached Files

  9. #9
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    114
    This issue is now also posted at the following: http://www.utteraccess.com/forum/ind...0#entry2646127

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,855
    You are using FilterName argument of OpenReport. I have NEVER used this argument. I use WHERE argument which is equivalent to WHERE clause in SQL statement.

    So in your code could be:

    DoCmd.OpenReport "RFW_rpt_single_request", acViewPreview, , , "[SystemID] = '" & Me.Prefix & "'"

    However, there is a variation between how you construct the Prefix and SystemID values.

    You have as Prefix: [STCode] & Format([Application_ID],"000000")

    You have as SystemID: [STCode] & Format([Application_ID],"00000")

    Note that the first has 6 zeros and the second has 5. The filter criteria will not find matching record.

    Don't you want to output a single application record? Suggest you include Application_ID in both form and report RecordSource and set the criteria with:

    DoCmd.OpenReport "RFW_rpt_single_request", acViewPreview, , , "[Application_ID] = " & Me!Application_ID

    You have a query object for the form and another query object for the report. They are both constructed with the same tables and join types. Why not use the same query for both?
    Last edited by June7; 05-17-2017 at 06:54 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.

  11. #11
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    114

    Still got the same issue with docmd.send in RFT-format

    Thanks for the advice, I have done as you suggested and I am really appreciative of that.

    Currect me if I'm wrong, but I didn't have an issue with previewing side of any record that I wanted to send.

    The Issue I currently have is with the sending part of that record in RTF format.

    So I need help with this line of code(below). As I still get the same results as before even with your changes made.
    Code:
    DoCmd.SendObject  acSendReport, "RFW_rpt_single_request", acFormatRTF, "email@here.Com", ,  , "Test_Email", "Test_Mailing"
    The above line is where I need the help (telling it to filter once only) as it still gives me an multiple report with repeated System_id/Application_id for every record in the table

    Thanks though for taking the time to read and suggest.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,855
    The SendObject command doesn't filter anything. The filtering has to be done in the OpenReport command. There is nothing wrong with that SendObject code.

    I did the following and nothing else in your db.

    1. for the report query, set the Application_ID to be available for reference, had to check the Show box in the design grid
    2. removed value from report Filter property and saved the report
    3. put 3 lines of code behind a button which works perfectly, the Word doc is only one page showing "Other Workshop"

    Code:
    DoCmd.OpenReport "RFW_rpt_single_request", acViewPreview, , "Application_ID=" & Me!Application_ID
    DoCmd.SendObject acSendReport, , acFormatRTF, "used my email here", , , "Test", "Test"
    DoCmd.Close
    So take that simple working code and build from there. Debug.

    Opening the report without filter is 13 pages, however, every page shows "OTH_000048" because the textbox references form textbox, should not reference form textbox, should be bound to SystemID field in the report RecordSource
    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.

  13. #13
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    114
    Thanks again for the advice, Now I can get on to the next phase of development

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

Similar Threads

  1. Replies: 2
    Last Post: 11-01-2015, 06:24 PM
  2. Replies: 6
    Last Post: 10-19-2015, 06:07 PM
  3. Replies: 0
    Last Post: 12-15-2014, 08:18 AM
  4. Replies: 1
    Last Post: 01-27-2011, 04:02 PM
  5. Replies: 3
    Last Post: 07-30-2009, 07:12 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
  •  
Tech Forums: Microsoft Office Forums