What happens if you use late binding like this?
Code:
Private Sub emailTest_Click()
On Error GoTo emailTest_Click_Err
Sbj = "Period " & RptDate & " until " & RptDate2
loc = "c:\temp"
flnm = loc & "Period" & Month(Now()) & Day(Now()) & ".xlsx"
DoCmd.OutputTo acOutputQuery, "qryStagedforCRM", acFormatXLSX, flnm, Autostart:=False
Dim appOutLook As Object 'Outlook.Application
Dim MailOutLook As Object 'Outlook.MailItem
Dim MailAttachment As Object 'Outlook.Attachments
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(0) 'olMailItem
'MailOutLook.Display '-you doing it later inside the With statement
With MailOutLook
.subject = Sbj
.To = Me.Email
.Attachments.Add flnm
.Display 'This will display the message for you to check and send yourself
' .Send ' This will send the message straight away
End With
emailTest_Click_Exit:
Exit Sub
emailTest_Click_Err:
MsgBox Error$
Resume emailTest_Click_Exit
End Sub
Cheers,