Send a file to Email

[swenger]

Hi,

I have an access file with code to send a file to Outlook which works fine. I copied that code to another access file but it gives me an error. Both on the same computer

The first part works that creates the excel file. It's the outlook that fails.

Here is the 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 Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim MailAttachment As Outlook.Attachments


Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
MailOutLook.Display
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

Here is the error

[Welshgasman]

Please use code tags when posting code.

Does the other DB have the outlook reference?

[swenger]

If you are referring to Tools / References in the VBA window then yes I compared that both files have the same lines checked.

[Welshgasman]

Ok, I am out of ideas if both files know about outlook

[CJ_London]

yes I compared that both files have the same lines checked.

suggest double check - the error message is saying it cannot find the library. Also looks like you don't have Option Explicit at the top of each module since there are a number of variables you have not declared

[Gicu]

What happens if you use late binding like this?

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,

[swenger]

I get this error

[swenger]

Do you know what I may be missing.Attachment 50243

[Gicu]

Your attachment doesn't work. What you are probably missing is Option Explicit at the top of all your VBA modules, having that would probably show you that you are missing a control (text box?) named Email on the form where this code is running (Me.Email).

Cheers,

[swenger]

Thanks. That helped. The issue was the .To=Me.Email so i just removed that line and the email opens with nothing completed in the To: field and we can just enter it in Outlook.

[Welshgasman]

So why not use the correct control that has the email address ?