Table data into BCC for emailing multiple recipients in one email. (1 Viewer)

Bar_NZ

Registered User.
Local time
Today, 22:34
Joined
Aug 14, 2012
Messages
48
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 :banghead:.
 

Attachments

  • Compile Error.png
    Compile Error.png
    27.8 KB · Views: 142

JHB

Have been here a while
Local time
Today, 10:34
Joined
Jun 17, 2012
Messages
7,732
As the error message says, you haven't declared the variable strSubject.

Dim strSubject As String
 

Bar_NZ

Registered User.
Local time
Today, 22:34
Joined
Aug 14, 2012
Messages
48
That’s great, works a treat, thank you very much :)
 

magrfa

New member
Local time
Today, 09:34
Joined
Dec 5, 2017
Messages
8
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 a moderator:

Minty

AWF VIP
Local time
Today, 09:34
Joined
Jul 26, 2013
Messages
10,353
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([COLOR="red"]"[/COLOR]E - Mail[COLOR="red"]"[/COLOR]) & "[COLOR="Red"];[/COLOR]"
 

magrfa

New member
Local time
Today, 09:34
Joined
Dec 5, 2017
Messages
8
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
 

Minty

AWF VIP
Local time
Today, 09:34
Joined
Jul 26, 2013
Messages
10,353
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
 

magrfa

New member
Local time
Today, 09:34
Joined
Dec 5, 2017
Messages
8
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
 

Minty

AWF VIP
Local time
Today, 09:34
Joined
Jul 26, 2013
Messages
10,353
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...
 

magrfa

New member
Local time
Today, 09:34
Joined
Dec 5, 2017
Messages
8
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
 

Minty

AWF VIP
Local time
Today, 09:34
Joined
Jul 26, 2013
Messages
10,353
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.
 

magrfa

New member
Local time
Today, 09:34
Joined
Dec 5, 2017
Messages
8
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    16.2 KB · Views: 102

Minty

AWF VIP
Local time
Today, 09:34
Joined
Jul 26, 2013
Messages
10,353
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.
 

magrfa

New member
Local time
Today, 09:34
Joined
Dec 5, 2017
Messages
8
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
 

Minty

AWF VIP
Local time
Today, 09:34
Joined
Jul 26, 2013
Messages
10,353
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.
 

Users who are viewing this thread

Top Bottom