Sendobject rtf format is sending MULTIPLE old rtf docs !?!?

[saustin]

Hi,
Bizzare workstation issue cropped up and clearing memory did not resolve it. Use a docmd.sendobject, acreport
rtf to email an access report. Worked well until last week when access started sending the correct report plus a bunch of old rtf reports. Can't even find these on the workstation. For example QARPT1.RTF is correctly attached to the email along with old QARPT18D.RTF QARPT18E.RTF QARPT18F.RTF etc.

Any thoughts ? Thanks, Steve.

Steve Austin
MIS Department
Helicopter Support Inc

 

[CVigil]

What e-mail client are you using (in case this becomes relevant as we go)?

Are you actually getting multiple attachments on a single letter? (If this is happening, there are a number of us who would like to know how and be able to do it ourselves -- on command and when we want to, of course.)

Are you getting multiple letters sent at once, each with its own report?

RE: not finding the files in you file system:
As I understand things, when you use SendObject to mail an object, the file to be attached is created temporarily *somewhere*, but not actually stored to disk anyplace that you get to specify. I imagine that the file is either being created in a system or Access temp directory, or whereever your e-mail software stores attachments, which may or may not be a directory in your file system. If the file is created somewhere on disk prior to attachment, it might even be deleted once the attachment proces is complete.

(As a side-note, I'd love to be able to get to the file in the file system or attach a file from the file system to an e-mail in Access. Access doesn't have readily available some of the same tools (objects, methods, etc.) that I've had in full-blown VB at other assignments. I'd enjoy having control of the attachment name.)

And the last question, the ever-popular, "Can you provide the code-block and circumstances surrounding the SendObject functionality?"

I hope these answers would help us help you out.

-- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot

 

[saustin]

Hi Cvigil,
Thanks for the reply. Use MS Outlook and as mentioned above QARPT1.RTF is correctly attached to the email along with old (correct files from weeks or months ago)QARPT18D.RTF QARPT18E.RTF QARPT18F.RTF etc. are ALSO ATTACHED AND SENT ON THE SAME EMAIL. So, am getting multiple reports one of which is the correct current one(rtf documents) which we open with word on the same email.

Here is the code. Sorry about some of the commented out stuff.

Option Compare Database
Dim intX As Integer

Sub Form_Load()

txtStartTime.SetFocus
txtStartTime.Text = Now()


stDocName = "rptQAMFGRP1AOGRecordOnly" '"rptQAMFGRP1NewRecordOnly"



'Debug.Print intX = DCount("*", "QAMFGNEW&quot

DoCmd.SetWarnings False

'stDocName = "qryAPPENDQAMFGORIGINALwithNEW"
'DoCmd.OpenQuery stDocName, acNormal, acEdit

