Solved

send object macro in access 2003

Posted on 2011-09-19
18
328 Views
Last Modified: 2012-05-12
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!!
0
Comment
Question by:jpomerantz
  • 9
  • 8
18 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 36563415
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
 

Author Comment

by:jpomerantz
ID: 36563441
i don't know how to implement this... i'm afraid i need more step by step guidance :)
thanks!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36563461
I'll post a little demo shim in about an hour and a half.
0
 

Author Comment

by:jpomerantz
ID: 36563476
ok - thanks so much!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36563590
<giving an error message>
Care to post this mysterious error message?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36564003
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
 

Author Comment

by:jpomerantz
ID: 36565002
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36565235
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
 

Author Comment

by:jpomerantz
ID: 36565767
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36567798
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
 

Author Comment

by:jpomerantz
ID: 36568226
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36568258
I forgot an End select statement!
Put
End Select
Just above
.MoveNext

and give 'er a whack
0
 

Author Comment

by:jpomerantz
ID: 36568439
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36568480
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
 

Author Comment

by:jpomerantz
ID: 36568508
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36568551
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
 

Author Comment

by:jpomerantz
ID: 36568576
cool - vba really is amazing.
i really apprecaite your time and patience and helping me through this!!
0
 

Author Closing Comment

by:jpomerantz
ID: 36568581
amazing!! exactly what I needed :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now