Send Listbox data within email body in MS Access

[Alhakeem1977]

Hi,
How to send Listbox data within email body in MS Access?
My listbox named List881
Here is my code:
'@@@@@@@@
Private Sub cmdOpenEmail_Click()

On Error GoTo ErrorHandler

*

*DoCmd.SendObject , , , "John@gmail.com", _

*Cc:="", subject:="File Physical Retrieval Request.", _

*MessageText:="Dear SCE," & vbCrLf & " " & vbNewLine & _

*"Kindly arrange to provide me with the physical file as per the below details:" & vbCrLf & vbNewLine & _

*"Account Details:" & "* " & List881 & "." & vbCrLf & vbNewLine & _

*"Your assistance in this matter would be highly appreciated." & vbNewLine & vbNewLine & "Thank you!" & vbNewLine & vbNewLine & _

*"Regards," & vbCrLf & _

*"" & [Forms]![NavigationForm]![txtUserName] & "" & "", EditMessage:=True

*

* DoCmd.Close acForm, "frmFileReqF", acSaveNo

* MsgBox "Message Sent Successfully."


Cleanup:

* Exit Sub


ErrorHandler:

* Select Case Err.Number

* * Case 2501

* * * MsgBox "Email message was Cancelled."

* * Case Else

* * * MsgBox Err.Number & ": " & Err.Description

* End Select

* Resume Cleanup

End Sub

'@@@@@@@@@@@@

Thanks a lot in advance!

Sent from my HUAWEI NXT-L29 using Tapatalk

 

[theDBguy]

Hi. Are you trying to email a multi-select listbox? If so, you will have to loop through the selected items.

 

[essaytee]

You need to access the column property of the Listbox.

Sample code (from the link):

Forms!Contacts!Customers.Column(1, 4)

Use of the Code tags would make your post more readable.

Edit/Update: @TheDBGuy - got in before me, I was expecting the followup question to be along the lines of what you asked.

 

[Alhakeem1977]

Hi. Are you trying to email a multi-select listbox? If so, you will have to loop through the selected items.

I am trying to send what ever in listbox, the listbox shows the current records entered by the the user in session only nothing to be selected (form in Data entry mode). Kindly
If could amend my code to do so.



Sent from my HUAWEI NXT-L29 using Tapatalk

 

[theDBguy]

I am trying to send what ever in listbox, the listbox shows the current records entered by the the user in session only nothing to be selected (form in Data entry mode). Kindly
If could amend my code to do so.



Sent from my HUAWEI NXT-L29 using Tapatalk

So, you must have some code to fill in the listbox. If so, you should be able to use the same code to populate the email as well. Can you show us this code, so we can tell you how you can maybe use it for the email?

 

[Alhakeem1977]

Below is my code:

Your earliest response would be highly appreciated.

Private Sub cmdOpenEmail_Click()
On Error GoTo ErrorHandler
 
 DoCmd.SendObject , , , "John@gmail.com", _
 Cc:="", subject:="File Physical Retrieval Request.", _
 MessageText:="Dear SCE," & vbCrLf & " " & vbNewLine & _
 "Kindly arrange to provide me with the physical file as per the below details:" & vbCrLf & vbNewLine & _
 "Account Details:" & "  " & List881 & "." & vbCrLf & vbNewLine & _
 "Your assistance in this matter would be highly appreciated." & vbNewLine & vbNewLine & "Thank you!" & vbNewLine & vbNewLine & _
 "Regards," & vbCrLf & _
 "" & [Forms]![NavigationForm]![txtUserName] & "" & "", EditMessage:=True
 
  DoCmd.Close acForm, "frmFileReqF", acSaveNo
  MsgBox "Message Sent Successfully."

Cleanup:
  Exit Sub

ErrorHandler:
  Select Case Err.Number
    Case 2501
      MsgBox "Email message was Cancelled."
    Case Else
      MsgBox Err.Number & ": " & Err.Description
  End Select
  Resume Cleanup
End Sub

 

[theDBguy]

Below is my code:

Your earliest response would be highly appreciated.

Hi. That’s the same code you already posted earlier. I was looking for the code you use to populate the listbox of the records the user entered in the current session only.

 

