Sending Reports to Multiple Recipients from Access 2010 Using Outlook

[InstructorGirl]

I am not a programmer and am trying to send reports to multiple recipients from my Access database using Outlook 2010. I am getting a message box that requires me to click on allow for each email. This process will be sending out individualized monthly reports to about 300 technicians so it's not feasible to do this of course, so therefore I am trying to use CDO in my code to send my emails and bypass the Outlook security model.
When I run my subprocedure I get an error message from Access that says, "Object required". Any help would be greatly appreciated. Thanks.
Here is my code:
Public Sub SendReports()
On Error GoTo Err_SendReports
Dim rs As DAO.Recordset
Dim EmailTo, EmailSubj, EmailText, EmailFrom, EmailAtach
Dim oFSO, sFile, sSig, oFile, sText, oFileA, sCurPath
Set rs = CurrentDb.OpenRecordset("Select Distinct tblTechnicians.TechNumber, tblTechnicians.Email FROM tblTechnicians WHERE tblTechnicians.TechNumber is not Null")
EmailFrom = "EmailAddressOfSender"
EmailTo = "rs"
EmailSubj = "Tool Reimbursement Report"
EmailText = "email.txt"
EmailAtach = "rptTechHoursSpent.rtf"
Set oFSO = CreateObject("Scripting.FileSystemObject")
sFile = "email.txt"
If oFSO.FileExists(sFile) Then
Set oFile = oFSO.OpenTextFile(sFile, 1)
Do While Not oFile.AtEndOfStream
sText = oFile.ReadAll
If Trim(sText) <> "" Then
EmailText = sText
End If
Loop
Else: WScript.Echo "The file was not there."
End If
sCurPath = oFSO.GetAbsolutePathName(".")
Set objEmail = CreateObject("Outlook.Application")
objEmail.From = EmailFrom
objEmail.To = EmailTo
objEmail.Subject = EmailSubj
objEmail.Textbody = EmailText
objEmail.AddAttachment sCurPath & "\" & EmailAtach
objEmail.Configuration.Fields.Item _
= 2
objEmail.Configuration.Fields.Item _
= _
"EmailAddressOfSender"
objEmail.Configuration.Fields.Item _
= 25
objEmail.Configuration.Fields.Update
objEmail.send
oFile.Close

Exit_SendReports:
Exit Sub
Err_SendReports:
MsgBox Err.Description
Resume Exit_SendReports
End Sub

 

[ByteMyzer]

Hello, InstructorGirl,

You didn't highlight which line of the code is giving you the error, but it seems that the likely candidate is the statement referencing the WScript item, which indicates that the original code came from a VBScript application. Access VBA does not have a native WScript object.

Try changing:

Else: [B][COLOR="Red"]WScript.Echo[/COLOR][/B] "The file was not there."

...to:

Else: [B][COLOR="Green"]MsgBox[/COLOR][/B] "The file was not there."

 

[Tango]

This is what I use in one of mine.

    Dim stDocName As String
    stDocName = "Empty_Report"
   ' DoCmd.SendObject acReport, stDocName
    DoCmd.SendObject acSendNoObject, stDocName, acFormatXLS, (Me![107POCs subform1].Form![TextPROS]), , , "QA-P3 107 Alert", "A 107 has been initiated and you have been identified as the next person to take action on it." & " ( " & ([Forms]![frm107s]![ID]) & " ) " & "Please open QA-P3 and process as required. To access this 107, click on ""MISC/PIM"", then ""107 Submissions"", click the edit mode button and then double click the same event ID in the black box in QA-P3. If you have any problems please contact your Quality Assurance office for help." & " " & "This message is automatically generated." & vbNewLine & vbNewLine & "----Event Details listed below----" & vbNewLine & vbNewLine & "Submitting Unit: " & [Forms]![frm107s]![SubFrom] & vbNewLine & vbNewLine & "Serial or Tail#: " & [Forms]![frm107s]![SubSerial] & vbNewLine & vbNewLine & "Narrative: " & vbNewLine & vbNewLine & [Forms]![frm107s]![SubDefandRecom], True

 

[Tango]

BTW, the (Me![107POCs subform1].Form![TextPROS]), part is where it gets a list of who to send the email to. It is a text field that stores each email seperated by a ; symbol.

 

[InstructorGirl]

I've made a couple of changes to my procedure. I debugged it and it is stopping at this line in the procedure:
If oFSO.FileExists(sFile) Then

The error message says: "Object doesn't support this property or method."


Does anyone have any ideas why this is happening? Help!!!!

 

[Tango]

I've made a couple of changes to my procedure. I debugged it and it is stopping at this line in the procedure:
If oFSO.FileExists(sFile) Then

The error message says: "Object doesn't support this property or method."


Does anyone have any ideas why this is happening? Help!!!!

Need to see the rest of your modified code to help you. I would check your sFile reference to make sure you identified it correctly.

 

[InstructorGirl]

Here is the rest of the procedure:

Dim rs As DAO.Recordset
Dim EmailTo, EmailSubj, EmailText, EmailFrom, EmailAtach
Dim oFSO, sFile, sSig, oFile, sText, oFileA, sCurPath
Set rs = CurrentDb.OpenRecordset("Select Distinct tblTechnicians.TechNumber, tblTechnicians.Email FROM tblTechnicians WHERE tblTechnicians.TechNumber is not Null")
EmailFrom = "personsendingfile"
EmailTo = "rs"
EmailSubj = "Tool Reimbursement Report"
EmailText = "email.txt"
EmailAtach = "rptTechBillableHoursVSSpentALL"
Set oFSO = CreateObject("cdo.message")
sFile = "email.txt"
If oFSO.FileExists(sFile) Then
Set oFile = oFSO.OpenTextFile(sFile, 1)
Do While Not oFile.AtEndOfStream
sText = oFile.ReadAll
If Trim(sText) <> "" Then
EmailText = sText
End If
Loop
Else: MsgBox "The file was not there."
End If
sCurPath = oFSO.GetAbsolutePathName(".")
Set objEmail = CreateObject("Outlook.Application")
objEmail.From = EmailFrom
objEmail.To = EmailTo
objEmail.Subject = EmailSubj
objEmail.Textbody = EmailText
objEmail.AddAttachment sCurPath & "\" & EmailAtach
objEmail.Configuration.Fields.Update
objEmail.send
oFile.Close

 

[Tango]

The first thing that jumps out at me is that you called the file name but not the location.

sFile = "email.txt" should include the path not just the name.