Email, report snapshots and changing code

CEH

Curtis
Local time
Today, 04:50
Joined
Oct 22, 2004
Messages
1,187
Trying to accomplish something here and need some input. Have a DB that produces a report which is emailed via a command button. No problem here, that works fine. The problem is I am about to split this DB and make the front end a mde file. After that, no code to easily change, and the email addresses are in the code! Code is simple like this...
Private Sub cmdEmailNotice_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SendObject acReport, "rptNewJob", "SnapshotFormat(*.snp)", "Email1@mail.com,Email2@mail.com,Email3@mail.com,Email4@mail.com", "", (Me!JobDescription), "", False, ""
End Sub

Now the problem.... What if "Email3@mail.com" got fired ?? And an "Email6@mail.com" got hired. I don't really want to create a update to the front-end just to change email addresses.
I thought of adding a table of email addresses but I would need a means of inserting the list (formatted properly with commas) into the code. Like I did the "Subject line" in this code.
What it would come to is inserting field from the Email table,(fieldEmailAddress) into the code, BUT separating each record with a comma. How would I do this?

Make sense???
Thoughts????
Thanks
Curtis
 
Last edited:
[Email1Field] & "," & [Email2Field] & "," & [Email3Field] & "," & [Email4Field]
 
That looks like it would work for four different fields........ But the email address is one field.... the records in that field need inserted into the code, each RECORD seperated by comma. Only one field involved.
Let me explain what I want to end up with...
I will need a table "tblSnapshotEmail"... simple... maybe 3 fields.. "EmailID","EmailName","EmailAddress" Now I have a form to add or delete people from the list. If ther is a record on the list then the "emailAddress" field of every record would be inserted into the code, seperated by commas.
 
Last edited:
You are absolutely correct about the four different fields in the same record. You may want to do a function that uses the DAO.RecordSetClone to move through the table collecting the email addresses and returning the formatted string. Let me know if you need help with that function but you might want to give it a stab yourself first.
 
well.................

Well,
I've tried to find examples to something close to what I want..... No luck... MANY examples there, but really more complex then I need!! I am still new to VBA and unsure of what some of these examples are doing... I kind of like to know line for line what everything is doing.... Not have it just "work" :) I have not yet used anything containing the "DAO.RecordSetClone" so I'm unfamiliar with what it is doing... and basically..... lost .....
What I have decided would be best to do is have one form... This would be to display and input names and email addresses. Then a command button "Make list" The on click event would run code to concatenate the email address field using comma delimter and display that in a field ie "txtList"..... I could then just insert this forms field in my existing code and it would result in emailing to only people seen in the result "txtList" box.
Simple code? Am I making this a bigger problem then it is?
 
Hi Curtis,
Am I making this a bigger problem then it is?
Not really. You have to crawl before you walk, walk before you run...

Break the problem down into it's various components, solve each problem and then put the whole solution back together again.

If your form is bound to a query/table then it has a RecordSet and a current record. The RecordSetClone is just what you think, a "clone" of the RecordSet that you can manipulate without messing with the record pointer of the RecordSet. The RecordSetClone is always available and does not have to be "Set" so it is very handy. Are you using DAO or ActiveX (ADODB) in your project? What sort order is the RecordSet in? What field in the record ties these email addresses together?
 
Now Im feeling REALLY dumb! :) DAO or ADODB... More reading I need to do....... Whatever the default for Access 2002 is... As far as the table... Well, sort order can be anything... Table can be anything :) I set it up using three fields... EmailID, EmailName, and EmailAddress. Ther was no table until I discovered the code would not be changable after I converted to mde...(without doing an update) So I put together the table to create a form for the sole purpose of changing the code. So I could have a "Admin" frontend also, that would have forms like this one that the other users would not.
 
Are you saying that every time you email you want everyone in the table to receive a CC:? That would make the job easier. Something like:
Code:
[COLOR=Red][B]Warning <<< Air Code >>> Warning[/B][/COLOR]
Private Function EmailList() As String
'-- Return all of the email addresses in the EmailAddress table
'-- as one string separated with a semicolon ";"