intX = DCount("*", "QAMFGORIGINALNEW&quot
If intX > 0 Then
DoCmd.OpenQuery "qryDELETEQAMFGORIGINALNEW", acNormal, acEdit
End If

DoCmd.OpenQuery "qryAPPENDQAMFGORIGINALNEW", acNormal, acEdit

'rst = Me.RecordsetClone
'rst.Bookmark = Me.Bookmark

'//Set a field with the total number of records
'Me![Field1] = rst.RecordCount

DoCmd.OpenQuery "qryDELETEOLDQAMFGORIGINALRECORDS", acNormal, acEdit

DoCmd.OpenQuery "qryDELETEQAMFGNEW", acNormal, acEdit

DoCmd.OpenQuery "qryAPPENDQAMFGQryNEWRECORDSONLY", acNormal, acEdit


'DoCmd.OutputTo acReport, stDocName

'DoCmd.SendObject acReport, stDocName, acFormatRTF, "saustin@hsius.com; smurphy@hsius.com; rrivera@hsius.com", , , "QA INSPECTION QUEUE REPORT", _
' "Hi Sue & Rose! Here is the QA INSPECTION QUEUE REPORT in Email. Next will work on emailing you ONLY the AOG/URG items ", False 'True

txtbox.SetFocus
[txtbox].Text = ""

DoCmd.OpenQuery "qryDELETEAOG", acNormal, acEdit

DoCmd.OpenQuery "qryAPPENDtoAOGfromSubQuerytest", acNormal, acEdit

'Steve, only email if there is a NEW AOG record !!!
intX = DCount("*", "AOG&quot
If intX > 0 Then

[txtbox].Text = "Just Started QA Report"

'DoCmd.SendObject acReport, stDocName, acFormatRTF, "smurphy@hsius.com; rrivera@hsius.com", , , "QA INSPECTION QUEUE REPORT", _
' "Hi Sue & Rose! Here is the QA INSPECTION QUEUE REPORT in Email. These are the AOG/URG items Only !", False 'True

DoCmd.SendObject acReport, "rptQAMFGRP1AOGRecordOnly", acFormatRTF, "saustin@hsius.com", , , "QA INSPECTION QUEUE REPORT", _
"Hi Sue & Rose! Here is the QA INSPECTION QUEUE REPORT in Email. These are the AOG/URG items Only !", False 'True


[txtbox].Text = "Just Emailed QA Report"
End If


DoCmd.OpenQuery "qryAPPENDQAMFGORIGINALwithNEW", acNormal, acEdit

[txtbox].Text = "Now Closing"

DoCmd.Close acQuery, "qryAPPENDQAMFGORIGINALwithNEW", acSaveNo

DoCmd.Close acQuery, "qryAPPENDQAMFGORIGINALwithNEW", acSaveNo
DoCmd.Close acReport, stDocName, acSaveNo
DoCmd.Close acQuery, "qryAPPENDtoAOGfromSubQuerytest", acSaveNo
DoCmd.Close acQuery, "qryDELETEAOG", acSaveNo
DoCmd.Close acQuery, "qryAPPENDQAMFGQryNEWRECORDSONLY", acSaveNo
DoCmd.Close acQuery, "qryDELETEQAMFGNEW", acSaveNo
DoCmd.Close acQuery, "qryDELETEOLDQAMFGORIGINALRECORDS", acSaveNo
DoCmd.Close acQuery, "qryAPPENDQAMFGORIGINALNEW", acSaveNo
DoCmd.Close acQuery, "qryDELETEQAMFGORIGINALNEW", acSaveNo
DoCmd.Close acTable, "QAMFGORIGINALNEW", acSaveNo
DoCmd.Close acTable, "QAMFGORIGINAL", acSaveNo
DoCmd.Close acTable, "QAMFGNEW", acSaveNo
DoCmd.Close acTable, "AOG", acSaveNo

'end closing
[txtbox].Text = "AOG Complete"

txtEndTime.SetFocus
txtEndTime.Text = Now()
DoCmd.Close acForm, "frmTIMED Emailed QAMFG Report AOG Only Automated Release", acSaveNo


'DoCmd.Quit
End Sub

 

[CVigil]

Quality Assurance and Manufacturing, huh ? Cool. I've worked for my share of QA guys myself, and am doing so now.

Anyway, I pasted your code into a new form in a new database, created three textboxes with the names referenced, and created a report with a label, naming the report rptQAMFGRP1AOGRecordOnly . I named the form itself "frmTIMED Emailed QAMFG Report AOG Only Automated Release". I also created a table named AOG, giving it an autonumber field, and a text field, filling in two records, then saving it. I never referenced the data at all.

Pasting the code into the module editor let me tell the difference between commented-out stuff and not-commented-out stuff, so no problem there. It's really the only way to look at long/complex code.

Looks like QAMFGORIGINALNEW is a table (or *possibly a query*). Looks like the first thing you do is clear it if it has any records at all. From there, I'll assume -- until corrected -- that all of your work is done on the same table.

I'm assuming that all of the "APPEND" and "DELETE" queries are action queries that actually append and delete records, respectively.

So it looks like you then append (original and new) records to the table.

Then it looks like you delete old original records, then delete new records, then append from QryNEWRECORDSONLY.

(You clear a textbox named txtbox.)

Then you delete AOG. Delete from an AOG table? Delete AOG records from "the" table? Not sure it matters, so I plug on. Later Note: Comments and Dcount code make it evident that AOG is another table; you only e-mail if it has records.

Then it seems you add some records to AOG from a test query.

IF there are in fact records in the AOG table now, you (set txtbox text with a message,) e-mail the report rptQAMFGRP1AOGRecordOnly to yourself (and set txtbox text with another message).

Then you close open queries and stuff (and set a couple more textboxes).

And finally, you close the form itself.

. . .

Let me know if anything in that review of your code is wrong. And if something there is wrong, is it significant? Mostly, I went through this review to make sure I had some decent grasp of what was happening, and what I could weed out for testing.

So I cut it down until I was left with very little. I'd suggest you try the same. Make a copy of your database, tables and all. If your tables are linked tables, make sure that they point to copies of the tables, and not the original tables, so in the copy of the database, you could unlink the tables and re-link to copies of the tables. (If links point to databases on a mapped drive, perhaps remapping the drive to a copied directory could do the trick.) Work in that copy so that you don't affect your actual objects OR data; let us be wise ;-) .

All of the queries seemed basically irrelevant to the e-mail functionality's behavior, so I cut out all the bits using them. Arbitrarily, I left in the check of AOG's records. Why not? I changed the subject and message body to indicate that this was a test version of the form and its output. And I actually ran the form once, so if the e-mail address for you is correct, you should have received an e-mail from me, with an RTF copy of the report I set up. Probably before I finish typing this . The report has only a single label, nothing else.

The code as it looks now, in my simulation of your form, now that I've cut out a lot:

Option Compare Database

Dim intX  As Integer

Sub Form_Load()
    
txtStartTime.SetFocus
txtStartTime.Text = Now()


stDocName = "rptQAMFGRP1AOGRecordOnly" '"rptQAMFGRP1NewRecordOnly"


'Steve, only email if there is a NEW AOG record !!!
intX = DCount("*", "AOG")

If intX > 0 Then
    [txtbox].SetFocus
    [txtbox].Text = "Just Started QA Report"
    
    DoCmd.SendObject acReport, "rptQAMFGRP1AOGRecordOnly", acFormatRTF, "saustin@hsius.com", , , _
                "TEST!  QA INSPECTION QUEUE REPORT.  TEST!", _
                "TEST!!!" & vbCr & _
                "Steve, this is the report rptQAMFGRP1AOGRecordOnly, sent from a way cut-down version " & _
                "of your Form_Load event." & vbCr & _
                "TEST!!!", _
                False 'True
    
    [txtbox].Text = "Just Emailed QA Report"
End If


[txtbox].Text = "AOG Complete"

txtEndTime.SetFocus
txtEndTime.Text = Now()
DoCmd.Close acForm, "frmTIMED Emailed QAMFG Report AOG Only Automated Release", acSaveNo


'DoCmd.Quit
End Sub

Before running your code, I set Outlook 2000 as my default e-mail client again, so it ran fine. (Security did warn me that something was trying to send mail without warning me and did give me the chance to stop it, but I let it go through, knowing what it was. Also, because of the way our Lotus Notes mail server is configured, I had to enter my password to let the mail go through. But it did work.) I looked in my Sent Items folder, and there it was, with a single object, named rptQAMFGRP1AOGRecordOnly.rtf (seemingly just given the report's name).

Now I'll try running the form again. [...doing so...] It contained a single attachment with the same name as before.

Wow, I really wish I could begin to figure out how you're managing (accidentally) to send mail with multiple attachments ... so that many of us could, too!

You have a lot of SendObject instances in your original code, as posted, though only one is currently not commented-out. When you started having the problem, were more of them not commented-out? Could the code somehow have "piled up" "Sent Objects" into the same mail?

Nowhere in the code you posted do you reference QARPT1.RTF, QARPT18D.RTF, QARPT18E.RTF, QARPT18F.RTF, etc. How were the attachments getting those names in the first place? And how does this code get those attachments into e-mail? On my machine, running Access97, the attachment just got the report name. Every time. (Of course, in other instances trying to use Lotus Notes v.5 instead of Outlook 2000, Lotus Notes renamed the attachment with a random crap name and changed the extension: "something.TMP".)

Could the problem be centered in your Outlook setup rather than your Access setup, especially if the problem "just cropped up" with no changes to Access that you're aware of.

Have you checked for viruses?

Have you repaired the database?

Have you tried running the database from other machines?

Have you considered re-installing Access to see if it cleans up some corrupted, uh, stuff (i.e. if it fixes things)? The old sledge-hammer approach, but it might do.


All done for now. I'll see what you have to say after this. I'll check back in when I'm notified of a response.

- - - -
By the way, a couple of other helpful programming tips, just on coding in general (for other future readers, as well)...

I always use the Option Explicit statement, which forces me to explicitly declare variables. It saves me from typos, and occasionally from having a variable get the wrong kind of data. Sure, the occasional compile error on a "quick change for testing" or on something that would have worked fine might be a minor annoyance, but the benefits outweigh the occasional minor annoyance greatly.

Also, working with TextBoxes, you can avoid the need to SetFocus by using the .Value property instead of the .Text property. And .Value is the TextBox's default property, so if you use

Me.[txtbox]=""

then you don't have to set the focus first.

Also, when referencing controls on a form, it is good programming practice to use the Me keyword first, to make sure that A) multiple instances of a form aren't confused with one another, and B) what is being referenced is interpreted as related to the form, not another variable or field name.
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot