Creating Multiple Recipients

  • Thread starter Thread starter RedHeadedMonster
  • Start date Start date
R

RedHeadedMonster

Im trying to create an automated email. It works fine, except for one small
problem. If the query Im pulling from has more than one email listing, my
program is only pulling the first name in the list instead of all emails.
How do I get it to see the recipients as being more than one name?

Thanx in advance.
RHM
 
I guess I should of stated that Im using Access 2003, so Im pulling the email
names from a query within the database. Basically the email is being created
with an OnClick event from a form.

Sorry, new to the forum.
 
In
RedHeadedMonster said:
Im trying to create an automated email. It works fine, except for
one small problem. If the query Im pulling from has more than one
email listing, my program is only pulling the first name in the list
instead of all emails. How do I get it to see the recipients as being
more than one name?
Click to expand...

Posting your code would help. I'm guessing that you need to loop
through a recordset opened on your query to build the list of
recipients -- something along these lines:

Dim rsRecips As DAO.Recordset
Dim strTo As String

Set rsRecips = CurrentDb.OpenRecordset("YourQuery")
With rsRecips
Do Until .EOF
If Len(!EmailAddress & vbNullString) > 0 Then
strTo = strTo & ";" & !EmailAddress
End If
.MoveNext
Loop
.Close
End With

If Len(strTo) > 0 Then

strTo = Mid$(strTo, 2) ' drop leading ";"

DoCmd.SendObject To:=strTo, _
' ... rest of SendObject arguments needes here'

End If
 
Ok I tried your code, now it lists the first email address over and over
again. It doesnt change to the next email in the list. i.e., if there are 2
emails in the list it lists the first one twice, if there are 50 names in the
list it lists the first email 50 times. Any idea why its seeing that there
are more than one, but only using the first email?
Thanx
RHM
 
In
RedHeadedMonster via AccessMonster.com said:
Ok I tried your code, now it lists the first email address over and
over again. It doesnt change to the next email in the list. i.e.,
if there are 2 emails in the list it lists the first one twice, if
there are 50 names in the list it lists the first email 50 times.
Any idea why its seeing that there are more than one, but only using
the first email?
Click to expand...

Please post your code and the SQL of query that you are opening with
your recordset.
 
I ended up using the following code and got the email to work pulling the
right names for the TO field.
So now a new question. I have one hyperlink in the body of the the email
message. It shows up in the email appearing as an address between #. But
its not clickable in the email. Any suggestions?
Thanx for getting me on the right track with your previous answers.
RHM

Private Sub cmdEmail_Click()

Dim db As Database
Set db = CurrentDb

Dim olApp As New Outlook.Application
Dim olMsg As Outlook.MailItem
Dim olRecipient As Outlook.Recipient

Dim msgTo As String
Dim msgSubject As Variant
Dim msgBody As String


Dim rst As DAO.Recordset
Dim i As Integer

Set rst = Me.Recordset

With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With


For i = 1 To rst.RecordCount
If Len(Me.Email) > 0 Then
If msgTo = "" Then
msgTo = Me.Email
Else
msgTo = msgTo & ";" & Me.Email
End If
rst.MoveNext
End If
Next i


Dim hyp As Hyperlink
Set hyp = Me.WorkingFolder.Hyperlink

msgSubject = Me.DocumentNo & " is ready for approval"

msgBody = "Document: " & Me.DocumentNo & vbCrLf & _
"Title: " & Me.Title & vbCrLf & vbCrLf & _
"The above referenced document is ready for your approval." &
vbCrLf & vbCrLf & _
"The Document is located in the Document Working Folder at: " &
vbCrLf & _
" " & Me.WorkingFolder & vbCrLf & vbCrLf & _
"Please reply to this email and indicate your
approval by: " & vbCrLf & _
" " & Me.Date & vbCrLf & vbCrLf & _
"Thank You."


Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(olMailItem)

With olMsg
.BodyFormat = olFormatHTML
.To = msgTo
.Subject = msgSubject
.Body = msgBody
.Importance = olImportanceHigh
.Display
End With


End Sub
 
In
RedHeadedMonster via AccessMonster.com said:
I ended up using the following code and got the email to work
pulling the right names for the TO field.
So now a new question. I have one hyperlink in the body of the the
email message. It shows up in the email appearing as an address
between #. But its not clickable in the email. Any suggestions?
Thanx for getting me on the right track with your previous answers.
RHM

Private Sub cmdEmail_Click()

Dim db As Database
Set db = CurrentDb

Dim olApp As New Outlook.Application
Dim olMsg As Outlook.MailItem
Dim olRecipient As Outlook.Recipient

Dim msgTo As String
Dim msgSubject As Variant
Dim msgBody As String


Dim rst As DAO.Recordset
Dim i As Integer

Set rst = Me.Recordset

With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With


For i = 1 To rst.RecordCount
If Len(Me.Email) > 0 Then
If msgTo = "" Then
msgTo = Me.Email
Else
msgTo = msgTo & ";" & Me.Email
End If
rst.MoveNext
End If
Next i


Dim hyp As Hyperlink
Set hyp = Me.WorkingFolder.Hyperlink

msgSubject = Me.DocumentNo & " is ready for approval"

msgBody = "Document: " & Me.DocumentNo & vbCrLf & _
"Title: " & Me.Title & vbCrLf & vbCrLf & _
"The above referenced document is ready for your
approval." & vbCrLf & vbCrLf & _
"The Document is located in the Document Working Folder
at: " & vbCrLf & _
" " & Me.WorkingFolder & vbCrLf & vbCrLf & _
"Please reply to this email and indicate your
approval by: " & vbCrLf & _
" " & Me.Date & vbCrLf & vbCrLf & _
"Thank You."


Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(olMailItem)

With olMsg
.BodyFormat = olFormatHTML
.To = msgTo
.Subject = msgSubject
.Body = msgBody
.Importance = olImportanceHigh
.Display
End With


End Sub
Click to expand...

This is one of the reasons I don't much care for hyperlink fields. I'd
rather store the link as a simple text field, and then use code to treat
it as a hyperlink when I want.

You can probably get rid of the enclosing hash marks (#) by changing
this:
" " & Me.WorkingFolder & vbCrLf & vbCrLf & _
Click to expand...

to this:

" " & Me.WorkingFolder.HyperLink.Address & _
vbCrLf & vbCrLf & _

I don't know if that will make it clickable in the e-mail or not. If
not, you could try prefixing it with "file://", like this:

" file://" & Me.WorkingFolder.HyperLink.Address & _
vbCrLf & vbCrLf & _

That's assuming that WorkingFolder holds a file path. If this is going
out in an e-mail, I hope that the path in question is a network path
that will be valid for all the recipients. Or maybe it's a web address,
in which case you'd use an "http://" prefix instead of "file://".

Given that this is an HTML-formatted e-mail, I suppose you might have to
wrap the URL in an actual HTML anchor tag (<A HREF=...>...</A>). I
don't know whether that will be necessary or not.
 
Back
Top