Send report by Email using a Form

[Tfrank]

I have a form which has a list box where the users can select multiple email adresses for sending a report as an attachment. When viewing a report I would like to have a button on the toolbar for emailing this opened report. When the email button is pushed it opens up my email form where the users can select the email addresses and then a send button which opens up there email system. All of this part works great, but I do not know how to assign the currently opened report to the email. It is easy to hardcode a report name to do this, but I would like to use this email form for sending out many different reports. Any help on how to attach the currently opened report would be greatly aprreciated.

Thanks for the help,
Tfrank

 

[GKProgrammer]

Hi Tfrank,

Sounds like you will only have one report open (the report you wish to find the name of), so the below code will show you how to bet the name of an opened report. On the condition that it is the only one opened.

If Reports.Count = 1 Then
MsgBox Reports(0).Name
End If


Use the Reports(0).Name instead of having to hardcode the name. Let me know if this helps you.



Regards,
gkprogrammer

 

[Tfrank]

Thanks!!! gkprogrammer,
Your answer was exactly what I needed to know. It worked perfect, thank you very much!

I have another question in regards to the same email form I am working on. Once the Email form is open for sending a report, I have list boxes for the user to select multiple email addresses, but the emails are broken down into 4 categories where each one is on its own tabbed page. Some the of the email address can be in more than one group. So as I am building my string which contains all the emails (email addresses are seperator by I have the possibility of some duplication of emails which I do not want. Any suggestions on how to get rid of the duplicates?

Thanks,
Tfrank

 

[GKProgrammer]

Hi TFrank,

I am assuming that these are multi select listboxes, in this case I would try comparing the lists after the user selects an email from the list to the other lists and if there are duplicates then delete the one selected, something like this:

Private Sub lst1_BeforeUpdate(Cancel As Integer)
Call CompareLists("lst1&quot
End Sub



Private Sub lst2_BeforeUpdate(Cancel As Integer)
Call CompareLists("lst2&quot
End Sub



Private Sub lst3_BeforeUpdate(Cancel As Integer)
Call CompareLists("lst3&quot
End Sub


Private Sub lst4_BeforeUpdate(Cancel As Integer)
Call CompareLists("lst4&quot
End Sub


Private Function CompareLists(ctrlName As String)
Dim SelValuelst1 As Variant
Dim SelValuelst2 As Variant
Dim SelValuelst3 As Variant
Dim SelValuelst4 As Variant

Select Case ctrlName
Case "lst1"
For Each SelValuelst1 In Me.lst1.ItemsSelected
For Each SelValuelst2 In Me.lst2.ItemsSelected
If SelValuelst1 = SelValuelst2 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst1.Selected(SelValuelst1) = False
Exit Function
End If
Next SelValuelst2
For Each SelValuelst3 In Me.lst3.ItemsSelected
If SelValuelst1 = SelValuelst3 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst1.Selected(SelValuelst1) = False
Exit Function
End If
Next SelValuelst3
For Each SelValuelst4 In Me.lst4.ItemsSelected
If SelValuelst1 = SelValuelst4 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst1.Selected(SelValuelst1) = False
Exit Function
End If
Next SelValuelst4
Next SelValuelst1
Case "lst2"
For Each SelValuelst2 In Me.lst2.ItemsSelected
For Each SelValuelst1 In Me.lst1.ItemsSelected
If SelValuelst2 = SelValuelst1 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst2.Selected(SelValuelst2) = False
Exit Function
End If
Next SelValuelst1
For Each SelValuelst3 In Me.lst3.ItemsSelected
If SelValuelst2 = SelValuelst3 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst2.Selected(SelValuelst2) = False
Exit Function
End If
Next SelValuelst3
For Each SelValuelst4 In Me.lst4.ItemsSelected
If SelValuelst2 = SelValuelst4 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst2.Selected(SelValuelst2) = False
Exit Function
End If
Next SelValuelst4
Next SelValuelst2
Case "lst3"
For Each SelValuelst3 In Me.lst3.ItemsSelected
For Each SelValuelst1 In Me.lst1.ItemsSelected
If SelValuelst3 = SelValuelst1 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst3.Selected(SelValuelst3) = False
Exit Function
End If
Next SelValuelst1
For Each SelValuelst2 In Me.lst2.ItemsSelected
If SelValuelst3 = SelValuelst2 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst3.Selected(SelValuelst3) = False
Exit Function
End If
Next SelValuelst2
For Each SelValuelst4 In Me.lst4.ItemsSelected
If SelValuelst3 = SelValuelst4 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst3.Selected(SelValuelst3) = False
Exit Function
End If
Next SelValuelst4
Next SelValuelst3
Case "lst4"
For Each SelValuelst4 In Me.lst4.ItemsSelected
For Each SelValuelst1 In Me.lst1.ItemsSelected
If SelValuelst4 = SelValuelst1 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst4.Selected(SelValuelst4) = False
Exit Function
End If
Next SelValuelst1
For Each SelValuelst2 In Me.lst2.ItemsSelected
If SelValuelst4 = SelValuelst2 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst4.Selected(SelValuelst4) = False
Exit Function
End If
Next SelValuelst2
For Each SelValuelst3 In Me.lst3.ItemsSelected
If SelValuelst4 = SelValuelst3 Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst4.Selected(SelValuelst4) = False
Exit Function
End If
Next SelValuelst3
Next SelValuelst4
Case Else
End Select

End Function


Hope this helps.
By the way this code isn't tested but I believe it should be OK.

Regards,
gkprogrammer

 

[GKProgrammer]

Hello Again,

I noticed a mistake I made, I was comparing the index number instead of the actual values in the If statements, so I changed that below. Also make sure that your listboxes bound column will be the email address field for each of the listboxes to get the required result.


Private Sub lst1_BeforeUpdate(Cancel As Integer)
Call CompareLists("lst1&quot
End Sub



Private Sub lst2_BeforeUpdate(Cancel As Integer)
Call CompareLists("lst2&quot
End Sub



Private Sub lst3_BeforeUpdate(Cancel As Integer)
Call CompareLists("lst3&quot
End Sub


Private Sub lst4_BeforeUpdate(Cancel As Integer)
Call CompareLists("lst4&quot
End Sub


Private Function CompareLists(ctrlName As String)
Dim SelValuelst1 As Variant
Dim SelValuelst2 As Variant
Dim SelValuelst3 As Variant
Dim SelValuelst4 As Variant

Select Case ctrlName
Case "lst1"
For Each SelValuelst1 In Me.lst1.ItemsSelected
For Each SelValuelst2 In Me.lst2.ItemsSelected
If Me.lst1.ItemData(SelValuelst1) = Me.lst2.ItemData(SelValuelst2) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst1.Selected(SelValuelst1) = False
Exit Function
End If
Next SelValuelst2
For Each SelValuelst3 In Me.lst3.ItemsSelected
If Me.lst1.ItemData(SelValuelst1) = Me.lst3.ItemData(SelValuelst3) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst1.Selected(SelValuelst1) = False
Exit Function
End If
Next SelValuelst3
For Each SelValuelst4 In Me.lst4.ItemsSelected
If Me.lst1.ItemData(SelValuelst1) = Me.lst4.ItemData(SelValuelst4) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst1.Selected(SelValuelst1) = False
Exit Function
End If
Next SelValuelst4
Next SelValuelst1
Case "lst2"
For Each SelValuelst2 In Me.lst2.ItemsSelected
For Each SelValuelst1 In Me.lst1.ItemsSelected
If Me.lst2.ItemData(SelValuelst2) = Me.lst1.ItemData(SelValuelst1) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst2.Selected(SelValuelst2) = False
Exit Function
End If
Next SelValuelst1
For Each SelValuelst3 In Me.lst3.ItemsSelected
If Me.lst2.ItemData(SelValuelst2) = Me.lst3.ItemData(SelValuelst3) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst2.Selected(SelValuelst2) = False
Exit Function
End If
Next SelValuelst3
For Each SelValuelst4 In Me.lst4.ItemsSelected
If Me.lst2.ItemData(SelValuelst2) = Me.lst4.ItemData(SelValuelst4) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst2.Selected(SelValuelst2) = False
Exit Function
End If
Next SelValuelst4
Next SelValuelst2
Case "lst3"
For Each SelValuelst3 In Me.lst3.ItemsSelected
For Each SelValuelst1 In Me.lst1.ItemsSelected
If Me.lst3.ItemData(SelValuelst3) = Me.lst1.ItemData(SelValuelst1) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst3.Selected(SelValuelst3) = False
Exit Function
End If
Next SelValuelst1
For Each SelValuelst2 In Me.lst2.ItemsSelected
If Me.lst3.ItemData(SelValuelst3) = Me.lst2.ItemData(SelValuelst2) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst3.Selected(SelValuelst3) = False
Exit Function
End If
Next SelValuelst2
For Each SelValuelst4 In Me.lst4.ItemsSelected
If Me.lst3.ItemData(SelValuelst3) = Me.lst4.ItemData(SelValuelst4) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst3.Selected(SelValuelst3) = False
Exit Function
End If
Next SelValuelst4
Next SelValuelst3
Case "lst4"
For Each SelValuelst4 In Me.lst4.ItemsSelected
For Each SelValuelst1 In Me.lst1.ItemsSelected
If Me.lst4.ItemData(SelValuelst4) = Me.lst1.ItemData(SelValuelst1) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst4.Selected(SelValuelst4) = False
Exit Function
End If
Next SelValuelst1
For Each SelValuelst2 In Me.lst2.ItemsSelected
If Me.lst4.ItemData(SelValuelst4) = Me.lst2.ItemData(SelValuelst2) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst4.Selected(SelValuelst4) = False
Exit Function
End If
Next SelValuelst2
For Each SelValuelst3 In Me.lst3.ItemsSelected
If Me.lst4.ItemData(SelValuelst4) = Me.lst3.ItemData(SelValuelst3) Then
MsgBox "This will create duplicate selections in listboxes, selection deselected", vbInformation
Me.lst4.Selected(SelValuelst4) = False
Exit Function
End If
Next SelValuelst3
Next SelValuelst4
Case Else
End Select

End Function


Regards,
gkprogrammer