Select Multiple Items from Listbox to Attach in Email

Question

I've built a database that holds multiple files, such as user manuals. One of the forms I created in Access is a search field which uses queries to find the particular file the user's looking for. The search narrows the results into a list box, upon which double clicking will open the file for you. The results are also narrowed down into tabs based on the type of document. I've implemented a feature that if you single select the (file) result to highlight it and then click on a button, it inserts that file into a new message in MS Outlook. This works great but I want to select more than just one file in the same email. I've been searching everywhere online and can't seem to find the proper solution. I'll list my code below.

This first piece is coded in my search form.

Private Sub cmdEMail_Click()

Dim fpath As String

'Find out what tab user is on
Select Case Me!tabResults.Value
Case 0
    If IsNull(lstManResults.Column(5, lstManResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstManResults.Column(5, lstManResults.ListIndex)
    End If
Case 1
    If IsNull(lstBullResults.Column(5, lstBullResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstBullResults.Column(5, lstBullResults.ListIndex)
    End If
Case 2
    If IsNull(lstSubResults.Column(5, lstSubResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstSubResults.Column(5, lstSubResults.ListIndex)
    End If
Case 3
        If IsNull(lstPicResults.Column(5, lstPicResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstPicResults.Column(5, lstPicResults.ListIndex)
    End If
Case 4
    If IsNull(lstWarrResults.Column(5, lstWarrResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstWarrResults.Column(5, lstWarrResults.ListIndex)
    End If
Case 5
    If IsNull(lstPartResults.Column(5, lstPartResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstPartResults.Column(5, lstPartResults.ListIndex)
    End If
Case 6
    If IsNull(lstSchemResults.Column(5, lstSchemResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstSchemResults.Column(5, lstSchemResults.ListIndex)
    End If
Case 7
    If IsNull(lstAppResults.Column(5, lstAppResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstAppResults.Column(5, lstAppResults.ListIndex)
    End If
Case 8
    If IsNull(lstSpecResults.Column(5, lstSpecResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstSpecResults.Column(5, lstSpecResults.ListIndex)
    End If
Case 9
    If IsNull(lstInternalResults.Column(5, lstInternalResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstInternalResults.Column(5, lstInternalResults.ListIndex)
    End If
Case 10
    If IsNull(lstAddenSuppResults.Column(5, lstAddenSuppResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstAddenSuppResults.Column(5, lstAddenSuppResults.ListIndex)
    End If
Case 11
    If IsNull(lstVideoResults.Column(5, lstVideoResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstVideoResults.Column(5, lstVideoResults.ListIndex)
    End If
Case 12
    If IsNull(lstTechTipsResults.Column(5, lstTechTipsResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstTechTipsResults.Column(5, lstTechTipsResults.ListIndex)
    End If
Case 13
    If IsNull(lstArchiveResults.Column(5, lstArchiveResults.ListIndex)) Then
        Exit Sub
    Else
        fpath = lstArchiveResults.Column(5, lstArchiveResults.ListIndex)
    End If
End Select

EmailDoc fpath

End Sub

This code is the function I created to handle the email operations:

Function EmailDoc(ByVal fpath As String)

'Get Outlook if it isn't open already
Set outlookApp = CreateObject("Outlook.Application")
Set outlookItem = outlookApp.CreateItem(0)

If Err <> 0 Then
    'Outlook wasn't running, start it
    Set outlookApp = CreateObject("Outlook.Application")
    Started = True
End If

With outlookItem
    .to = ""
    .Subject = "Requested Document"
    .Body = "Thank you"
    .attachments.Add (fpath)

    .display

End With

End Function

Any help at all would be greatly appreciated.

score 0 · Accepted Answer · edited May 23 '17 at 10:28

0

Try these two links:

cycling through values in a MS Access list box

https://support.microsoft.com/en-us/kb/827423

They show two different ways of processing multiple listbox selections.

edited May 23 '17 at 10:28

Community

1
1

answered Jan 06 '16 at 21:40

PKatona

629
2
9
19

1

I think something like this will get you going in the right direction. http://www.fontstuff.com/access/acctut18.htm OR http://www.fontstuff.com/access/acctut19.htm – Jan 10 '16 at 15:45

Select Multiple Items from Listbox to Attach in Email

1 Answers1