Old question but new twist.... (1 Viewer)

[mgmercurio]

Ok first off...I am not asking for anyone to write the code for me..I don't expect anyone to donate their time or effort to that level...

However, I am asking for someone to point me in the right direction for me to do some research and figure out the answer.

What I need to find is "the happy path" when trying to come up with a working solution, for my users to preview an embedded thumbnail of a PDF file and then clicking the "select" box to choose which ever one they want to use, and then attaching it to an email with the use of the email button and the Multi-select list box of customers and thier emails. As shown in the attached screenshot.

The specifics:
I already have the email button working beautifully with some help from some very smart individuals from this forum an another access forum. The user can select one or many emails from the list and upon clicking the send email button, an outlook message window pops pre populated with all the needed info and addresses. So that part is working perfectly.

What I need help with is, the actual process of the user being able to click the thumbnail of the PDF file to pop up a window with the actual PDF so they can review it...then once they make their decision, they check the "attach" box and then choose the emails in the box, click the send mail button and the email pops up with the PDF attached.

A couple of key things to keep in mind...
1. I cannot use an Adobe ActiveX control in the form because for whatever reason, my company has restricted this use on my company owned laptop...(the machine I am using to build the database)
2. The PDF thumbnails you are seeing in the screen shot is achieved by placing a web browser control on the form and then coding the file path to the full document in the record source property. I am sure this is a very amateur way to do this and I am sure the experts on here have a much better way....but I am a complete noobie....just stumbling my way through...and this is what I came up with.

Thanks in advance,
-mgm

 

[MajP]

You could try this. This would be the same as double clicking a file in windows explorer.

Option Compare Database
Option Explicit

'=================================================
'API conversaion for 32/64-bit - checked 09/03/2019
#If VBA7 Then
    Public Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
#Else
    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If
'=================================================

Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 2            'Open Maximized
Public Const WIN_MIN = 3            'Open Minimized

Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

Dim gCount As Long

Function fHandleFile(stFile As String, lShowHow As Long)

'09/03/2019 - Updated for 64-bit
#If VBA7 Then
    Dim lRet As LongPtr
#Else
    Dim lRet As Long
#End If

Dim varTaskID As Variant
Dim stRet As String

    'First try ShellExecute
    lRet = ShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
            
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function

If you have the full path stored then call like
Call fHandleFile(strPath, WIN_NORMAL)
where strPath is a full path.

 

[mgmercurio]

You could try this. This would be the same as double clicking a file in windows explorer.

Option Compare Database
Option Explicit

'=================================================
'API conversaion for 32/64-bit - checked 09/03/2019
#If VBA7 Then
    Public Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
#Else
    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If
'=================================================

Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 2            'Open Maximized
Public Const WIN_MIN = 3            'Open Minimized

Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

Dim gCount As Long

Function fHandleFile(stFile As String, lShowHow As Long)

'09/03/2019 - Updated for 64-bit
#If VBA7 Then
    Dim lRet As LongPtr
#Else
    Dim lRet As Long
#End If

Dim varTaskID As Variant
Dim stRet As String

    'First try ShellExecute
    lRet = ShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
           
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
   
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function

If you have the full path stored then call like
Call fHandleFile(strPath, WIN_NORMAL)
where strPath is a full path.

ahhhhhhh so you are saying to place your function code in a module and then making a call to it from the double_click property of the thumbnail window...so when the user double clicks the thumbnail it will open the actual document....correct?

 

[arnelgp]

you can put the Path of each pdfs to a table.
add a Yes/No field.
when you open the form, set the value of this field
to all record to False.
add a code (update sql) to your checkbox, so that this
field is toggled true/false, depend on the value of the checkbox.

now on your email code, get a recordset from this table
where the yes/no fields are True.
loop through each record, and adding the Path to
the Attachment.

 

[mgmercurio]

you can put the Path of each pdfs to a table.
add a Yes/No field.
when you open the form, set the value of this field
to all record to False.
add a code (update sql) to your checkbox, so that this
field is toggled true/false, depend on the value of the checkbox.

now on your email code, get a recordset from this table
where the yes/no fields are True.
loop through each record, and adding the Path to
the Attachment.

