Multiple recipients in same Access created email

HVACMAN24 · Feb 17, 2011

I have this reminder form so when I run it it tells me who hasnt done thier monthly assessment then I click a button to send an reminder by email. But it does everyone seperately and I'd rather just send one email. Is there an easy way to put multiple recipients in a email created by Access? I've tried making an email array but it didnt seem to iterate through all the emails and list them in the email To: box. Any thoughts?

Heres a look at what I have:

Private Sub send_cmd_Click()
On Error GoTo Err_send_cmd_Click

Dim begin As String
Dim beginshare As String
Dim finish As String
Dim finishshare As String
Dim link As String

finishshare = ", by the end of this month."
finish = " by the end of this month."
beginshare = "Please remember to do a 5S assessment for the shared area, "
begin = "Please remember to do a 5S assessment for "
link = "M:\Engineering\Cooling Dev\ACE\Cooling Lab ACE\Cooling Lab New 5S\New 5S\5S Self-Scorecard.mdb"

DoCmd.GoToRecord , , acLast
last = Form.CurrentRecord
DoCmd.GoToRecord , , acFirst

For i = 1 To last
If Area_Type.Value = "Lab" Or Area_Type.Value = "Office" Then
DoCmd.SendObject , , , Email.Value, , , "5S Reminder", begin & Area.Value & finish & " You can find the database at " & link, True
Else
DoCmd.SendObject , , , Email.Value, , , "5S Reminder", beginshare & Area.Value & finishshare & " You can find the database at " & link, True
End If
If Form.CurrentRecord < last Then
DoCmd.GoToRecord , , acNext
End If
Next

Exit_send_cmd_Click:
Exit Sub
Err_send_cmd_Click:
MsgBox Err.Description
Resume Exit_send_cmd_Click

End Sub

hbrems · Feb 17, 2011

I used to do this as follows:

Code:

Dim to As String

' Create to list
to = ""
For i = 1 To last
     to = to & Email.Value & ";"
Next

' Create message
message = "<HTML><BODY><P>Type something here...</P></BODY></HTML>

' Send mail
Set OutApp = CreateObject("outlook.application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
    .to = to
    .cc = "whoeveryouwant@yourcompany.com"
    .subject = "yoursubject"
    .HTMLBody = message
    .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing

E-mails are send via Outlook, not sure if that is an issue for you. You can get very creative with the HTML.

HVACMAN24 · Feb 17, 2011

Thanks that worked great.

Multiple recipients in same Access created email

HVACMAN24

Registered User.

hbrems

has no clue...

HVACMAN24

Registered User.

Similar threads

Users who are viewing this thread