[X]My Assistant

[mike60smart]

Hi Everyone

I am trying to use the following Code to send an Email to 2 Recipients but it errors on Line 130

T1 & T2 are Email addresses in Unbound Textboxes

Any help appreciated.

CODE

Private Sub cmdPrintPosition_Click()

10    On Error GoTo cmdPrintPosition_Click_Error
20    If Me.Dirty Then Me.Dirty = False
30      Dim strCrit As String
40      Dim strDocname As String
50    Dim strToWhom As String
60    Dim strMsgBody As String
70    Dim strSubject As String
80    Dim strtxtName As String
90    Dim strDir As String
100    Dim strName As String
    
110    strDocname = "rptTrainingRequired"
120    strSubject = "Training List"
130    strToWhom = Nz(Me![T1]) And Nz(Me![T2])
140    strMsgBody = "Find attached Training List."
150    If Me.txtPosition > "" Then
160      strCrit = strCrit & "([Position] = " & Chr(34) & Me.txtPosition & Chr(34) & ") AND "
170    End If

180    If strCrit > "" Then
190      strCrit = Left(strCrit, Len(strCrit) - 5)
        
200    End If
210    DoCmd.OutputTo acOutputReport, "rptTrainingRequired", acFormatPDF, , strCrit
  
220    DoCmd.OpenReport strDocname, acPreview
230    DoCmd.SendObject acSendReport, "rptTrainingRequired", acFormatPDF, strToWhom, , , strSubject, strMsgBody, True


240       On Error GoTo 0
250       Exit Sub

260 cmdPrintPosition_Click_Error:

270       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPrintPosition_Click, line " & Erl & "."

End Sub

[Doug Steele]

And is a Boolean operator. It doesn't work on strings...

I'm assuming that if you do have two email addresses, they should be separated by a comma. You can take advantage of the fact that + and & work differently for concatenation of strings to Null values, and try something like

CODE

  strToWhom = (Me![T1] + ", ") & Me![T2]

You really should check to ensure that they've put an email address into at least one of the two textboxes. You can simply check to insure that strToWhom isn't Null after the concatenation above.

[DanielPineault]

they should be separated by a comma.

The separator is dependent on your regional settings, but typically it is a semi-colon (not a comma). So it should be

CODE

strToWhom = (Me![T1] + "; ") & Me![T2]

Separate the recipient names that you specify in this argument and in the Cc and Bcc arguments with a semicolon (, or with the list separator set on the Number tab of the Regional Settings Properties dialog box in the Windows Control Panel.

Taken from https://docs.microsoft.com/en-us/office/vba...ocmd.sendobject


and I totally agree with Doug about validating the entries.

[mike60smart]

Hi Doug

The code is as shown below but I am now getting the following error:-

 error.PNG ( 4.05K )Number of downloads: 0


I get the same error if I trying sending to 1 or Both emails.

[Code]
Private Sub cmdPrintPosition_Click()

10 On Error GoTo cmdPrintPosition_Click_Error
20 If Me.Dirty Then Me.Dirty = False
30 Dim strCrit As String
40 Dim strDocname As String
50 Dim strToWhom As String
60 Dim strMsgBody As String
70 Dim strSubject As String
80 Dim strtxtName As String
90 Dim strDir As String
100 Dim strName As String

110 strDocname = "rptTrainingRequired"
120 strSubject = "Training List"
130 strToWhom = (Me![T1] + ", ") & Me![T2]
140 strMsgBody = "Find attached Training List."
150 If Me.txtPosition > "" Then
160 strCrit = strCrit & "([Position] = " & Chr(34) & Me.txtPosition & Chr(34) & ") AND "
170 End If

180 If strCrit > "" Then
190 strCrit = Left(strCrit, Len(strCrit) - 5)

200 End If
210 DoCmd.OutputTo acOutputReport, "rptTrainingRequired", acViewPreview, , strCrit

220 DoCmd.OpenReport strDocname, acPreview
230 DoCmd.SendObject acSendReport, "rptTrainingRequired", acFormatPDF, strToWhom, , , strSubject, strMsgBody, True


240 On Error GoTo 0
250 Exit Sub

260 cmdPrintPosition_Click_Error:

270 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPrintPosition_Click, line " & Erl & "."

End Sub

[Code]

[Doug Steele]

Your syntax is incorrect, Mike. You can't actually pass a criteria to the OutputTo command. Check DoCmd.OutputTo for details of what it should be.

That being said, your code seems to be opening the report three times: once with the OutputTo command, once with the OpenReport command and once with the SendObject command!

[mike60smart]

Hi Doug

Thanks for pointing out my errors it is much appreciated.

This now works ok:-

CODE

Private Sub cmdPrintPosition_Click()

10    On Error GoTo cmdPrintPosition_Click_Error
20    If Me.Dirty Then Me.Dirty = False
      Dim strCrit As String
      Dim strDocname As String
      Dim strToWhom As String
      Dim strMsgBody As String
      Dim strSubject As String
      Dim strtxtName As String
      Dim strDir As String
      Dim strName As String

30     strDocname = "rptTrainingRequired"
40     strSubject = "Training List"
50     strToWhom = (Me![T1] + "; ") & Me![T2]
60     strMsgBody = "Find attached Training List."


70     If Me.txtPosition > "" Then
80       strCrit = strCrit & "([Position] = " & Chr(34) & Me.txtPosition & Chr(34) & ") AND "
90     End If

100    If strCrit > "" Then
110      strCrit = Left(strCrit, Len(strCrit) - 5)
120    End If
      
130    DoCmd.OpenReport strDocname, acPreview, , strCrit
140    DoCmd.SendObject acSendReport, "rptTrainingRequired", acFormatPDF, strToWhom, , , strSubject, strMsgBody, True


150       On Error GoTo 0
160       Exit Sub

cmdPrintPosition_Click_Error:

170       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPrintPosition_Click, line " & Erl & "."

End Sub


Thanks again

[Doug Steele]

Glad to help, Mike!