Solved

send object macro in access 2003

Posted on 2011-09-19
18
331 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 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