Solved Send current Form record in Email body (1 Viewer)

[Ihk]

I have form based on query and want to send its reocord in email body but not as an attachement.
Form is continuouse, its record is filred based on query. It can be hardly 5-8 rows daily, and I want to send that per email.
Earlier I am sending this one by one, so users receive many emails, rather I want a single email. is there any way out? I also tried this via listbox, but it still sends each record in separate email but with one click, but problem still remains. thanks

 

[theDBguy]

Hi. Lots of ways to do it. Essentially, you'll have to loop through the values and create your email body from there. If you post your code, we could try to tweak it for you.

 

[Ihk]

On record change this code works, which opens a form with some infomation..

Dim Full_Name As String
Dim Emailaddress As String
Dim Article As String
Dim StoragePlace As String
Dim Order_Number As String

Full_Name = DLookup("Full_Name", "Users", "UserID = '" & Me.User.Value & "'")
Emailaddress = DLookup("Emailusers", "Users", "UserID = '" & Me.User.Value & "'")
Article = Me.Article.Value
StoragePlace = Me.StoredPlace.Value

Form which contains above information sends email to multiple users, this works via button on my form.
This below is to send multiple users, but for single receiver code is different....
Because this is for single article (record), that why I am addressing to user as well (full name of user, who has done that).
But for sending multiple records, I dont need specefic user name that case.

Dim oOutlook As Object
Dim oEmailItem As Object
Dim rs As Recordset

Dim recipientsList As String
Dim Article As String
Dim Full_Name As String
Article = Me.txtArticleName
Full_Name = Me.txtUserFullName

If oOutlook Is Nothing Then
    Set oOutlook = CreateObject("Outlook.Application")
End If
Set oEmailItem = oOutlook.CreateItem(0)
With oEmailItem
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Users")
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do Until rs.EOF
            If IsNull(rs!Emailusers) Then
  
            rs.MoveNext
        Else
        recipientsList = recipientsList & rs!Emailusers & ";"
        .To = recipientsList
        rs.MoveNext
    End If
        Loop
    Else
       MsgBox "No email address found"
    End If
    Set rs = Nothing
        .CC = ""
        .subject = "Article delivery notice " & Date
        .Display
        .HTMLBody = "<u>xDELIVERY NOTICE x </u> <br/> <br/> Dear: " & Full_Name & " and All  <br/><br/>  Your delivered article: <b><span style=""color:#003CFA""> " & Article & "  </span style=""color:#003CFA""> </b> <br/>  Article Number: <b>" & Me.OrderNumber & " </b> <br/>  Storage: <b>" & Me.txtStoredPlace & " </b> <br/> <br/> and my further text comes here "

End With
Set oEmailItem = Nothing
Set oOutlook = Nothing
DoCmd.Close acForm, acSaveNo

 

[theDBguy]

but for single receiver code is different....

And do you have that "different" code?

 

[Ihk]

And do you have that "different" code?

Yes for single recipient I dont have to openRecordset to look emails of all users. thats the difference.

 

[theDBguy]

Yes for single recipient I dont have to openRecordset to look emails of all users. thats the difference.

But, you may have to open a recordset to read each record into the body of the email. So, essentially, you flip the process from the one earlier. Instead of looking up email addresses, you're gathering records for the email's message.

 

[Ihk]

((Just updated attachment (sample db)- previous one had problem...)
Everyone is busy, to make it easy for you, I created a sample db. So someone can help me out. Please check this, single record is being sent in an email without any problem. But I want to send more records in one go.
May be a form with listbox, I need your suggestion / help here. Thanks

 

[theDBguy]

((Just updated attachment (sample db)- previous one had problem...)
Everyone is busy, to make it easy for you, I created a sample db. So someone can help me out. Please check this, single record is being sent in an email without any problem. But I want to send more records in one go.
May be a form with listbox, I need your suggestion / help here. Thanks

Hi. Was there a specific form you wanted us to check out?

 

[Ihk]

Hi. Was there a specific form you wanted us to check out?

Thank you for getting me back.
Once Main form open, It has link to two forms
1) Today's Arrival (This record I want to send per email, this is my main question)
2) Update Recrod (this is an exmple, which is already functional, to check this > open this > try to change checkbox Arrival) > a new form appears Cick "Send All" .... this is how it is working at the moment.

But I want to send all records (Today's arrival) in one go. Whether by listbox or continous form.

 

[theDBguy]

Thank you for getting me back.
Once Main form open, It has link to two forms
1) Today's Arrival (This record I want to send per email, this is my main question)
2) Update Recrod (this is an exmple, which is already functional, to check this > open this > try to change checkbox Arrival) > a new form appears Cick "Send All" .... this is how it is working at the moment.

But I want to send all records (Today's arrival) in one go. Whether by listbox or continous form.

Hi. Thanks for the additional information. This is just a quick example. Hope you can go from here.

 

[Ihk]

Hi. Thanks for the additional information. This is just a quick example. Hope you can go from here.

Thank you very much. Nice of you, this serves my purpose. Now I will format it in html. Kind regards,

 

[theDBguy]

Thank you very much. Nice of you, this serves my purpose. Now I will format it in html. Kind regards,

Good luck!

 

[Ihk]

hi @theDBguy once again here.
Error Name: Run-time error 3061 Too few Parameters. Expected 2
In above I am using query filter between dates, these dates are on form (Start Date and End Date).
Data is filtered on form (based on that query)
But When I use the same query for sending mail, it gives error

Between [Forms]![ArrivedToday]![txtStartDate] And [Forms]![ArrivedToday]![txtEndDate]

If I use simply use " Date() " or " #2/2/2012# " or " Between Date() and Date()-6 "
then both works, form filtering and Email sending. What I think is problem is of format if I use between dates criteria from form.

 

[theDBguy]

hi @theDBguy once again here.
Error Name: Run-time error 3061 Too few Parameters. Expected 2
In above I am using query filter between dates, these dates are on form (Start Date and End Date).
Data is filtered on form (based on that query)
But When I use the same query for sending mail, it gives error

Between [Forms]![ArrivedToday]![txtStartDate] And [Forms]![ArrivedToday]![txtEndDate]

If I use simply use " Date() " or " #2/2/2012# " or " Between Date() and Date()-6 "
then both works, form filtering and Email sending. What I think is problem is of format if I use between dates criteria from form.

Please use this updated version (2.0) of the SimpleCSV() function. I used the old version.

SimpleCSV() v2.0

A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of thi...

thedbguy.blogspot.com

 

[Ihk]

Please use this updated version (2.0) of the SimpleCSV() function. I used the old version.

SimpleCSV() v2.0

A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of thi...

thedbguy.blogspot.com

Thank you very much, it worked

 

[theDBguy]

Thank you very much, it worked

Glad we could assist.