On Error GoTo Err_EmailList

Dim MyRs As DAO.Recordset
Set MyRs = CurrentDb().OpenRecordset([b]"tblEaddresses"[/b], dbOpenForwardOnly)

With MyRs
   Do While Not .EOF
      EmailList = EmailList & ![b][EaddressField][/b] & ";"
      .MoveNext
   Loop
End With

'-- Strip off the last ";"
EmailList = Left(EmailList, Len(EmailList) - 1)

Exit_EmailList:
If Not MyRs Is Nothing Then
    MyRs.Close
    Set MyRs = Nothing
End If
Exit Function
    
Err_EmailList:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume Exit_EmailList

End Function
...should work if that is the case.

You would need to use YourTableName and YourFieldName of course.
 
Thanks RG, looks like that should work, I'll give it a shot tomorrow. So in my code from above... "Email1@mail.com,Email2@mail.com,Email3@mail.com,Email4@mail.com"
I will just replace the addresses with the field name? ie ... "EmailList" ?? Or do I need to reference the table?? I think I have realized by doing some reading on the DAO ADODB that I have never had reason to update or alter a DB with code... :) So much to learn.
Thanks again.
Curtis
 
...replace the addresses with the field name? ie ... "EmailList"
No! Replace the addresses with the Function , EmailList, with no quotes, after you replace my names with your names in the function. You will also need to reference DAO since the default in acXP is ADODB. After <ALT> F11 go to Tools>References and scroll down to Microsoft DAO 3.6 Object Library and check it. You might as well uncheck ActiveX while you are in there. OK back to the code page and Debug>Compile and see if there are any compile errors.
 
ok followed that....... put the function in a module... the new code on the ON CLick event of my "Email Report" command button is..

Private Sub cmdEmailNotice_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SendObject acReport, "rptNewJob", "SnapshotFormat(*.snp)", EmailList, "", (Me!JobDescription), "", False, """"

End Sub

getting errors.......
 
What error? You may have to use Eval(EmailList()) to get it to work.
 
OK.. almost working.... I put it in a query of the table... using....
List: EmailList() ...... Fine...... Returns the list as it should.... I can put this in my Admin form of email addresses to view the compiled list of all people to send to........ BUT....... when I insert it in the command button "Email" onclick event.... the
Private Sub cmdEmailNotice_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SendObject acReport, "rptNewJob", "SnapshotFormat(*.snp)", EmailList, "", (Me!JobDescription), "", False, """"

End Sub
Comes back with "Expression wrong data type"
also tried ....EmailList() . same results
So I know the code is working..... to return the concatenated list..... but the next step is not....."inserting the list into the Sendobject cammand.
 
Try:
DoCmd.SendObject acReport, "rptNewJob", "SnapshotFormat(*.snp)", Eval(EmailList), "", (Me!JobDescription), "", False, """"
 
Yoy could also try:
Code:
Private Sub cmdEmailNotice_Click()
[b]Dim ToList As String[/b]
DoCmd.RunCommand acCmdSaveRecord
[b]ToList = EmailList()[/b]
DoCmd.SendObject acReport, "rptNewJob", "SnapshotFormat(*.snp)", [b]ToList[/b], "", (Me!JobDescription), "", False, """"

End Sub
 
The "toList" comes back with......."An expression you entered is the wrong data type for one of the arguments" With the Docmd Sendobect line highlighted.
The "Eval" one is an interesting error... "Miscrosoft Access cant find the name 'Curtis' you entered in the expression"
"Curtis" is part of one of the email addresses.... Firstname.Lastname@.....

:confused:
 
As a diagnostic put:
MsgBox toList
Just bedore the DoCmd line and after setting toList
Let's see what is in there.
 
First box says "OK"
Second returns list of the email addresses.
Third is the error box '2498'
expression you entered is wrong data type for one of the arguments.
 
While I'm doing some more research try:
DoCmd.SendObject acReport, "rptNewJob", "SnapshotFormat(*.snp)", To:=ToList, "", (Me!JobDescription), "", False, """"
 

Users who are viewing this thread

Back
Top Bottom