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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-01-2017, 06:58 PM   #1
Bar_NZ
Newly Registered User
 
Bar_NZ's Avatar
 
Join Date: Aug 2012
Location: Wellington
Posts: 48
Thanks: 13
Thanked 0 Times in 0 Posts
Bar_NZ is on a distinguished road
Cool Table data into BCC for emailing multiple recipients in one email.

Hi all, I'm trying to get Access to send out one email to multiple recipients using Bcc and pull the email address from a table.

I do not want to send any attachments directly from Access, these will be attached, once the e-mail opens and is ready to send.

Table Name: Just_Email
Column Name: Email

I have tried this code and get a compile error:


Private Sub Command4_Click()
Dim rst As DAO.Recordset
Dim strEmailAddress
Set rst = CurrentDb.OpenRecordset("Just_Email")
Do Until rst.EOF
strEmailAddress = strEmailAddress & rst("Email") & ";"
rst.MoveNext
Loop
strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False
rst.Close
Set rst = Nothing
End Sub


Anyone's assistance would be greatly appreciated, I have been Goggling this for about a week now .
Attached Images
File Type: png Compile Error.png (27.8 KB, 65 views)

Bar_NZ is offline   Reply With Quote
Old 10-01-2017, 08:17 PM   #2
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Table data into BCC for emailing multiple recipients in one email.

As the error message says, you haven't declared the variable strSubject.

Quote:
Dim strSubject As String
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 10-03-2017, 02:50 PM   #3
Bar_NZ
Newly Registered User
 
Bar_NZ's Avatar
 
Join Date: Aug 2012
Location: Wellington
Posts: 48
Thanks: 13
Thanked 0 Times in 0 Posts
Bar_NZ is on a distinguished road
Smile Re: Table data into BCC for emailing multiple recipients in one email.

Thatís great, works a treat, thank you very much

Bar_NZ is offline   Reply With Quote
Old 12-05-2017, 01:54 AM   #4
magrfa
Newly Registered User
 
Join Date: Dec 2017
Posts: 8
Thanks: 3
Thanked 0 Times in 0 Posts
magrfa is on a distinguished road
Re: Table data into BCC for emailing multiple recipients in one email.

Please Help I don't know vba and need to get this working. My current code is as follows

Code:
Private Sub Command6_Click()
Dim rst As DAO.Recordset
Dim strEmailAddress
Set rst = CurrentDb.OpenRecordset("T_Export")
Do Until rst.EOF
  strEmailAddress = strEmailAddress & rst(E - Mail) & ","
  rst.MoveNext
Loop
strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
Debug.Print strEmailAddress
DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False
rst.Close
Set rst = Nothing
End Sub
but when I run this I get Error 2295 unknown message recipient(s) the message was not sent.

In T_Export there is only 1 column called E-Mail with the addresses and I want outlook to open a blank e-mail with those addresses in the to box for a colleague to type in a message then send

Please Help!!

Last edited by Uncle Gizmo; 12-05-2017 at 02:02 AM. Reason: Added Code Tags
magrfa is offline   Reply With Quote
Old 12-05-2017, 02:01 AM   #5
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,467
Thanks: 170
Thanked 1,752 Times in 1,720 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Table data into BCC for emailing multiple recipients in one email.

You are using a comma , for the address separator. Try changing it to a semi colon ;

Also you will need to surround your e- mail field in quotes as it has spaces in the field name and it's the required syntax:-

Code:
strEmailAddress = strEmailAddress & rst("E - Mail") & ";"
__________________
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
The Following User Says Thank You to Minty For This Useful Post:
magrfa (12-05-2017)
Old 12-05-2017, 03:08 AM   #6
magrfa
Newly Registered User
 
Join Date: Dec 2017
Posts: 8
Thanks: 3
Thanked 0 Times in 0 Posts
magrfa is on a distinguished road
Re: Table data into BCC for emailing multiple recipients in one email.

Thanks for Reply,

