Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2018, 02:47 AM   #1
jansed1
Newly Registered User
 
Join Date: Jan 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
jansed1 is on a distinguished road
mail to multiple recipients

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?

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 "
    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

jansed1 is offline   Reply With Quote
Old 04-17-2018, 03:22 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,436
Thanks: 128
Thanked 1,476 Times in 1,448 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: mail to multiple recipients

Is your field name actually MailAdres ?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 04-17-2018, 03:39 AM   #3
jansed1
Newly Registered User
 
Join Date: Jan 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
jansed1 is on a distinguished road
Re: mail to multiple recipients

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

jansed1 is offline   Reply With Quote
Old 04-17-2018, 06:00 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,436
Thanks: 128
Thanked 1,476 Times in 1,448 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: mail to multiple recipients

Which line is the debug window highlighting ?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 04-17-2018, 06:02 AM   #5
jansed1
Newly Registered User
 
Join Date: Jan 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
jansed1 is on a distinguished road
Re: mail to multiple recipients

Set rstEMail = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
jansed1 is offline   Reply With Quote
Old 04-17-2018, 06:13 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,436
Thanks: 128
Thanked 1,476 Times in 1,448 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: mail to multiple recipients

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

Try this instead
Code:
 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 ?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 04-17-2018, 06:50 AM   #7
jansed1
Newly Registered User
 
Join Date: Jan 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
jansed1 is on a distinguished road
Re: mail to multiple recipients

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).

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

jansed1 is offline   Reply With Quote
Old 04-17-2018, 07:01 AM   #8
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,436
Thanks: 128
Thanked 1,476 Times in 1,448 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: mail to multiple recipients

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
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 04-17-2018, 07:11 AM   #9
bastanu
Registered User
 
Join Date: Apr 2010
Location: Kelowna, Canada
Posts: 148
Thanks: 1
Thanked 48 Times in 48 Posts
bastanu is on a distinguished road
Re: mail to multiple recipients

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
bastanu is offline   Reply With Quote
Old 04-17-2018, 10:43 AM   #10
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,387
Thanks: 14
Thanked 259 Times in 257 Posts
Mark_ will become famous soon enough
Re: mail to multiple recipients

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

If so, you may be running into a permissions issue with Outlook.
Mark_ is offline   Reply With Quote
Old 04-17-2018, 04:58 PM   #11
1268
Newly Registered User
 
Join Date: Oct 2012
Posts: 41
Thanks: 1
Thanked 4 Times in 4 Posts
1268 is on a distinguished road
Re: mail to multiple recipients

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
1268 is offline   Reply With Quote
Old 04-17-2018, 10:05 PM   #12
jansed1
Newly Registered User
 
Join Date: Jan 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
jansed1 is on a distinguished road
Re: mail to multiple recipients

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.

jansed1 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sending an E-mail to multiple recipients LHolden Forms 2 01-24-2013 11:16 AM
E-mail reports to multiple recipients based on form filter Felix314 Forms 0 01-09-2013 09:22 AM
Emailing From access (adding selected recipients to bcc line of outlook new mail wind enigma54 Modules & VBA 6 10-11-2010 12:11 AM
Sending a Query to multiple recipients in 1 e-mail driver7408 Modules & VBA 4 04-06-2010 11:16 PM
Mail Merge: Best way to deal with unknown recipients? DJ81 General 1 05-13-2007 01:10 PM




All times are GMT -8. The time now is 04:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World