Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

send object macro in access 2003

Posted on 2011-09-19
18
Medium Priority
?
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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