UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Email, Access 2016    
 
   
mike60smart
post Nov 8 2019, 07:12 AM
Post#1


UtterAccess VIP
Posts: 13,470
Joined: 6-June 05
From: Dunbar,Scotland


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

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Doug Steele
post Nov 8 2019, 08:14 AM
Post#2


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


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.


--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
DanielPineault
post Nov 8 2019, 08:27 AM
Post#3


UtterAccess VIP
Posts: 6,962
Joined: 30-June 11



QUOTE
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]

QUOTE
Separate the recipient names that you specify in this argument and in the Cc and Bcc arguments with a semicolon (wink.gif, 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.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
mike60smart
post Nov 8 2019, 08:28 AM
Post#4


UtterAccess VIP
Posts: 13,470
Joined: 6-June 05
From: Dunbar,Scotland


Hi Doug

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

Attached File  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]

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Doug Steele
post Nov 8 2019, 08:49 AM
Post#5


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


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!

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
mike60smart
post Nov 8 2019, 11:10 AM
Post#6


UtterAccess VIP
Posts: 13,470
Joined: 6-June 05
From: Dunbar,Scotland


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
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Doug Steele
post Nov 8 2019, 11:14 AM
Post#7


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Glad to help, Mike!

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2019 - 11:00 AM