Automation giving mail undeliverable errors (Access 2k Win 2k Outlook 2k)

Topic

New Reply

Hello Again.

I’ve got a little database that records all our Goods Inwards rejects, it should then email the recorded cause of the rejection to the supplier. This is where the problem is.

In order for the supplier to have an easily readable message I was asked to put the thing into Word, a bit of automation and no problem, that works.

In order to then email the Word file automatically I had to automate Outlook, this is where I’m having problems. I use a do loop to create a string of email addresses for the correct vendor. This string is then used in the objOutlook.Recipeints.Add (myStringName) line. At the moment I’m getting mail undeliverable errors where ever there are more than one recipient. The system has been working fine with a single user testing it, although I now find this user has been changing the email addresses as they were to people not usually contacted, so the test didn’t use the emails generated by the string…….

Here’s the code for the Do …. Loop and the Outlook automation. I just can’t see what I’ve got wrong, unless I’m supposed to pass a record set to the objOutlook.Resolve line rather than a string??

Set objMsg = objOutLook.CreateItem(olMailItem)
With objMsg
‘fill the to and CC sections of the message
Set objRecipient = .Recipients.Add(strVendorEmailAddys) ‘It’s this bit that seems to be causing my problems.
objRecipient.Type = olTo
Set objRecipient = .Recipients.Add(“myname@myemployersurl”) ‘Added so I get to see what’s happening for the first month or two and can tell if there are problems. So far I’ve received every message generated complete with the attachmentt.
objRecipient.Type = olCC
‘Enter the subject matter
.Subject = “Non conforming Product Received”
.Body = “For the attention of ” & strVendorNames & “. This message has been sent to you from My Employer to inform you that they have received defective product. Please respond to the Quality Assurance Department within 24 hours.” & nlcr() & “Thank You” & nlcr()
‘tell outlook what to attach
Set objAttach = .Attachments.Add(strDocName)
For Each objRecipient In .Recipients
objRecipient.Resolve
Next
.Display
End With

‘create the email list for the Vendor contacts.
Do While Not VendorEmailAddys.EOF
If Not IsNull(VendorEmailAddys!VendContEmail) Then
strVendorEmailAddys = strVendorEmailAddys & VendorEmailAddys!VendContEmail & “;”
strVendorNames = strVendorNames & VendorEmailAddys!VendContName & “, ”
Else ‘Added as we have a couple of suppliers who will not accept emails
If Not IsNull(VendorEmailAddys!VendorFAXNumber) Then
strVendorFAX = strVendorFAX & VendorEmailAddys!VendorFAXNumber & “, ”
End If
End If
VendorEmailAddys.MoveNext
Loop

I’m used to missing the obvious, but if this one is then I’ve missed it again….

Thanks for looking

ian

Reply | Quote

Replies

If I understand this correctly, you are concatenating all recipients into strVendorEmailAddys first, and then using Recipients.Add with this string. Instead, you should add the recipients one by one. You will have to change the flow of your code for this.

Set objMsg = objOutLook.CreateItem(olMailItem)
With objMsg
‘fill the to and CC sections of the message
Do While Not VendorEmailAddys.EOF
If Not IsNull(VendorEmailAddys!VendContEmail) Then
Set objRecipient = .Recipients.Add(VendorEmailAddys!VendContEmail)
objRecipient.Type = olTo
End If
VendorEmailAddys.MoveNext
Loop
Set objRecipient = .Recipients.Add(“myname@myemployersurl”)
objRecipient.Type = olCC
‘ Rest of code goes here
…

Reply | Quote

Hans

Thank You very much. One problem solved….

Now, how do I reset a recordset to the beginning? The recordset I use also has the FAX number in, and if I get the FAX number first it shows Recordset.EOF as True when I try for the email, this works the same the other way round as well….. I know I’ve seen this somewhere but at the moment can’t find it and I’m under a little pressure to sort this out about an hour ago…..

Thanks

Ian

Sorted. Worked out it was the Recordset.MoveFirst option….. Don’t panic Mr Mannering…….

Reply | Quote

Hans

Thank You very much. One problem solved….

Now, how do I reset a recordset to the beginning? The recordset I use also has the FAX number in, and if I get the FAX number first it shows Recordset.EOF as True when I try for the email, this works the same the other way round as well….. I know I’ve seen this somewhere but at the moment can’t find it and I’m under a little pressure to sort this out about an hour ago…..

Thanks

Ian

Sorted. Worked out it was the Recordset.MoveFirst option….. Don’t panic Mr Mannering…….

Reply | Quote

If I understand this correctly, you are concatenating all recipients into strVendorEmailAddys first, and then using Recipients.Add with this string. Instead, you should add the recipients one by one. You will have to change the flow of your code for this.

Set objMsg = objOutLook.CreateItem(olMailItem)
With objMsg
‘fill the to and CC sections of the message
Do While Not VendorEmailAddys.EOF
If Not IsNull(VendorEmailAddys!VendContEmail) Then
Set objRecipient = .Recipients.Add(VendorEmailAddys!VendContEmail)
objRecipient.Type = olTo
End If
VendorEmailAddys.MoveNext
Loop
Set objRecipient = .Recipients.Add(“myname@myemployersurl”)
objRecipient.Type = olCC
‘ Rest of code goes here
…

Reply | Quote