ohhhh I like this approach. I totally get your logic here.... now I gotta go find the right code for each of the steps...but i do understand what you are saying.

First thing I'll do is create a table with all the PDF files (25 of them). Then Ill attach the form to that table by record source. Then I will figure out how to "OnLoad" set the value of the yes/no to false Then I will figure out the update code to attach to the check box to set the yes/no field to true or false depending on the check mark.

Then do a query of the recordset to get the True values looping through to each file path... then the final step is to actually attach all the TRUE filepaths to the email

correct?

 

[mgmercurio]

you can put the Path of each pdfs to a table.
add a Yes/No field.
when you open the form, set the value of this field
to all record to False.
add a code (update sql) to your checkbox, so that this
field is toggled true/false, depend on the value of the checkbox.

now on your email code, get a recordset from this table
where the yes/no fields are True.
loop through each record, and adding the Path to
the Attachment.

So I have been trying to follow your instructions on this and have made a bit of progress.

This is what I have done so far...

1. Created a table called tbl_Attachments with the following fields:
AttachmentID (PK) Autonumber
Category number (from one of my LookUpTables)
UpLoad Date Date/Time (this is to try an implement a basic versioning tracker)
PDFFile attachment (this the actual filepath to the attachment)
AttachFile Yes/No (this is the yes/no flag you instructed to implement)
See Screenshot 10.png
=====================

2. Successfully coded the LoadForm property to flag the AttachFile (Yes/No) field to false.
Here is the code I used:

Private Sub Form_Load()
' set AttchFile field default No
Me.AttachFile = False
End Sub

======================
3. Partially coded the AttachFile Check Box to set the value of the check box to true and inform the user they have selected this file to be attached to the email and to flip the YES/NO value to either True or False. However, I say partially because I have not been able to figure out how to connect this box with the corresponding record in the table. Here is the code I placed on the check box's ON Click Property:

Private Sub AttachFile_Click()
If Me.AttachFile = True Then
MsgBox "This file will be attached to your email"
Else
MsgBox "You have removed this file from your email"
End If

End Sub

Can you offer some direction on this?

=====================
Items still need help with...

1. I can't figure out how to code the "Source" property of the web browser control to dynamically display the correct PDF file according to the AttachmentID within the tbl_Attachment table. I know I can hard code the full path directly in the source property, but that would defeat the purpose of automatically pulling in the file. The file will change over time as new versions of the pdf come out. I am going to provide a front end loader on the database that will allow the end user to upload the correct PDF version into the Attachment table as new versions come out.

2. I cannot figure out what code to place on my email button to pull in the "TRUE" flags from all the records. This is the code I have on my button right now. As you can see, I don't have anything to even start this process.
Private Sub bt_SendEmail_Click()

Dim strEmail As String
Dim var As Variant

With Me.EmailListBx1
For Each var In .ItemsSelected
strEmail = strEmail & .ItemData(var) & ";"
Next
End With

'MsgBox strEmail

DoCmd.SendObject acSendNoObject, , , strEmail, , , "This is a test message subject", "This is the body of my email message. Hello World.", True


End Sub

Any help and/or guidance would be very appreciated.

Thank you!
-mgm

 

[bastanu]

Hi mgm,
I see multiple problems with your approach to date.
In your new table tbl_attachments you do not want the PDFFile field to be attachment data type, but rather the short text to store the path to the PDF file. Then you can code the web browser control to display it but using that field as the source.
Secondly, the Docmd.SendObject will not allow you to attach external files (your PDFs). So you will need to replace that with a VBA function that automates Outlook.
Here is something I use (assuming you use Outlook, if not look for sending emails using CDO):

Function vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)
    Dim OutApp As Object
    Dim OutMail As Object
 
 
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon 
 
    Set OutMail = OutApp.CreateItem(0)
 
    OutMail.To = sTo
    If sCC <> "" Then OutMail.CC = sCC
    If sBcc <> "" Then OutMail.BCC = sBcc
    OutMail.Subject = sSubject
    If sBody <> "" Then OutMail.HTMLBody = sBody 

    OutMail.Attachments.Add (sAttachment)

    OutMail.Display  'Send | Display
