Simple Email to Multiple Recipients Answer?? 2

[Buckar00B0nzai]

I have a customer database that allows customers to enter requests. After a customer enters a request, I need the database to send an email to all of the technicians (whose names and emails are stored and updated in a table (tbl Technicians). I have what I believe to be similar code that sends an individual technician an email when they are assigned to a request. That code is:

DoCmd.SendObject acSendQuery, "New Assignment", acFormatXLS, _
Me.TECHNICIAN_EMAIL.Value, , , _
"You Have Been Assigned a New Task: Request ID = " & Me.REQUEST_ID.Value & ", Subject = " & Me.SUBJECT.Value, , False

Where Me.TECHNICIAN_EMAIL.Value is a hidden field on the form that populates with the techs email whenever their name is pulled from a combo box. How can I make this work for all of the emails listed on tbl Technician in a single email?

 

[jsteph]

Buckar,
If you use Active Directory you can make an AD Group and send the single email to that group.

Or you could use Cdo, the Outlook object model, or a third-party smtp mailer such as Chilkat which allows for a multiple "send-to" list.
--Jim

 

[dhookom]

There is a generic concatenate function faq701-4233 that could gather all the email addresses.

Duane
Hook'D on Access
MS Access MVP

 

[Buckar00B0nzai]

OK - we operate on a secure server - and purchasing anything is a no-go. Talk me through yuor other options, please.

 

[dhookom]

Did you consider the concatenate funcion? Are you asking how to implement it or are you asking about something else?

Duane
Hook'D on Access
MS Access MVP

 

[Buckar00B0nzai]

First off - I have to say dhookum, you have been a huge help over the last few years, so thanks. Now for the concatenate function, I don't understand how to use it in order to combine all of the data in a certain column. I have about 9 rows of emails, and just want them to appear in a single field as email 1; email 2; etc. so that I can then use that field (TECHNICIAN_EMAIL) in this code:

DoCmd.SendObject acSendQuery, "New Assignment", acFormatXLS, _
Me.TECHNICIAN_EMAIL.Value, , , _
"You Have Been Assigned a New Task: Request ID = " & Me.REQUEST_ID.Value & ", Subject = " & Me.SUBJECT.Value, , False

Thanks man.

 

[Buckar00B0nzai]

Also - How can I restructure the code above to send just an email without an attachment?

Thank you, again.

 

[dhookom]

Try code like:

  Dim strTechEmails as String
  strTechEmails = Concatenate("SELECT TECHNICIAN_EMAIL FROM [tbl Technicians]",";")
  DoCmd.SendObject acSendQuery, "New Assignment", acFormatXLS, _
  strTechEmails, , , _
  "You Have Been Assigned a New Task: Request ID = " & Me.REQUEST_ID.Value & ",
  Subject = " & Me.SUBJECT.Value, , False

Duane
Hook'D on Access
MS Access MVP

 

[Buckar00B0nzai]

I get an error message that states "Sub or Function Not Defined" and it highlights concatenate.

 

[dhookom]

Did you copy the code from the FAQ into a new, blank module and save the module with a name like "modStringFunctions"?

Duane
Hook'D on Access
MS Access MVP

 

[Buckar00B0nzai]

OK - got it. Can you tell me how to adjust the module for ADO or DAO. I'm not sure which is appropriate. I do appreciate the babysitting on this one.

 

[Buckar00B0nzai]

This is what I've tried:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'(This SQL statement assumes FamID is numeric)
'===================================
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'===================================
'
'If the FamID is a string then the SQL would be
'===================================
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =""" & [FamID] & """") as FirstNames
'FROM tblFamily
'===================================

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
'Dim strConcat As String 'build return string
'With rs
'If Not .EOF Then
'.MoveFirst
'Do While Not .EOF
'strConcat = strConcat & _
'.Fields(0) & pstrDelim
'.MoveNext
'Loop
'End If
'.Close
'End With
'Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

This brings up the email, but there are no entries in the TO: area. What did I mess up?

 

[HarleyQuinn]

Un-comment the following code:

 'Dim strConcat As String 'build return string
    'With rs
        'If Not .EOF Then
            '.MoveFirst
            'Do While Not .EOF
                'strConcat = strConcat & _
                    '.Fields(0) & pstrDelim
                '.MoveNext
            'Loop
        'End If
        '.Close
    'End With
    'Set rs = Nothing

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[Buckar00B0nzai]

You guys are the best. Now I have one last question (in this thread). If I want the form to go to a new record after the customer hits the submit button, what am I doing wrong. My code is:

Private Sub Command89_Click()
DoCmd.RunMacro "Refresh Macro", 1
If IsNull(Me.SUBJECT.Value) Then
Dim Msg, Style, Title

Msg = "You did not enter a subject. If you select Yes, this record will be DELETED. Do you want to continue?"
Style = vbYesNo
Title = "Not So Fast, High Speed"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then ' User chose Yes.
DoCmd.RunMacro "Exit Customer Entry Form Macro", 1
Else ' User chose No.

End If

Else

Dim strTechEmails As String
strTechEmails = Concatenate("SELECT [Work Email] FROM [Personnel Table]", ";")
DoCmd.SendObject acSendQuery, "New Customer Assignment", acFormatXLS, _
strTechEmails, , , _
"ATTENTION - NEW CUSTOMER ENTRY: Request ID = " & Me.REQUEST_ID.Value & " FROM: " & Me.REQUESTOR.Value & ", SUBJECT = " & Me.SUBJECT.Value, , False

DoCmd.RunCommand (acCmdRecordsGoToNew)
End If
End Sub

I get this error: "The command or action 'RecordsGoToNew' isn't available now.

 

[Buckar00B0nzai]

Could it be because the "Allow/Deny" message box (w/status bar) is up and has the focus while the command is trying to run? Can I delay the new record command for 20 seconds or set the focus back to the form?

 

[dhookom]

I would probably put a MsgBox() after the SendObject to notify the user the email has been sent. Then move to the next record.

Duane
Hook'D on Access
MS Access MVP

 

[Buckar00B0nzai]

I can't (or don't think that I can) control the message box - warning me about security threats, etc. How could I insert a message box after?

 

[dhookom]

What? Aren't you using a msgbox earlier in the same code?

Dim strTechEmails As String
  strTechEmails = Concatenate("SELECT [Work Email] FROM [Personnel Table]", ";")
  DoCmd.SendObject acSendQuery, "New Customer Assignment", acFormatXLS, _
  strTechEmails, , , _
  "ATTENTION - NEW CUSTOMER ENTRY: Request ID = " & Me.REQUEST_ID.Value & " FROM: " & Me.REQUESTOR.Value & ", SUBJECT = " & Me.SUBJECT.Value, , False
  If MsgBox("Email sent. Do you want to move to the next record?", vbYesNo+ vbQuestion,"Move") = vbYes Then
    DoCmd.RunCommand (acCmdRecordsGoToNew)
  End If
End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

warning me about security threats
Do a google search for outlook object model guard

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Buckar00B0nzai]

Yes - I was just concerned that since the macro wouldn't run while the other message box was up, that this wouldn't work either. I'll give it a shot. Appreciate your time.