mail to multiple recipients (1 Viewer)

[jansed1]

I want to send mail from Access to multiple recipients. The recipients are in a table. The code gives me errorcode 3061. What am I doing wrong?

 Dim oApp As Object
    Dim oMail As Object
    Dim oByValue
    Dim Ldatum As String
    Dim AfzenderVN As String 'voornaam
    Dim AfzenderAN As String 'achternaam
    Dim strEMail As String
    Dim MyDB As DAO.Database
    Dim rstEMail As DAO.Recordset
    Dim strBuild As String
    Dim strSQL As String
    
    strSQL = "SELECT * FROM qryKwaliteitsbeheerMail "
    Set MyDB = CurrentDb
    Set rstEMail = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    
    With rstEMail
        Do While Not .EOF
            strBuild = strBuild & ![MailAdres] & ";" 'Build the 'TO'String
                .MoveNext
        Loop
    End With

 

[Minty]

Is your field name actually MailAdres ?

 

[jansed1]

Yes, but it's no problem to rename it if necessary.

 

[Minty]

Which line is the debug window highlighting ?

 

[jansed1]

Set rstEMail = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

 

[Minty]

That indicates it can''t open the recordset, is this a SQL linked table ?

Try this instead

 strSQL = "SELECT MailAdres  FROM qryKwaliteitsbeheerMail "

 Set rstEMail = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)

If that doesn't work does qryKwaliteitsbeheerMail rely on a form that isn't open or something similar ?

 

[jansed1]

The qry was filtered by a field on the form. I removed the field from the qry and put it in the sql and used some quotes because it's a textfield.

But now the next problem pops up: error -2147467259 (80004005) Can't recognize one or more names. The highlighted line is .send (at the end of the code).

    Dim oApp As Object
    Dim oMail As Object
    Dim oByValue
    Dim Ldatum As String
    Dim AfzenderVN As String 'voornaam
    Dim AfzenderAN As String 'achternaam
    Dim strEMail As String
    Dim MyDB As DAO.Database
    Dim rstEMail As DAO.Recordset
    Dim strBuild As String
    Dim strSQL As String
    
    strSQL = "SELECT * FROM qryKwaliteitsbeheerMail WHERE Relatienr = '" & Forms!frmKwaliteitsbeheer!Relatienr & "'"
    'Debug.Print strSQL
    'Stop
    Set MyDB = CurrentDb
    Set rstEMail = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    
    With rstEMail
        Do While Not .EOF
            strBuild = strBuild & ![Mail] & ";" 'Build the 'TO'String
                .MoveNext
        Loop
    End With
    
  
    'Create and show the Outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    
    Ldatum = Date + 21
    
    With oMail
        
        'add the image in hidden manner, position at 0 will make it hidden
        .Attachments.Add "J:\logo.jpg", oByValue, 0
    
        'Now add it to the Html body using image name
        'change the src property to 'cid:your image filename'
        'it will be changed to the correct cid when its sent.
       .To = strBuild
       .Subject = "Opvraag leveranciers gegevens (KWALITEIT) "
        .HTMLBody = .HTMLBody & "<p><font face=Calibri>Geachte heer, mevrouw,</p> " _
                    & "<p>Een goede en constante kwaliteit van onze eindproducten begint met een goede en constante kwaliteit van de diensten/producten die u levert.<b></b><br />" _
                    & "<font size=1><i>Op al onze overeenkomsten met betrekking tot de levering van diensten en goederen zijn onze algemene voorwaarden van toepassing, deze zijn gedeponeerd bij de Kamer van Koophandel en kunnen desgevraagd ter hand worden gesteld</TD></TR></TABLE>"
        .Attachments.Add "J:\Kwaliteit\Leveranciersbeoordeling\RP01-F02 Formulier leveranciersspecificatie Non Food.pdf"
        '.Display
        .Send
              
    End With

 

[Minty]

Add a debug.print strBuild after the string is finished building. - it sounds like you have some duff email address or characters in your data

 

[bastanu]

You don't seem to show the entire sub. Noticed LDatum is a string but you use it as a date (Date +21) and can't see where it is used.

Have you tried to comment out the line where you set the HTML body and see what happens?

Cheers,
Vlad

 

[Mark_]

Does your code work with .display instead of .send?

If so, you may be running into a permissions issue with Outlook.

 

[1268]

1. Get it to send with a manual string or single name. Make sure it's working. Sometime hard to troubleshoot if multiple errors present.
2. Make your name string a function you can call. Get it to build correctly by itself first. As someone mentioned use debug.print.

Sent from my SM-G950U using Tapatalk

 

[jansed1]

Problem is solved. The field with mailaddresses was a hyperlink field so the qry came back with mailaddress#mailto:mailadress#. Changed the field to text and now all works as it should.
Thank you all for your support.

@Mark_ my code works with .send. No problems with Outlook.
@bastanu LDate is used in the html body but it wasn't necessary to place the whole code.