'    OutMail.Inspector.Activate
    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

Cheers,
Vlad

 

[mgmercurio]

Hi mgm,
I see multiple problems with your approach to date.
In your new table tbl_attachments you do not want the PDFFile field to be attachment data type, but rather the short text to store the path to the PDF file. Then you can code the web browser control to display it but using that field as the source.
Secondly, the Docmd.SendObject will not allow you to attach external files (your PDFs). So you will need to replace that with a VBA function that automates Outlook.
Here is something I use (assuming you use Outlook, if not look for sending emails using CDO):

Function vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)
    Dim OutApp As Object
    Dim OutMail As Object


    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon

    Set OutMail = OutApp.CreateItem(0)

    OutMail.To = sTo
    If sCC <> "" Then OutMail.CC = sCC
    If sBcc <> "" Then OutMail.BCC = sBcc
    OutMail.Subject = sSubject
    If sBody <> "" Then OutMail.HTMLBody = sBody

    OutMail.Attachments.Add (sAttachment)

    OutMail.Display  'Send | Display
'    OutMail.Inspector.Activate
    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

Cheers,
Vlad

Thank you Vlad,

Yes...I saw the same thing you did and I did change the field in the table to short text and have stored the full path there. The field name is caled DocPath. I can now attach the stored path to the web browser object and it works. However, I don't see how I can make the web browser pull up the file path for a specific record. I am using AttachmentID as the unique value for each record.

As you can tell by the attached screenshot, I am not doing this correctly. I don't where / how to use the expression wizard to code the object directly to specific record of the table.

Any thoughts?

As for your recommendation with using OutlookAutomation....I see what you are saying and I am going to dig into what you have provided to see how I can attach it to my email button. The two important things with using this, is 1. To be able to select multiple email addresses from the email box and to attach the selected PDF files.

Thanks for your input! It is great appreciated and I will dig into this right now and report back.

Thanks again,
-mgm

 

[bastanu]

For your first question regarding the path, just use a dlookup in the control source of the web browser control:
=dLookup("[PDFFile]","[tbl_Attachments]","[AttachementID]=" & [AttachmentID])

As for the second just keep you For Next loop to gather the emails into your sEmail variable then just pass that to function I posted in your desired position (To or Bcc if you want the recipients no not see the others) along with the path for the attachment. The function I gave you only takes one attachment but can be easily modified to accomodate multiple ones by using Split() with the sAttachment variable if it were to contain multiple paths separated by comma or any other character.

Cheers,
Vlad

 

[mgmercurio]

*****UPDATE*****
Vlad, I took your suggestion and it worked. I put the following code in my object source property and it worked! Thank you!

=DLookup("[DocPath]", "tbl_Attachments", "AttachmentID = 1")

-mgm

This is awesome news and it puts me on the right track...but one last question....

I don't see where in the DLookup line that it actually ties the Web browser control to one specific record? I am going to have 5 browser controls all on the same form pointing to a specific record number in the table. I am sure I just need to denote the record number somewhere....but I dont see where? Sorry for being thick headed here....

and thank you for the OA function...yes I see where that would work perfectly. I will give it a go and report back...

Thanks again!
-mgm

For your first question regarding the path, just use a dlookup in the control source of the web browser control:
=dLookup("[PDFFile]","[tbl_Attachments]","[AttachementID]=" & [AttachmentID])

As for the second just keep you For Next loop to gather the emails into your sEmail variable then just pass that to function I posted in your desired position (To or Bcc if you want the recipients no not see the others) along with the path for the attachment. The function I gave you only takes one attachment but can be easily modified to accomodate multiple ones by using Split() with the sAttachment variable if it were to contain multiple paths separated by comma or any other character.

Cheers,
Vlad

 

[bastanu]

=dLookup("[PDFFile]","[tbl_Attachments]","[AttachementID]=" & 1)
=dLookup("[PDFFile]","[tbl_Attachments]","[AttachementID]=" & 2)
....
Cheers,

 

[bastanu]

But would probably much nicer to only have one bigger web browser control and either a subform or listbox showing the PDFs and dynamically setting the source as in my original post (with some tweaks to point to whatever control type you choose to show the PDFs).