I have tried that here is the new code but still get the message, when I click debug, it highlights the two lines starting Docmd.send in yellow with an arrow pointing at the 2nd line starting , , strsubject

Private Sub Command6_Click()
Dim rst As DAO.Recordset
Dim strEmailAddress
Set rst = CurrentDb.OpenRecordset("T_Export")
Do Until rst.EOF
strEmailAddress = strEmailAddress & rst("E-Mail") & ":"
rst.MoveNext
Loop
strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
Debug.Print strEmailAddress
DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False
rst.Close
Set rst = Nothing
End Sub
magrfa is offline   Reply With Quote
Old 12-05-2017, 03:18 AM   #7
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,467
Thanks: 170
Thanked 1,752 Times in 1,720 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Table data into BCC for emailing multiple recipients in one email.

Your continuation line is missing an &
I hate them anyway so try this ;

You also weren't actually declaring what your variables were, and you hadn't declared strSubject
Add Option Explicit to the top of all your code modules, it will highlight missing and misspelt names.
Code:
Private Sub Command6_Click()
    Dim rst              As DAO.Recordset
    Dim strEmailAddress  As String
    Dim strSubject       As String

    strSubject = "Your Email Subject"
    Set rst = CurrentDb.OpenRecordset("T_Export")
    Do Until rst.EOF
        strEmailAddress = strEmailAddress & rst("E-Mail") & ";"
        rst.MoveNext
    Loop
    strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
    Debug.Print strEmailAddress
    DoCmd.SendObject , , acFormatRTF, strEmailAddress, , , strSubject, strEMailMsg, False, False
    rst.Close
    Set rst = Nothing
End Sub

__________________
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
The Following User Says Thank You to Minty For This Useful Post:
magrfa (12-05-2017)
Old 12-05-2017, 03:31 AM   #8
magrfa
Newly Registered User
 
Join Date: Dec 2017
Posts: 8
Thanks: 3
Thanked 0 Times in 0 Posts
magrfa is on a distinguished road
Re: Table data into BCC for emailing multiple recipients in one email.

Thanks for your help most deeply appreciated.

I still get the same error with the same yellow and arrow, my new code is below

Private Sub Command6_Click()
Dim rst As DAO.Recordset
Dim strEmailAddress As String
Dim strSubject As String
strSubject = "From Ryedale"
Set rst = CurrentDb.OpenRecordset("T_Export")
Do Until rst.EOF
strEmailAddress = strEmailAddress & rst("E-Mail") & ";"
rst.MoveNext
Loop
strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
Debug.Print strEmailAddress
DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False
rst.Close
Set rst = Nothing
End Sub
magrfa is offline   Reply With Quote
Old 12-05-2017, 04:45 AM   #9
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,467
Thanks: 170
Thanked 1,752 Times in 1,720 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Table data into BCC for emailing multiple recipients in one email.

Why have you added back that continuation _ ?
Copy and paste the code exactly as it is.

The syntax is
Code:
DoCmd.SendObject ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile
I've just noticed you don't have an object type, I'm not sure that will work ? Edit : apparently it will.

Also declare strEMailMsg as a string - another variable you haven't declared...
__________________
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
The Following User Says Thank You to Minty For This Useful Post:
magrfa (12-05-2017)
Old 12-05-2017, 04:51 AM   #10
magrfa
Newly Registered User
 
Join Date: Dec 2017
Posts: 8
Thanks: 3
Thanked 0 Times in 0 Posts
magrfa is on a distinguished road
Re: Table data into BCC for emailing multiple recipients in one email.

Thanks for your continued help,

I replaced that line as suggested and it went red, I tried to run it but got a sintax error

Here is my current code

