Multiple Contacts in To field from Access to Outlook

[Terminus]

Hi there im try in to move a whole load of email addresses from a table to an email. I came up with the following:

Private Sub cmdAutoMailout_Click()

Dim db As Database
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT tblcontacts.EastForwardPDF FROM tblcontacts WHERE tblcontacts.EastForwardPDF = Yes"

DoCmd.SendObject acSendNoObject, , , strSQL, , , , , True

End Sub

But this didnt work it only moved the the actual statement into the to field in Outlook. Kinda see how it went wrong, but i dont know how to get the SQL to execute or pick up the names i need. Can anyone help, im a fairly new user to vb and really need some help.

Terminus

 

[nat1967]

HI Terminus,

in order to get all the email addresses, you will need to create a recordset and loop through that recordset to grab all the email addresses.

you query seems to define only the (assumption) yes/no field but no email fields. you might want to change your query to either include ALL fields (use *) or at least an email field.

Below is some code I threw together to give you an example of what I mean.

the example will build an SQL statement, use the SQL to open a recordset, loop through the recordset to grab all the email addresses, then build one email string with all the names seperated by a (.

'******begin code****
Option Compare Database

Sub TekTipEmail()
On Error GoTo errhandler:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strEmail As String

Set db = CurrentDb()

strSQL = "SELECT * FROM tblcontacts WHERE tblcontacts.EastForwardPDF = Yes" 'i used a wild card to grab all fields that match Yes in the EastForwardPDF field

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) 'used the strSQL string to open a recordset

rs.MoveLast

strEmail = ""

With rs
If .RecordCount > 0 Then
.MoveFirst
For i = 1 To .RecordCount
If Len(!CustEmail) > 0 Then '<---CustEmail is the field from my table that had email addresses
strEmail = strEmail & !CustEmail & &quot;;&quot; '<---start building string and use ( to seperate
End If
.MoveNext '<---move to the next record
Next i

strEmail = Left$(strEmail, Len(strEmail) - 1) '<---trim off the unneeded space and (



End If

End With

DoCmd.SendObject acSendNoObject, , , strEmail, , , &quot;Insert Subject&quot;, &quot;Insert Message Body&quot;, True '<---send email to everyone in strEmail

Exit Sub

errhandler:

msgbox Err.number & &quot; &quot; & Err.Description
Exit Sub

End Sub
'*******end code******

HTH


Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com

 

[adamroof]

Just so you realize your first error,
you Dim strSQL As String
and then say that your string equals a bunch of text within your quotes.

follow nathan and you'll be fine.

Motto:&quot;I don't know everything, but with this forum, i can find out anything!&quot;

Thanks for all of your great help!

 

[Terminus]

Thank you for your swift reply i get the code but now im getting the error:

variable not Defined. In the debugger it then highlights the 'i' in the code:

With rs
If .RecordCount > 0 Then
.MoveFirst
For i = 1 To .RecordCount
If Len(!tblcontacts!Email) > 0 Then '<---CustEmail is the field from my table that had email addresses
strEmail = strEmail & !tblcontacts!Email & &quot;;&quot; '<---start building string and use ( to seperate
End If
.MoveNext '<---move to the next record
Next i

Can you help?

Terminus

 

[nat1967]

HI terminus,

Dim i as Integer

place that at the top of the code. It just needed to be defined as a variable.

HTH

Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com

 

[Terminus]

Many thanks,

It works a treat!!!!!!

Terminus

 

[Terminus]

Just one query, the button works fine. Though when its cancelled you cannot the script wont run until the database is closed and opened, can you help?

Terminus

 

[nat1967]

Terminus,

are you receiving an error? its sounds as if you are getting the dreaded 2501 (i think) error. what version of Access are you running?

Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com

 

[Terminus]

Yes when i cancel the email, i get the dreaded 2501 error. What is it and can u help? Im running Access 2000

Terminus

 

[nat1967]

Hi Terminus,

To be honest, I dont have a precise answer for you. This problem started when MS put out a security patch last year or so. I'm not sure, but i think the issue was addressed in an Office update package. i tried to find the article this morning but havent found it. i'll keep looking. below are a couple of links i did find.

link describes error:

http://support.microsoft.com/default.aspx?scid=kb;en-us;260819

also, it might not hurt to check for any updates on the office package:

http://office.microsoft.com/officeupdate/default.aspx

one other option is to maybe use Outlook to send your emails. however, the security update forces the user to &quot;ok&quot; each email that gets sent via Outlook. so, each method poses different challenges.

there might be some other threads in Tek-Tips that offer other suggestions but i havent looked for them. Maybe someone else will stop by and shed some more light on this 2501 error message for us.

Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com