SendObject HELP! 1

[snowyej]

I've searched and searched this site for the answer to my problem, but can't find anything...

After lots of trial and error I've figured out how to send email from my form. I have a knowledge database and want to have users click a button to send an email to a set group of people (list of email addresses - emailAddress - in a table - tblEmail). In the email, it will automatically populate the body of the email with the different fields on the knowledge form (issue, cause, resolution, etc). This I've figured out fine. The only thing that is not working for me is populating the To: field in the email. It will only put the first email in the table. I'm sure it's something pretty simple in my code, but I'm still not knowledgeable enough to figure it out.

Here's my code:

Private Sub Command11_Click()
On Error Resume Next

Dim DateEntered As String
Dim App As String
Dim Issues As String
Dim Cause As String
Dim Resolution As String
Dim EnteredBy As String
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.openrecordset("Select * from [tblEmail]&quot

DateEntered = Me![Date]
App = Me![Application]
Issues = Me![IssueFound]
Cause = Me![Cause]
Resolution = Me![Resolution]
EnteredBy = Me![EnteredBy]

'Create message
bodytext = bodytext & "Date: " & DateEntered & Chr$(10)
bodytext = bodytext & Chr$(10)
bodytext = bodytext & "Application: " & App & Chr$(10)
bodytext = bodytext & Chr$(10)
bodytext = bodytext & "Issue: " & Issues & Chr$(10)
bodytext = bodytext & Chr$(10)
bodytext = bodytext & "Cause: " & Cause & Chr$(10)
bodytext = bodytext & Chr$(10)
bodytext = bodytext & "Resolution: " & Resolution & Chr$(10)
bodytext = bodytext & Chr$(10)
bodytext = bodytext & "Entered By: " & EnteredBy

'E-mail above message
DoCmd.SendObject acSendNoObject, "", acFormatTXT, rst!emailAddress, , , "Knowledge Transfer", bodytext, True

End Sub

So, to recap, the email process works fine for what I want, except for populating the To: field. How can it get it to pull in all of the email address in the tblEmail table?

Any help is greatly appreciated!

Thanks much!
Elizabeth

 

[jebry]

Hi Elizabeth!

Are you separating the addresses with the list separator used in the regional properties?

hth
Jeff Bridgham
bridgham@purdue.edu

 

[snowyej]

Ok, maybe I'm just dumb, but I'm not sure what you mean? Do you mean a comma between the email addresses? If so, how would I specify that in the code? The code in my first post, I got from browsing around this newsgroup. I just edited it to fit my needs. I'm still fairly new at the VB part of Access.

Thanks for the fast response!

Elizabeth

 

[jebry]

Hi again!

Normally, in Outlook, the email addresses are separated by a semi-colon so that is what I would try first. Just make sure that you store the addresses that way in the table and you will be okay.

If you are not using Outlook then open up the Windows control panel, from Start - Settings and double click on Regional Setting. In the Numbers tab, you will see List Separator. It will probably be a comma, but you will need to put the list separator between each email address in your table.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[snowyej]

Ok, I see what you're saying. I am using Outlook. (And I knew it used semi-colons...must've had a brain freeze when I said commas...) So, should I just put all the email addresses in one field in the table? Right now I have and emailID that identifies each email (not sure what purpose this would serve, but it seemed like a good idea when I created the table), and then the emailAddress field --

emailID emailAddress
-------------------------------
1 bob@email.com
2 elizabeth@email.com
3 jane@email.com

etc.

Should I just create one field instead and put the emails in one field like so:

emailAddress
-------------------
bob@email.com; elizabeth@email.com; jane@email.com

etc...

Thanks for your help!

Elizabeth

 

[jebry]

Hi!

I thought that you were already doing that from your post. I would keep them stored in separate records and adjust your code like this:

Private Sub Command11_Click()
On Error Resume Next

Dim DateEntered As String
Dim App As String
Dim Issues As String
Dim Cause As String
Dim Resolution As String
Dim EnteredBy As String
Dim EmailRecipients As String
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.openrecordset("Select * from [tblEmail]&quot

DateEntered = Me![Date]
App = Me![Application]
Issues = Me![IssueFound]
Cause = Me![Cause]
Resolution = Me![Resolution]
EnteredBy = Me![EnteredBy]

'Create message
bodytext = bodytext & "Date: " & DateEntered & Chr$(10)
bodytext = bodytext & Chr$(10)
bodytext = bodytext & "Application: " & App & Chr$(10)
bodytext = bodytext & Chr$(10)
bodytext = bodytext & "Issue: " & Issues & Chr$(10)
bodytext = bodytext & Chr$(10)
bodytext = bodytext & "Cause: " & Cause & Chr$(10)
bodytext = bodytext & Chr$(10)
bodytext = bodytext & "Resolution: " & Resolution & Chr$(10)
bodytext = bodytext & Chr$(10)
bodytext = bodytext & "Entered By: " & EnteredBy

'Make recipient string
rst.MoveFirst
EmailRecipients = ""
Do Until rst.EOF = True
EmailRecipients = EmailRecipients & rst!emailAddress & ";"
rst.MoveNext
Loop
EmailRecipients = Left(EmailRecipients, Len(EmailRecipients) - 1)

'E-mail above message
DoCmd.SendObject acSendNoObject, "", acFormatTXT, EmailRecipients, , , "Knowledge Transfer", bodytext, True

End Sub

hth
Jeff Bridgham
bridgham@purdue.edu

 

[snowyej]

That worked!! Thanks for all your help. My co-workers will be so impressed!

Elizabeht

 

[jebry]

Hi!

Your welcome! Now you're an Access Wizard!

Jeff Bridgham
bridgham@purdue.edu

 

[snowyej]

Through these newsgroups I keep learning more and more. Don't think I'm a wizard yet, but maybe someday!

Thanks again!

Elizabeth