Solved

Send and Infopath form for a specific record from an Access database form

Posted on 2016-07-25
6
94 Views
Last Modified: 2016-08-01
In my Access 2010 Inventory database I have a splitform to show my inventory list.
From a specific record in the form view I wish to send an InfoPath form to email.
In that InfoPath form recipients will fill in blank or missing data and update existing data in all fields.

I have created the InfoPath form, and pretty much everything I need.
What I am struggling with is the vba code to push the info path form to email on just that record from the open form in access.

Can anyone help please.

I am proficient in Access and can edit VBA code, but I am not fluent in writing code.

Thankks
0
Comment
Question by:Careca
  • 4
  • 2
6 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41727881
I toyed briefly with InfoPath but ultimately abandoned it prior to implementation because Microsoft deprecated the feature.  If you have any plans to update Office (there are already two versions newer than O2010), you might want to consider an alternative.
0
 

Author Comment

by:Careca
ID: 41728987
Hi Pat, thank you for your comment. Yes I know that InfoPath is not the future, however I am in the hands of my client who will be using it for the foreseeable future so I need to use InfoPath.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41729845
1
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Careca
ID: 41737027
I worked around it and created an excel form with a named range for the export data.
As infopath will not be well supported, this will suit us best. Thank you
0
 

Author Closing Comment

by:Careca
ID: 41737029
I worked around it and made an excel form with named range for the export data
0
 

Author Comment

by:Careca
ID: 41737081
Here's the code I used incase anyone looking for similar work around ideas..

Private Sub SendTQ_Click()
Dim TQXLattachName As String
Dim TQPicAttachName As String
Dim Picfile As String
TQPicAttachName = Me!PicturePath
TQXLattachName = Me!XlFormPath
Picfile = Dir(TQPicAttachName) 'sets the variable file path of the picture file to be sent

    If IsNull(Me![End User]) Then
        MsgBox "You must select an End User"
        Exit Sub
    Else
        If Me.Dirty Then 'DSave any edits first - if true.
        Me.Dirty = False
        End If
    End If
    On Error GoTo Send_TQ_Err
   
    'EXPORT THE DATA TO A NAMED RANGE ON THE TEMPLATE FILE
    DoCmd.OpenQuery "Qry_End_User_TQ", acViewNormal, acEdit
    DoCmd.TransferSpreadsheet acExport, 9, "Qry_End_User_TQ", TQXLattachName, True, "ExportTQ"
    DoCmd.Close acQuery, "Qry_End_User_TQ"
   
   
    ' SET Outlook APPLICATION OBJECT.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
   
    ' CREATE EMAIL OBJECT.
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)
 If Picfile <> "" Then ' checks to see if a picture exists for this record and jumps to the next WITH statement if not
    With objEmail
        .to = Forms!Fm_Data_Entry![End User email]
        .Subject = "NMA Inventory Reduction Project - Action Required for TQ on " & Forms!Fm_Data_Entry![Symbol No] & " - " & Forms!Fm_Data_Entry!Desc1
        .Body = "Sir," & vbNewLine & "you have been identified as the intended end user of this slow moving stock item." & vbNewLine & vbNewLine & "Please review the attached TQ form and complete the yellow highlighted areas and return your recommended actions and comments at your earliest convenience." & vbNewLine & "Please do make every effort to improve the JDE data on this high value item, particularly in setting appropriate Criticality and QA Codes and Min/Max Values." & vbNewLine & "Note: to qualify for DSE an item must have an individual unit cost of over $5000." & vbNewLine & "Descriptive dropdown lists are included in the form for your assistance, please do not hesitate to call for any clarification." & vbNewLine & "Thank you in advance for your timely response." & vbNewLine & "Kind regards"
        .Attachments.Add (TQXLattachName)
        .Attachments.Add (TQPicAttachName)
        .FlagStatus = olFlagMarked
        .FlagRequest = "Follow up"
        .Display        ' DISPLAY MESSAGE.
    End With
    ' CLEAR.
    Set objEmail = Nothing:    Set objOutlook = Nothing
Else
    If MsgBox("There is no photograph associatead with this item, do you still want to send just the TQ form without the photo?", vbYesNo, "No Photograph!") = vbYes Then
    With objEmail
        .to = Forms!Fm_Data_Entry![End User email]
        .Subject = "NMA Inventory Reduction Project - Action Required for TQ on " & Forms!Fm_Data_Entry![Symbol No] & " - " & Forms!Fm_Data_Entry!Desc1
        .Body = "Sir," & vbNewLine & "you have been identified as the intended end user of this slow moving stock item." & vbNewLine & vbNewLine & "Please review the attached TQ form and complete the yellow highlighted areas and return your recommended actions and comments at your earliest convenience." & vbNewLine & "Please do make every effort to improve the JDE data on this high value item, particularly in setting appropriate Criticality and QA Codes and Min/Max Values." & vbNewLine & "Note: to qualify for DSE an item must have an individual unit cost of over $5000." & vbNewLine & "Descriptive dropdown lists are included in the form for your assistance, please do not hesitate to call for any clarification." & vbNewLine & "Thank you in advance for your timely response." & vbNewLine & "Kind regards"
        .Attachments.Add (TQXLattachName)
        .FlagStatus = olFlagMarked
        .FlagRequest = "Follow up"
        .Display        ' DISPLAY MESSAGE.
    End With
    End If
End If

    ' CLEAR.
    Set objEmail = Nothing:    Set objOutlook = Nothing

Send_TQ_Exit:
    DoCmd.Close acQuery, "Qry_End_User_TQ"
    Exit Sub

Send_TQ_Err:
        MsgBox Error$
    Resume Send_TQ_Exit
[/code][/code]
End Sub
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
Read this checklist to learn more about the 15 things you should never include in an email signature.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now