I broke something

[fat controller]

I have a couple of procedures based on similar code, and they have all stopped working recently - - I believe as a result of me poking around with reference libraries when trying to get something else to work. Doh!

The code:

Dim myPath As String
Dim strReportName As String
Dim Bonnet_Number As String
Dim Running_Number As String
Dim Route_Number As String
Dim rs As Recordset
      Dim vRecipientList As String
      Dim vMsg As String
      Dim vSubject As String
      Dim streMailReport As String


On Error Resume Next
      
DoCmd.RunCommand acCmdSaveRecord

DoCmd.OpenReport "Road_Call_Report", acViewPreview

Bonnet_Number = Reports!Road_Call_Report.[Bonnet Number]

Running_Number = Reports!Road_Call_Report.[Running Number]

Route_Number = Reports!Road_Call_Report.[Route]

strReportName = "Road Call Request" + " " + Bonnet_Number + " - " + Running_Number + " on route " + Route_Number + " " + " - " + Format(Date, "ddmmyyyy") + ".pdf"


   Set rs = CurrentDb.OpenRecordset("SELECT * FROM MailRecipientsENG")
           If rs.RecordCount > 0 Then
               rs.MoveFirst
               Do
                   If Not IsNull(rs!Recipient_Mail) Then
                       vRecipientList = vRecipientList & rs!Recipient_Mail & ";"
                       rs.MoveNext
                  Else
                       rs.MoveNext
                  End If

              Loop Until rs.EOF

              vMsg = "Please see the attached road call request"
              vSubject = strReportName
              streMailReport = myPath & strReportName
              DoCmd.SendObject acSendReport, "Road_Call_Report", acFormatPDF, vRecipientList, , , vSubject, vMsg, False

              MsgBox ("Thank you!" & vbLf & vbLf & "The road call request has been successfully sent" & vbLf & vbLf & "Please remember to have your emails open to allow the request to be sent"), vbOKOnly, "Thank you"

      Else
              MsgBox "No contacts found on mailing list - request NOT sent"
       End If

It works partially, but does not populate the recipients email addresses into the email. Everything else seems OK.

I have stepped into the code, and it runs through without errors - just no email addresses.

Can anyone point me in the direction of the right reference library that I need to reinstate to fix this?

 

[pbaldy]

Doesn't sound like a reference issue. When it goes through the loop, does it populate vRecipientList? You might add a Debug.Print on that and see if it's getting values.

 

[fat controller]

Doesn't sound like a reference issue. When it goes through the loop, does it populate vRecipientList? You might add a Debug.Print on that and see if it's getting values.

Where would I add that to the code? Sorry, I have never used it

 

[pbaldy]

More here:

http://www.baldyweb.com/ImmediateWindow.htm

you could put it inside the loop to watch it grow or right before the SendObject.

 

[fat controller]

I put it in just before the list is created - hope I did it right

Debug.Print vRecipientList

Nothing appeared in the immediate window.

I have checked the query that it refers to, and it shows all the addresses

 

[fat controller]

Found it.

I have had to declare rs = DAO.Recordset, as without the DAO. it doesn't work. No idea why/how it was working and then fell over, but at least it is resolved.

Thanks once more

 

[pbaldy]

Can you post the db here?

 

[fat controller]

Sadly not - it is massive, spilt with a couple of back ends, and is completely littered with company logos and info.

 

[pbaldy]

Ah good. Curious, as I would expect it to error. You probably moved the ADO reference above the DAO reference. In any case, it would be more efficient to have:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM MailRecipientsENG WHERE Recipient_Mail Is Not Null")

then you're only pulling the records you need and don't need the If/Then block that tests for Null.

 

[fat controller]

Ah good. Curious, as I would expect it to error. You probably moved the ADO reference above the DAO reference. In any case, it would be more efficient to have:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM MailRecipientsENG WHERE Recipient_Mail Is Not Null")

then you're only pulling the records you need and don't need the If/Then block that tests for Null.

I think you have hit the nail on the head - when I was trying to get a report out to excel the other night (see the long thread in reports), I was shuffling things about in terms of references, and I do distinctly remember doing something with ADODB - - suppose that is where a little knowledge can be a dangerous thing....

Cheers

 

[pbaldy]

Ah, I just noticed why it didn't error. You told it to ignore any errors with:

On Error Resume Next

As you can see, that's not an advisable way to handle errors. Properly done it looks more like:

http://www.baldyweb.com/ErrorTrap.htm

 

[fat controller]

Thanks

I am just about to head out and get some gardening done, but will look at that later - no doubt that will lead to some improvements

 

[pbaldy]

Happy to help!