Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Simple Email to Multiple Recipients Answer?? 2

Status
Not open for further replies.
Buckar00B0nzai

Buckar00B0nzai

Technical User
Jun 17, 2009
50
US
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?
 
Sort by date Sort by votes
This is what I came up with:

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
DoCmd.RunMacro "DeleteEmptyMacro"
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
End If


Msg = "Your Request Has Been Submitted"
Style = vbOKOnly
Title = "Shack"

Response = MsgBox(Msg, Style, Title)

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



End If
End Sub

I get the error message:

Macro Name: Exit Customer Entry Form Macro
Condition: True
Action Name: RunCommand
Arguments: 28
Error Number: 2950

The Macro runs the NewRecord Command

Any ideas?

Buckaroo Banzai
 
Upvote 0 Downvote
I tried the code from dhookum, and got this message: The command or action 'RecordsGoToNew' isn't available now.

This is the last piece of the puzzle. What the heck?!

Buckaroo Banzai
 
Upvote 0 Downvote
The code looks like this now:

Code: 
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
    DoCmd.RunMacro "DeleteEmptyMacro"
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
If MsgBox("Notification sent. Do you want to complete your request?", vbYesNo + vbQuestion, "Stay on Target") = vbYes Then
    DoCmd.GoToRecord , , acNext
    DoCmd.RunMacro "DeleteEmptyMacro"
  End If
End If


End Sub

Am I completely off-base here?

Buckaroo Banzai
 
Upvote 0 Downvote
Yes - if I add a command button I can add a new record.

Here is the original code:

Code: 
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
DoCmd.RunMacro "Exit Customer Entry Form Macro", 1
End If
End Sub

Then I inserted your code to yield:

Code: 
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
    DoCmd.RunMacro "DeleteEmptyMacro"
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
If MsgBox("Notification sent. Do you want to complete your request?", vbYesNo + vbQuestion, "Stay on Target") = vbYes Then
    DoCmd.RunCommand (acCmdRecordsGoToNew)
    DoCmd.RunMacro "DeleteEmptyMacro"
  End If
End If


End Sub

The new record function worked great prior to email automation, bringing up a new record OnClick for Command89. Is the group email function somehow disabling the new record piece?




Buckaroo Banzai
 
Upvote 0 Downvote
I tried setting the focus to [SUSPENSE_DATE] - a text box on the form. I received the error message again.

Code: 
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
    DoCmd.RunMacro "DeleteEmptyMacro"
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
If MsgBox("Notification sent. Do you want to complete your request?", vbYesNo + vbQuestion, "Stay on Target") = vbYes Then
    Me.SUSPENSE_DATE.SetFocus
    DoCmd.RunMacro "Exit Customer Entry Form Macro", 1
    DoCmd.RunMacro "DeleteEmptyMacro"
  End If
End If


End Sub

Buckaroo Banzai
 
Upvote 0 Downvote
The work-around for now is a modification of the code (see below) and the addition of an "Add a New Record" command button on the submission form. The customer hits the submit button (Command89), allows the email to send, and then has to press the "Complete" button manually. Its hard to believe that we can't automate the NewRecord function. Weird.

BUT...dhookum, I'm giving you another star because you put some serious work in on this one. Thanks - and if you come up with a solution, I will be watching this thread.

Code: 
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
    DoCmd.RunMacro "DeleteEmptyMacro"
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

End If

End Sub

Buckaroo Banzai
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheresAlwaysAWay
  • Locked
  • Question
Can Access be used to generate and respond to text messages? 1
Replies
6
Views
531
TheresAlwaysAWay
TheresAlwaysAWay
renigar
  • Question
Access 365: Search LastName in one table to put EmployeeID in another
Replies
8
Views
343
renigar
renigar
lmcate
  • Locked
  • Question
How to Open ACCESS (MDB) from an HTML Page 1
Replies
2
Views
477
lmcate
lmcate
pankajunk
  • Locked
  • Question
I want to set up a simple meeting b
Replies
2
Views
120
lameid
lameid
atadbitslow
  • Locked
  • Question
how to search data and display matches that include the user's input, but not necessarily match
Replies
5
Views
230
atadbitslow
atadbitslow

Part and Inventory Search

Sponsor

Back
Top