Cheers,

 

[mgmercurio]

But would probably much nicer to only have one bigger web browser control and either a subform or listbox showing the PDFs and dynamically setting the source as in my original post (with some tweaks to point to whatever control type you choose to show the PDFs).

Cheers,

Yes understood...Im going to play around with that approach and see what I can do...thank you!

 

[mgmercurio]

I have been trying for a while and I can't seem to figure out how to use the "AttachPDF" checkbox to update the value in the AttachPDF (Yes/No) field of the tbl_Attachments table.

Arnelgp made a great suggestion earlier...but I am unable to implement.

I have 5 unbound checkboxes named:
cBx_AttachPDF 1 through 5
I cant seem to be able to programmically tie the check box to the AttachPDF field in the tbl_Attachments table uniquely. Meaning I can't program each checkbox to the specific record in the table

I tried using a DSLookup statement in each check box record source...

=DLookUp("[DocPath]","tbl_Attachments","AttachmentID = 1"
=DLookUp("[DocPath]","tbl_Attachments","AttachmentID = 2"
=DLookUp("[DocPath]","tbl_Attachments","AttachmentID = 3"
=DLookUp("[DocPath]","tbl_Attachments","AttachmentID = 4"
=DLookUp("[DocPath]","tbl_Attachments","AttachmentID = 5"

it worked as far as connecting the checkbox to the field, but since it bound the expression to the record it also made it non-editable on the form.

Where am I screwing up here?

Thanks!
-mgm

 

[bastanu]

The easiest way would be to implement a subform bound to tbl_Attachment that exposes the AttachPDF field (Yes\No bound to check box) from the table directly to the user (by the way I recommend making this table a local one so users don't interfere with each others selections). No code required....

Cheers,

 

[arnelgp]

suggestion on post #14.

remove the Expression on the checkbox's ControlSource.
instead, add code on the Load event of the form:

'assumming your checkbox control is "serialized
'meaning their name is Chkbox01, Chkbox02, Chkbox03, etc..
Private Sub Form_Load()
    Dim i As Integer
    'the sample also assume you have 4 checkbox
    For i = 1 To 4 Step 1
        Me("Chkbox" & Format(i, "00")) = DLookup("TheCheckboxField", "tbl_Attachments", "AttachmentID = " & i)
    Next
End Sub

'you also add this function to your Form
'this will update tbl_Attachments when you
'click on the Checkbox
Public Function fncUpdateTblAttachments()
    Dim i As Long
    Dim Ctl As Control
    Set Ctl = Screen.ActiveControl
    i = CLng(Right$(Ctl.Name, 2))
    CurrentDb.Execute _
        "Update tbl_Attachment Set TheCheckboxField = " & Ctl.Value & " Where AttachmentID = " & i
End Function

'!!!
Now, on Each Checkbox control's After Update Event, you type:

=fncUpdateTblAttachment()

 

[bastanu]

@arnelgp, just curious what would be the problem with just using the bound checkboxes instead of all the code?

Cheers,
Vlad

 

[arnelgp]

there is no wrong, except how to display them (including the attachment) horizontally.

 

[mgmercurio]

Thank you for this Arnelgp... I appreciate the assistance. I placed all the code in the right spots...but one issue...upon clicking the attachpdf box (by way of the afterupdate function you provided) I am getting a message stating access can not find the function. I have placed this function in the standard module of the database. I have even tried to create a new module and place it in there and still no luck...

Any ideas?

Also, as far as the bound / unbound debate.... I agree with you Vlad...it would be much easier to use a subform and bound the checkbox to the form...but as Arnelgp pointed out.... the issue is having all pdf "previews" available in one window pane and having the ability to click attach from that one window. If I use a sub form (continuous form) it would make my users have to flip through each record....which...I am really not opposed to ... I have attached a couple screenshots to explain.

by the way...I want to take a second and thank you folks for all the help! You make this look so easy with your very obvious skilled expertise...and there is no way I would be this far along without your guidance. I really do appreciate it.

-mgm

 

[bastanu]

In the AfterUpdate event(s) I think you want Call fncUpdateTblAttachment(), not =fncUpdateTblAttachment().

Cheers,