[Alhakeem1977]

The SQL row source listbox881 is as below:
ReqPK is the primary key

I would like to return in the email body only two columns: Customerstbl.AccountNumber AS [A/C], Customerstbl.Customer AS [Custmer's Name]

SELECT Customerstbl.FID, Customerstbl.AccountNumber AS [A/C], Customerstbl.Customer, [Userlogin] & " - " & [UserName] AS [Requested by]
FROM tblUser INNER JOIN (Customerstbl INNER JOIN tblFileReq ON Customerstbl.FID = tblFileReq.[AccountNo]) ON tblUser.UserID = tblFileReq.ReqID
WHERE (((tblFileReq.IsActive)=True) AND ((tblFileReq.ReqID)=[Forms]![NavigationForm]![txtID]))
ORDER BY tblFileReq.ReqPk;

This is in the list box:

Private Sub List881_AfterUpdate()
On Error Resume Next
    If IsNull(Me!List881) Then
        Me.Filter = ""
    Me.FilterOn = False
Else
    Me.Filter = "[ReqPK]=" & Me![List881]
        Me.FilterOn = True
    Me.Requery
Me.Refresh
End If
End Sub

 

[essaytee]

Two ways to handle this (may be other ways).

Create a function that effectively cycles through the ListBox data and the output is a string value of the data requested. Having run the function and got the string value of the data requested, substitute that value in your existing email code (replace List888 part).

First option, cycle through the ListBox directly utilising properties of the Listbox, ListCount, ColumnCount in order to extract the information.

Function GetListBoxDataByRowCol() As String
    Dim rcount As Integer
    Dim ccount As Integer
    Dim r As Integer
    Dim c As Integer
    Dim strLine As String
            
    rcount = Me.lstInfo.ListCount - 1
    ccount = Me.lstInfo.ColumnCount - 1
    
    For r = 0 To rcount
        For c = 0 To ccount
            strLine = strLine & Me.lstInfo.Column(c, r) & " -- "
        Next c
        strLine = strLine & vbCr
    Next r
    GetListBoxDataByRowCol = strLine
End Function

Sample above, you will need to specifically account for the columns required, but the template is there for you to work with.


Second option (it was the first one I thought of) is utilising the Recordsource of the ListBox, create a recordset and cycle through that to extract your information.

Function GetLBData() As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strLine As String
        
    Set db = CurrentDb
    strSql = Me.lstInfo.RowSource
    Set rs = db.OpenRecordset(strSql)
    
    rs.MoveFirst
    
    Do While Not rs.EOF
        strLine = strLine & rs("Site_Name") & " " & rs("Site_Path") & vbCr
        rs.MoveNext
    Loop
    Debug.Print "strSLine = " & vbCr & strLine
    GetLBData = strLine
End Function

You will need to substitute for your field names, but the template is there for you to work with.

The above functions are the bare-bones of what's required.

 

[Alhakeem1977]

Dear essaytee,

I have attached my db.

I've tried to do it as recommended but unfortunately, I couldn't be able to do it.:banghead:

If you have enough time, please do it for me.

Thank you in advance!

 

[essaytee]

Dear essaytee,

I have attached my db.

I've tried to do it as recommended but unfortunately, I couldn't be able to do it.:banghead:

If you have enough time, please do it for me.

Thank you in advance!

What did you do? Please show your code that didn't work, that would help others as well trying to help you. At the moment, I can't check your sample db.

 

[essaytee]

I've checked your sample DB from post #10, but I can't find anywhere where you made any attempts, you did say that you tried but couldn't do it. Also not included are the functions I provided in post #9.

With what part of the process are you struggling? I'm sure we can resolve it.

 

[Alhakeem1977]

I've checked your sample DB from post #10, but I can't find anywhere where you made any attempts, you did say that you tried but couldn't do it. Also not included are the functions I provided in post #9.



With what part of the process are you struggling? I'm sure we can resolve it.

Thanks a lot for your reply.

It's fine now I found another way to send my the listbox data with record set query, I post it in another post.

Thank you so much!

Sent from my HUAWEI NXT-L29 using Tapatalk