Solved

send object macro in access 2003

Posted on 2011-09-19
18
327 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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

21 Experts available now in Live!

Get 1:1 Help Now