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

Ihk

Member
Local time
Today, 15:04
Joined
Apr 7, 2020
Messages
238
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

I’m here to help
Staff member
Local time
Today, 06:04
Joined
Oct 29, 2018
Messages
19,172
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

Member
Local time
Today, 15:04
Joined
Apr 7, 2020
Messages
238
On record change this code works, which opens a form with some infomation..
Code:
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.

Code:
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
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 06:04
Joined
Oct 29, 2018
Messages
19,172
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

Member
Local time
Today, 15:04
Joined
Apr 7, 2020
Messages
238
((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
 

Attachments

  • Send Email.accdb
    1.3 MB · Views: 115
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 06:04
Joined
Oct 29, 2018
Messages
19,172
((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

Member
Local time
Today, 15:04
Joined
Apr 7, 2020
Messages
238
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

I’m here to help
Staff member
Local time
Today, 06:04
Joined
Oct 29, 2018
Messages
19,172
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.
 

Attachments

  • Send Email.zip
    48.5 KB · Views: 130
  • Love
Reactions: Ihk

Ihk

Member
Local time
Today, 15:04
Joined
Apr 7, 2020
Messages
238
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,
 

Ihk

Member
Local time
Today, 15:04
Joined
Apr 7, 2020
Messages
238
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
Code:
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

I’m here to help
Staff member
Local time
Today, 06:04
Joined
Oct 29, 2018
Messages
19,172
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
Code:
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.

 
  • Love
Reactions: Ihk

Ihk

Member
Local time
Today, 15:04
Joined
Apr 7, 2020
Messages
238
Please use this updated version (2.0) of the SimpleCSV() function. I used the old version.

Thank you very much, it worked
 

Users who are viewing this thread

Top Bottom