VBA to email from recordset

[pbaldy]

Sure

deleted to prevent spam

 

[Sketchin]

thanks Paul,

Its in the training form under the Email Confirmed command button

 

[Sketchin]

Ok, I got it. Im a moron and never had the dates entered in the form!

Really sorry for wasting your time Paul...stupid mistake...too much coding yesterday!

 

[pbaldy]

No problem, glad you got it sorted out. I was just booting up a VM with 2010 on it.

 

[Sketchin]

I wont be surprised if I run into other formatting issues in the near future

 

[konecny.tomas]

Hello,
I inspired myself pretty much in what is posted here, but it is apparently over my current knowledge to get it run. Please help.
I have a Parent table (“TBL_DodacieListy”) which defines a client, I have a child table (“TBL_DodacieListyMnozstvo”) which defines goods (“ID_tov “) sold to the client (from 1 to n records in child table).
I have a form (“F_DODACIE_LISTYnovy “) containg a subform operating over these tables. I generate a Report from a form with a click button E-mail, which runs OnClick procedure below. (Run-time error 3078)

I want to send e-mail to the client and I want to have listed all goods sold to the client within the e-mail body.

Thank you very much in advance.

  Dim olLook As Object                    
  Dim olNewEmail As Object               
  Dim distro As String
  Dim strSql As String
   
  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Set db = CurrentDb
   
  strSql = "SELECT TBL_DodacieListyMnozstvo.ID_dl, TBL_DodacieListyMnozstvo.ID_tov" & _
  " FROM TBL_DodacieListyMnozstvo" & _
  " WHERE (((TBL_DodacieListyMnozstvo.ID_dl)=[Forms]![F_DODACIE_LISTYnovy]![ID_dl]))"
   
  Set rst = db.OpenRecordset("strSql", dbOpenDynaset)
   
   
  Do While Not rst.EOF
  distro = distro & ";" & rst.Fields("[ID_tov]")
  rst.MoveNext
  Loop
   
  Set olLook = CreateObject("Outlook.Application")
  Set olNewEmail = olLook.CreateItem(0)
   
   
  With Me
  'Body
  strEmailText = "List of Goods sold to the client: " & distro & vbCrLf
  End With
   
     With olNewEmail   'Attach template
        .Display
        .Subject = strEmailSubject
        .HTMLBody = strEmailText & olNewEmail.HTMLBody & strline
     End With
  End Sub

 

[pbaldy]

You must have missed post 4.

If you're still stuck, it helps to know the text of the error and where it occurs.

 

[konecny.tomas]

Thank you for your prompt response.

Well, I updated where condition in sql code but it still shows the Run-time error 3078 and it occurs on "Set rst = db.OpenRecordset("strSql", dbOpenDynaset)"

strSql = "SELECT TBL_DodacieListyMnozstvo.ID_dl, TBL_DodacieListyMnozstvo.ID_tov" & _
" FROM TBL_DodacieListyMnozstvo" & _
" WHERE TBL_DodacieListyMnozstvo.ID_dl = " & [Reports]![DODACI_list_c]![ID_dl]

Debug.Print strSql
Set rst = db.OpenRecordset("strSql", dbOpenDynaset)

 

[pr2-eugin]

Do not enclose the strSql in quotes... just..

Set rst = db.OpenRecordset([COLOR=Red][B]strSql[/B][/COLOR], dbOpenDynaset)

 

[konecny.tomas]

Many thanks pr2-eugin and pbaldy.

Now it works perfectly

 

[pbaldy]

Happy to help!