send object macro in access 2003

hello! i have a sendobject macro in access2003 that I use to generate an automatic email to inform people when a change is made... for some reason it stopped working and is giving an error message - I really think that the problem is that i have way too many names in the to, cc, and bcc fields - they are long email addresses and it's really overload.
how can i set it up that i can put the email address in a table and then have the "to" of the macro pull all the addresses from the table? there must be a way :)
thanks so much!!
jpomerantzAsked:
Who is Participating?
 
Nick67Commented:
Yes, that could be done, but you may begin to get close to the limit of what a string can hold.
Your 'converted macro' was blown up quite good in the VBA editor.

We'll build up a myTo, myCC and myBCC string in the loop and try throwing those into the command to get a single email
Since you see what's happening, try this.
Hopefully I got all the arguments in the right spot
Dim rs As Recordset
Dim mySubject As String 'a string to hold your email subject
Dim myText As String 'a string to hold your text
myTo As String 'a string to hold your to addresses 
myCC As String 'a string to hold your CC addtesses
and myBCC As String 'a string to hold your BCC addresses

mySubject = "Notice: change of status in VSHDS   Name: " & [Forms]![administrator assignment of status]![text10] & " " & [Forms]![administrator assignment of status]![text30] & " DOB: " & [Forms]![administrator assignment of status]![text12]
myText = "Be advised- this student's placement or status has changed: Current Status: " & Forms![administrator assignment of status]!Status & " Placement: " & Forms![administrator assignment of status]!currentplacement
Set rs = CurrentDb.OpenRecordset("select DudeEmail, typeofEmail from tblDudes;", dbOpenDynaset)
With rs
Do Until .EOF = True
    Select case !typeofEmail 'what type is it?
        Case 1 'a to address
            myTo = MyTo & !DudeEmail & ";"
        Case 2 'a CC address
            myCC = MyCC & !DudeEmail & ";"
        Case 3 'a BCC address
            myBCC = MyBCC & !DudeEmail & ";"
    .MoveNext
Loop
    DoCmd.SendObject acSendNoObject, "", "", myTo,myCC,myBCC, mySubject, myText, True
End With

Open in new window

0
 
Nick67Commented:
Looping is easy enough

Dim rs a recordset
set rs = currentdb("select email from tblMyRecipients;", dbOpenDynaset)
with rs
do until .EOF = true
    'whatever SendObject code you've got with rs!email at the To: argument
    .movenext
loop
 
0
 
jpomerantzAuthor Commented:
i don't know how to implement this... i'm afraid i need more step by step guidance :)
thanks!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nick67Commented:
I'll post a little demo shim in about an hour and a half.
0
 
jpomerantzAuthor Commented:
ok - thanks so much!
0
 
Jeffrey CoachmanMIS LiasonCommented:
<giving an error message>
Care to post this mysterious error message?
0
 
Nick67Commented:
Here's that shim
Given that you need a hand, you'd better post all the parts of your macro.

This one sends a table as Excel
SendObject.mdb
0
 
jpomerantzAuthor Commented:
cool!
here's the thing - i probably wasn't clear about this... what I'm trying to do actually is to send the message at once to all the names - not just send an attached file with them;
i want to post it for you - so I tried convering to vba - and it worked - but I can't find where it would be to post it here. the command button code just shows me the "run the macro" code not the macro itself...
here's a fake database with the macro - maybe this will help

thanks so so much!!
macro.zip
0
 
Nick67Commented:
If you Open Tools | Macro | Visual Basic Editor you get to the code.
Since you are in A2003,  right-click the command button that sends the emails on your form and choose properties.
Go to the events tab
Click the ... at the end of [Event Procedure] on the OnClick()
that'll open the code.

I adapted the code from your macro and I think I have all the bugs out
Import tblDudes to your database
Comment out what code you have in your button by putting a single quote in front each line
Paste in the code below and give it a try.

The shim won't run becuase it's missing all the controls your form has, but the idea is there.
It sends one email for each record in tblDudes but sends them as to, cc, or bcc based on the last field in the table.



Dim rs As Recordset
Dim mySubject As String 'a string to hold your email subject
Dim myText As String 'a string to hold your text

mySubject = "Notice: change of status in VSHDS   Name: " & [Forms]![administrator assignment of status]![text10] & " " & [Forms]![administrator assignment of status]![text30] & " DOB: " & [Forms]![administrator assignment of status]![text12]
myText = "Be advised- this student's placement or status has changed: Current Status: " & Forms![administrator assignment of status]!Status & " Placement: " & Forms![administrator assignment of status]!currentplacement
Set rs = CurrentDb.OpenRecordset("select DudeEmail, typeofEmail from tblDudes;", dbOpenDynaset)
With rs
Do Until .EOF = True
    'whatever SendObject code you've got with rs!email at the To: argument
    'iif if it's 1 it'll be TO:
    'iif if it is 2 it''' be CC:
    'iif if it is 3 it'll be BCC:
    DoCmd.SendObject acSendNoObject, "", "", IIf(!typeofEmail = 1, !DudeEmail, ""), IIf(!typeofEmail = 2, !DudeEmail, ""), IIf(!typeofEmail = 3, !DudeEmail, ""), mySubject, myText, True
    .MoveNext
Loop
End With

Open in new window

SendObject.mdb
0
 
jpomerantzAuthor Commented:
cool!! i see what it's doing - the only thing is that i think it will really annoy the person to have to generate so many individual emails... is there any any way to set it up so that all the addresses get dumped into the "TO" of one single email?? (that's how it used to work - just that i must have put too many names and messed it up :)

thanks so so much for all your help!!
0
 
jpomerantzAuthor Commented:
tried this... it's giving me an error that says "loop without do" -- which i don't understand cuz there is a "do" in there... huh?
0
 
Nick67Commented:
I forgot an End select statement!
Put
End Select
Just above
.MoveNext

and give 'er a whack
0
 
jpomerantzAuthor Commented:
it works!!!
are you saying though that the string is too long and it may stop working? would I have to worry not to add more names to the table for that reason?
0
 
Nick67Commented:
The macro may have gone BANG from such a limitation.
There can also be upper limits on the length of strings used for things like SQL Statements.

I doubt that you'll hit the limitations of the VBA string type from what I just quickly googled up (possibly 2 billion characters), so you should be good.
<it works!!!>
Do you understand it, too?
0
 
jpomerantzAuthor Commented:
2 billion ought to hold me for at least a few weeks :) :)

i sort of understand it... i'm a self taught access developer who learned from the for dummies book - so i don't have much background in vba; one of these days when i have a few minutes i'm going to try to sit down with this and study it and see if I can understand it more... can i post if i have questions?
0
 
Nick67Commented:
Yuuuuuuuuuuuuuuuuuuuuuuuuup!

So what it does is it pulls all the records from tblDudes
It walks down each record until it gets to the end
For each record, it has a look at the value of TypeofEmail
It add the email address and a semicolon to the appropriate string based on whether TypeofEmail = 1,2, or 3
After all the records have been done, it issues the SendObjectCommand.

You can post new questions, too.
0
 
jpomerantzAuthor Commented:
cool - vba really is amazing.
i really apprecaite your time and patience and helping me through this!!
0
 
jpomerantzAuthor Commented:
amazing!! exactly what I needed :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.