Solved Docmd.Send object, display many records

[Leo_Polla_Psemata]

Hi
With this very simple command, I have the ability to send emails to several clients, reporting many things, quotations etc.

Private Sub Command84_Click()
DoCmd.SendObject , , , , , , "SOMETHING", [SalesGroupingField] & "" & [1], True, ""
End Sub

Now, i want, on the body of the emai, to display many records,
See this picture, it is from Nothwind, how can i display on the body of the email all records from this report ? not only the first one ?

 

[Rx_]

Didn't see anyone else trying to assist. I don't have time to try this today. It is likely you need to create a Recordset. Then loop through the recordset. I might suggest limiting the loop to 100 or some number to prevent very large emails should something in the query be unexpected.
If you need help createing a Recordset Object, I suggest that you post a different question.

With rsEmail
[B]sMessageBody = "Email body Text"[/B]
Do Until .EOF
sMessageBody = [B]sMessageBody &[/B] vbCrLf & .Fields (0) & " " &.Fields (1) & "" &.Fields (2)
.MoveNext
Loop
DoCmd.SendObject acSendNoObject,,,"emailaddressedto",,,"testmail",sMessageBody,False,False
End With

 

[Leo_Polla_Psemata]

Rx, thanks very very much
Yiap, recordset is what i need but , I am new in the world of vba and recordset sound like sience fiction
The records are not going to be more than 10 maximum.

I will try this syntax ,

Could you please tell me where can I go and read and understand what is .eof , and where can I tune the records no more than 10 ?

Thanks again for your kindness to reply .

 

[Leo_Polla_Psemata]

Rx, i followed your code step by step and it returns runtime error 424 object required
The code is athis, can you make a suggestion what goes wrong ?

Private Sub Report_Current()

With rsEmail
sMessageBody = "Email body Text"
Do Until .EOF
sMessageBody = sMessageBody & vbCrLf & .Fields(POL) & " " & .Fields(POD) & "" & .Fields(RATE20) & "" & .Fields(RATE40)
.MoveNext
Loop

DoCmd.SendObject , , , , , , "test test", sMessageBody, True, ""

End With
End Sub

 

[joeKra]

In order to use a recordset you need to create it first

Dim RsEmail as dao.recordset
set RsEmail = currentdb.openrecordset ("your table name")

also, the field must be wrapped with double quotes so instead of Fields(POL) you need to write Fields("POL")

BTW you can end the with right after the loop as it's not anymore used in the following statements

Joe

 

[Rx_]

Joe is right, the recordset is created then used by the construct.
Thanks Joe.
I will be out in NYC at the USMMA.Edu during MLK week. So please warm the city up for me.

 

[joeKra]

Glad i can help !

I will be out in NYC at the USMMA.Edu during MLK week. So please warm the city up for me.

Brrrrrr....... it's really cold (global warming ?? i doubt it !)

 

[Leo_Polla_Psemata]

JoeKra and Rx, many many thanks for your input .

I realize that recordset is a very powerful weapon.
How can someone master it ?
I mean, is there any method or site you can recommend for a beginner ?

 

[Leo_Polla_Psemata]

And something else

I understand that this syntax : & vbCrLf & .Fields("POL")
displays the records one next to another, horizontal
Is there another syntax i could use if i want to display records in a vertical lay out ?

record1
pol
pod
ratea
rateb

record2
pol
pod
ratea
rateb

and so on

 

[Leo_Polla_Psemata]

Okay, I found it

I must use the & vbNewLine &

& vbCrLf & .Fields(POL) & " " & .Fields(POD)

must change to

& vbCrLf & .Fields(POL) & " " & vbNewLine & .Fields(POD)

 

[Leo_Polla_Psemata]

Hi everyone


With this code, i can display on the body of an email a recordset.

Private Sub Report_Current()

Dim RsEmail as dao.recordset
set RsEmail = currentdb.openrecordset ("your table name")
With rsEmail
sMessageBody = "Email body Text"
Do Until .EOF
sMessageBody = sMessageBody & vbCrLf & .Fields(POL) & " " & .Fields(POD) & "" & .Fields(RATE20) & "" & .Fields(RATE40)
.MoveNext
Loop

DoCmd.SendObject , , , , , , "test test", sMessageBody, True, ""

End With
End Sub

Now, the question is what should i change if i want to output a recordset into a text file ?
Not being displayed into a email body but make a .txt file.

 

[Leo_Polla_Psemata]

Private Sub SendMl_Click()

        DoCmd.SetWarnings False
        DoCmd.OpenQuery "UpdF2"
        DoCmd.OpenQuery "UPDT2"
        DoCmd.SetWarnings True
        Me.Refresh
   
    Dim Msg2 As String
    Dim Msg3 As DAO.Recordset
   
        Msg2 = "Dear team," & "<br>" & "Please find attached booking list for" & "<br>" & "Vessel : " & Vessel & "<br>" & "Voy : " & voy & "<br>" & "ETA : " & ETA
   
    Set Msg3 = CurrentDb.OpenRecordset("BkLay")

    With Msg3
    sMessageBody = "Booking Summary"
    Do Until .EOF
    sMessageBody = sMessageBody & "<br>" & .Fields("bkid") & " * " & .Fields("bk#") & " * " & .Fields("1stLoadingVoyage") & " * " & .Fields("ETDat1stLoadingTerminal") & " * " & .Fields("Rating") & " * " & .Fields("NrOfCntrs") & "X" & .Fields("CNTRSZTP") & " * " & .Fields("ToCity")
    .MoveNext
    Loop
    End With
   
   
    Dim O As Outlook.Application
    Dim M As Outlook.MailItem

    Set O = New Outlook.Application
    Set M = O.CreateItem(olMailItem)
   
        With M
            .BodyFormat = olFormatHTML
            .HTMLBody = Msg2 & "<br>" & "<br>" & sMessageBody
           
            .To = "BkPir"
            .CC = "BkList"
            .Subject = "Booking List, Vessel: " & Vessel & "  // VOY : " & voy & "  //  @Drz on : " & ETA & " // " & iris & " // bklst_ " & voy
            .Display
           
        End With


    Set M = Nothing
    Set O = Nothing
End Sub

Many years have gone through since the first time i managed to send email with email body filled with info taken from a table/form.
Many thanks to RX (although his last login was 2 years ago) and all other members who have inputted.

For people like me, amateurs access developers with no much programming knowledge, transfering data from complicated tables into email text is very important. Clients don't read attachments or even long emails, most of the time, one sms or info on email body that they can read through mobile, is much more handy.