Table data into BCC for emailing multiple recipients in one email.

[Bar_NZ]

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

 

[JHB]

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

Dim strSubject As String

 

[Bar_NZ]

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

 

[magrfa]

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

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

 

[Minty]

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

strEmailAddress = strEmailAddress & rst([COLOR="red"]"[/COLOR]E - Mail[COLOR="red"]"[/COLOR]) & "[COLOR="Red"];[/COLOR]"

 

[magrfa]

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]

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.

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]

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]

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

The syntax is

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]

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]

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

Dim strEMailMsg as String

with the rest of the Variable declarations.

 

[magrfa]

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

 

[Minty]

Okay - this is the complete code with the corrections

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]

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]

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.