Sendind a result of a query by mail

[habiler]

Hello,

First of all my best wishes to you all and thank you for your continued relevance aid.

I started the year as I left. Pb mail access

In the following code, I debug a problem with the CurrentDb.OpenRecordset

' ---
' ENVOYER UN MAIL DEPUIS ACCESS
' ---
' Entrée : strEmail <- Adresse e-mail du destinataire
'        strObj   <- Objet du courrier
'        strMsg   <- Corps du message
'        blnEdit  <- True pour pouvoir modifier le courrier avant envoi
'                   False pour expédier le courrier directement.
'
Public Sub SendMail(ByVal strEmail As String, _
  ByVal strObj As String, _
  ByVal strMsg As String, _
  ByVal blnEdit As Boolean)
'On Error Resume Next
DoCmd.SendObject acSendNoObject, , , strEmail, , , strObj, strMsg, blnEdit
End Sub
Private Sub Outlook_Click()
  Dim MonOutlook As New Outlook.Application
  Dim MonMessage As Outlook.MailItem
  Dim ListeEMail As Recordset
  Dim ListeComplete As String

  ' Initialisation
  Set MonMessage = MonOutlook.CreateItem(0)
[COLOR=Red]  Set ListeEMail = CurrentDb.OpenRecordset("SELECT e-mail FROM demandes 2007")[/COLOR]
  '
  ListeEMail.MoveFirst
  ListeComplete = ""
 
  ' Parcourt des enregistrements de la requête
  While Not ListeEMail.EOF
    ListeComplete = ListeComplete & ListeEMail("e-Mail") & ";"
    MonMessage.Attachments.Add ListeEMail(" ")
    ListeEMail.MoveNext
  Wend
 
  'Remplissage de l'objet MailItem
  MonMessage.To = e - mail
  MonMessage.BCC = Left(ListeComplete, Len(ListeComplete) - 1) ' On enlève le dernier point virgule
  MonMessage.Subject = Id
  MonMessage.Body = prenom & vbCrLf
 
  ' Envoi du mail
  MonMessage.Display
 
  ' Fermeture de la session Outlook et désallocation des objets
  MonOutlook.Quit
  ListeEMail.Close
  Set MonOutlook = Nothing
  Set ListeEMail = Nothing
 
End Sub

 

[MarkK]

You can't have spaces or hyphens in SQL table or field names. The SQL parser thinks you want to subtract the field "mail" from the field "e".

Happy new year,

 

[habiler]

Good Evening,

I corrected the error by

  Set ListeEMail = CurrentDb.OpenRecordset("SELECT * FROM 2015")

and still the same error

Habiler

 

[MarkK]

What error do you get?

 

[habiler]

Hello,
My problem is now :

MonMessage.Attachments.Add ListeEMail(" ") (Yellow color + error run time 3265

Private Sub Outlook_Click()
  Dim MonOutlook As New Outlook.Application
  Dim MonMessage As Outlook.MailItem
  Dim ListeEMail As Recordset
  Dim ListeComplete As String

  ' Initialisation
  Set MonMessage = MonOutlook.CreateItem(0)
  Set ListeEMail = CurrentDb.OpenRecordset("SELECT * FROM 2015;")
  'Set oRst =       CurrentDb.OpenRecordset("SELECT * FROM Clients")
  ListeEMail.MoveFirst
  ListeComplete = ""
 
  ' Parcourt des enregistrements de la requête
  While Not ListeEMail.EOF
    ListeComplete = ListeComplete & ListeEMail("e_Mail") & ";"
    MonMessage.Attachments.Add ListeEMail(" ")
    ListeEMail.MoveNext
  Wend
 
  'Remplissage de l'objet MailItem
  MonMessage.To = "e_mail"
  MonMessage.BCC = Left(ListeComplete, Len(ListeComplete) - 1) ' On enlève le dernier point virgule
  MonMessage.Subject = Id
  MonMessage.Body = prenom & vbCrLf
 
  ' Envoi du mail
  MonMessage.Display
 
  ' Fermeture de la session Outlook et désallocation des objets
  MonOutlook.Quit
  ListeEMail.Close
  Set MonOutlook = Nothing
  Set ListeEMail = Nothing
 
End Sub

 

[MarkK]

What are you trying to do when you do this?

ListeEMail(" ")

I believe that . . .

MonMessage.Attachments.Add <filename>

. . . is expecting the name of the file to attach to the email.

What is error 3265? What is the description you get when the error occurs? That is more useful than the number.

Cheers,
Mark

 

[habiler]

Now i have a problem with this line . E_mail is the name of the field in table 2015. (Item not found in this collection. Run time error 3265

ListeComplete = ListeComplete & ListeEMail(e_mail) & ";"

Private Sub Outlook_Click()
  Dim MonOutlook As New Outlook.Application
  Dim MonMessage As Outlook.MailItem
  Dim ListeEMail As Recordset
  Dim ListeComplete As String

  ' Initialisation
  Set MonMessage = MonOutlook.CreateItem(0)
  Set ListeEMail = CurrentDb.OpenRecordset("SELECT [2015].[Feast 1 présent], * FROM 2015 WHERE ((([2015].[Feast 1 présent])=Yes));")
  
  ListeEMail.MoveFirst
  ListeComplete = ""
 
  ' Parcourt des enregistrements de la requête
  While Not ListeEMail.EOF
  ListeComplete = ListeComplete & ListeEMail(e_mail) & ";"
    MonMessage.Attachments.Add ListeEMail("C:\Users\Admin\Desktop\billingDocument.pdf")
    ListeEMail.MoveNext
  Wend
 
  'Remplissage de l'objet MailItem
  MonMessage.To = e_mail
  MonMessage.BCC = Left(ListeComplete, Len(ListeComplete) - 1) ' On enlève le dernier point virgule
  MonMessage.Subject = Id
  MonMessage.Body = prenom & vbCrLf
 
  ' Envoi du mail
  MonMessage.Display
 
  ' Fermeture de la session Outlook et désallocation des objets
  MonOutlook.Quit
  ListeEMail.Close
  Set MonOutlook = Nothing
  Set ListeEMail = Nothing
 
End Sub

 

[MarkK]

You are referencing a named member of the Fields collection of a recordset, so if the field is named "e_mail", then the name must be in quotes . . .

  ListeComplete = ListeComplete & ListeEMail("e_mail") & ";"

Alternatively, for your existing code to work you could do this . . .

Dim e_mail As String
e_mail = "e_mail"
[COLOR="Green"]'now this line would work[/COLOR]
ListeComplete = ListeComplete & ListeEMail(e_mail) & ";"

See the difference?