Private Sub Command6_Click()
Dim rst As DAO.Recordset
Dim strEmailAddress As String
Dim strSubject As String
strSubject = "From Ryedale"
Set rst = CurrentDb.OpenRecordset("T_Export")
Do Until rst.EOF
strEmailAddress = strEmailAddress & rst("E-Mail") & ";"
rst.MoveNext
Loop
strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
Debug.Print strEmailAddress
DoCmd.SendObject ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile
rst.Close
Set rst = Nothing
End Sub
magrfa is offline   Reply With Quote
Old 12-05-2017, 04:54 AM   #11
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,467
Thanks: 170
Thanked 1,752 Times in 1,720 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Table data into BCC for emailing multiple recipients in one email.

I showed you the full syntax,it was not a line to replace. The line I showed on the code I posted should work.

Just add

Code:
Dim strEMailMsg as String
with the rest of the Variable declarations.
__________________
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 12-05-2017, 06:07 AM   #12
magrfa
Newly Registered User
 
Join Date: Dec 2017
Posts: 8
Thanks: 3
Thanked 0 Times in 0 Posts
magrfa is on a distinguished road
Re: Table data into BCC for emailing multiple recipients in one email.

Minty,

Sorry I am so slow but I don't understand v basic much, including things like the names that you have been using e.g continuation and declaration

I have made a couple of changes and added your code in, I have attached screenshot, Please can you take a look and see what needs changing or moving,

I do appreciate your help
Attached Images
File Type: png Capture.PNG (16.2 KB, 32 views)
magrfa is offline   Reply With Quote
Old 12-05-2017, 08:20 AM   #13
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,467
Thanks: 170
Thanked 1,752 Times in 1,720 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Arrow Re: Table data into BCC for emailing multiple recipients in one email.

Okay - this is the complete code with the corrections
Code:
Private Sub Command6_Click()
    Dim rst              As DAO.Recordset
    Dim strEmailAddress  As String
    Dim strSubject       As String
    Dim strEMailMsg      As String

    strSubject = "From Ryedale"
    strEMailMsg = "Your Email Text"

    Set rst = CurrentDb.OpenRecordset("T_Export")
    Do Until rst.EOF
        strEmailAddress = strEmailAddress & rst("E-Mail") & ";"
        rst.MoveNext
    Loop

    strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
    Debug.Print strEmailAddress

    DoCmd.SendObject , , acFormatRTF, strEmailAddress, , , strSubject, strEMailMsg, True
    rst.Close
    Set rst = Nothing
End Sub
Currently the send object is set to not send immediately. So it should open as a mail for you to review.
To change that alter the last option from True to False. Only do that once you have checked it all is correct.
__________________
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 12-05-2017, 08:54 AM   #14
magrfa
Newly Registered User
 
Join Date: Dec 2017
Posts: 8
Thanks: 3
Thanked 0 Times in 0 Posts
magrfa is on a distinguished road
Re: Table data into BCC for emailing multiple recipients in one email.

Minty,

That is wonderful, thank you. One point though when in the outlook e-mail it shows e-mails like this
Email_company.co.uk#mailto:email_company.co.uk#. This then requires modifying, is this from access or how it pulls through?

Thanks again for all of your help, you are brilliant
magrfa is offline   Reply With Quote
Old 12-05-2017, 10:12 AM   #15
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,467
Thanks: 170
Thanked 1,752 Times in 1,720 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Table data into BCC for emailing multiple recipients in one email.

That must (I Assume) be how it is stored.

You could create a query from your original table to strip the characters after the # then base your code on that query and the stripped field.

__________________
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
Reply

Tags
bcc , bulk email , email , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Emailing Specialized Report to Multiple Recipients 3link Reports 2 07-20-2015 02:47 PM
Emailing a single record to multiple recipients draconosjb Macros 3 05-25-2012 06:45 PM
Email to Multiple Recipients GinaM Modules & VBA 3 04-20-2011 07:35 AM
Email to multiple recipients mcgheec3 Modules & VBA 2 08-08-2008 06:25 AM
Email multiple recipients from a table skwilliams Modules & VBA 3 12-30-2003 07:50 AM




All times are GMT -8. The time now is 06:03 PM.


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

Featured Forum post


Sponsored Links


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