Help on sending email 1

[burgerman]

I am having trouble running the following code. I am trying to access a query named "Schedule Email List" and I wanting to email a report to each individual in that query. I am getting an error on the set rst line. the error is a runtime 13 error type mismatch. Im sure it is a simple solution but with my limited experience I am having trouble

Thanks
Shawn

Dim db As Database, rst As Recordset
Dim strSQL As String
Dim Names As String

'Return reference to current database
Set db = CurrentDb
strSQL = "SELECT [Schedule Email List].[emailaddressPrimary] FROM [Schedule Email List];"
Set rst = db.OpenRecordset(strSQL)

With rst
.MoveFirst
Names = ![emailaddressPrimary] & ";"
.MoveNext
Do Until .EOF
Names = Names & ![emailaddressPrimary] & ";"
.MoveNext
Loop
End With

'Sending job opportunities to Email Contacts in Joblist Email List
DoCmd.SendObject acSendReport, "Weekly Schedule Report", acFormatRTF, Names, , , "Schedule From" & " " & [Text7] & " until " & [Text9], , -1

rst.Close
End If

 

[hammarj]

Check that the Reference under Tools is set to pick up the Microsoft DAO 3.6 Library. I ran through the Set statment just fine.

 

[KenReay]

Hi

Cannot see anything wrong with it, try this:

put a debug check point on the line

Set rst = db.OpenRecordset(strSQL)

run the app, when it stops on the check point line in the immediate wibdow type debug.print strSQL

edit\copy the resulting SQL to the clipboard

Open the query window, switch to SQL view and paste it in

Run it

What happens? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[burgerman]

I pasted the SQL into a window and ran it and it successfully called the appropriate data. Is it possiable that this code would run incorrectly on a on click event?

 

[KenReay]

Hi

If it runs in Query then it is OK to run in OnClick event, having eliminated syntax error as the cause, I suspect that it may be a references problem as suggested by hammarj, too check this, in the code module design view, click tools \ references \nd ensure DAO library is present and above ADO (if present) in the list. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[burgerman]

Here are my references:
Name: VBA
FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Version: 4.0
***********************
Name: Access
FullPath: C:\Program Files\Microsoft Office\Office\MSACC9.OLB
Version: 9.0
***********************
Name: activelock18
FullPath: c:\windows\system\activelock18.ocx
Version: 1.0
***********************
Name: JRO
FullPath: C:\Program Files\Common Files\System\ado\msjro.dll
Version: 2.6
***********************
Name: Office
FullPath: C:\Program Files\Microsoft Office\Office\MSO9.DLL
Version: 2.1
***********************
Name: VBScript_RegExp_10
FullPath: C:\WINDOWS\System32\vbscript.dll\2
Version: 1.0
***********************
Name: VBScript_RegExp_55
FullPath: C:\WINDOWS\System32\vbscript.dll\3
Version: 5.5
***********************
Name: stdole
FullPath: C:\WINDOWS\System32\stdole2.tlb
Version: 2.0
***********************
Name: ADODB
FullPath: C:\Program Files\Common Files\System\ado\msado21.tlb
Version: 2.1
***********************
Name: MSACAL
FullPath: C:\PROGRA~1\MICROS~1\Office\MSCAL.OCX
Version: 7.0
***********************
Name: DAO
FullPath: C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
Version: 5.0
***********************
Name: MDACVer
FullPath: C:\WINDOWS\System32\odbcconf.dll
Version: 2.50
***********************
Name: Scripting
FullPath: C:\WINDOWS\System32\scrrun.dll
Version: 1.0
***********************
Name: Scriptlet
FullPath: C:\WINDOWS\System32\scrobj.dll
Version: 1.0
***********************
Name: MSComCtl2
FullPath: C:\Windows\System32\mscomct2.ocx
Version: 2.0
***********************
Anything out of line?

 

[KenReay]

Hi

Well if these are all of the selected rferences, then DAO is after ADO, so you either need to move the DAO above the ADO

OR

qualify the Recordset sim statement so:

Dim db As Database, rst As DAO.Recordset
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[burgerman]

Ken Thanks for your help! Moving the DAO above ADO did the trick. I never knew that the order of the list made any difference

Shawn

 

[KenReay]

Hi

Well it does not, under most circumstances, but the ADO and DAO libraries have at least one common property (.Recordset), so unless you qualify the definition as I suggested (eg ADO.Recordset or DAO.Recordset), then Access just uses the first one it comes to, hence the Order of the libraries in the list matters.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK