First, try .Recipients.Add("My.Email@Somewhere")
Second, code tags would make your code much easier to read (# on menu bar), as would grouping your declaration blocks. The way you have it, I have to continually scan the entire procedure to see if the parent of a child object (such as a mail item) has been properly declared/defined - and it could be anywhere. Your posted code might end up looking like
Code:
Option Compare Database
OPTION EXPLICIT 'should be turned on. VB editor, Tools>Options>Require Variable Declaration
Private Sub Submit_Click()
On Error GoTo errHandler 'you should have this when creating objects as code can terminate after error.
'leaving you with unrecovered pc memory allocation as well as database bloat
'Dim time1, time2 ** should not be needed as you point out
Dim fileName As String
Dim oApp As Object
Dim oEmail As Object
Set oApp = CreateObject("Outlook.Application")
Set oEmail = oApp.CreateItem(olMailItem)
fileName = Application.CurrentProject.Path & "\Safeguarding_Referral" & ".pdf"
DoCmd.OutputTo acOutputReport, "reportReferrals_Norfolk_New", acFormatPDF, fileName, False, "", , acExportQualityPrint
'so if the file cannot be found, error occurs, code stops, Nothings don't execute as previously mentioned
With oEmail
.Recipients.Add("My.Email@Somewhere")
.Subject = "My Subject"
.Body = "My Body"
.Importance = 2
.Sensitivity = 3
.Attachments.Add(fileName)
.Send
End With
DoCmd.SetWarnings False
DoCmd.OpenQuery "02c - Referral_Norfolk_New_Remove_Update_Flag", acViewNormal, acEdit
'DoCmd.SetWarnings True 'move this and see note **
DoCmd.Close acReport, "reportReferrals_Norfolk_New"
DoCmd.Close acForm, "Referrals_Norfolk_New"
DoCmd.Close acForm, "Referrals_Norfolk"
MsgBox "A secure email has beeen sent to the appropriate teams", vbInformation, "Email Status"
DoCmd.OpenForm "Opening Screen", acNormal, "", "", , acNormal
If Len(Dir(fileName)) > 0 Then Kill fileName 'simple, needs only 1 line thus no End If
exitHere:
Set oApp = Nothing
Set oMail = Nothing
DoCmd SetWarnings True '**if turned off & query fails, warnings can remain off.
'Would put it here only, but suggest you research .Execute method of DoCmd object instead
Exit Sub
errHandler:
Select Case err.Number
Case 31655 'Outlook cannot be started' 'Note the explanation for the error number, which you would leave in.
Msgbox "Outlook failed to start. Please contact the database adminstrator."
Case Else
Msgbox "Error " & err.Number & ": " & err.Description
End Select
Resume exitHere
End Sub
Easy to make mistakes when copying/pasting around so no guarantees. Plus much of the comments I entered would not be required in the final product. There is only one possible error (31655) shown for example but there are several possibilities related to Outlook, plus the failure of any query to run, path to not be found, etc., otherwise could be simpler as
errHandler:
Msgbox "Error " & err.Number & ": " & err.Description
Resume exitHere
EDIT: forgot to mention that it's customary to create and SET a recipients object, but I don't see why your way wouldn't work if you had the parentheses unless the reason is security constraints. Given that you state the error occurs on the recipients line, I don't see that being the case, but watch out for the second set that I added.