I have several reports that I have made in access (2010) with it's reporting tool. What I would like to do is to send each of these reports off in an email to there corresponding departments. Sounds simple enough already huh?? Well I have the email function in place so that is finished but now what is happening is when I try to save the reports off in pdf format so they can be attached to the emails. I get a run time error, a "2046 The command or action 'Output To' isn't available now. I attempted to solve this by putting a "DoEvents" after the OutputTo and it continued to throw the same error. I then proceeded to make a timer function to cause the subroutine to "pause" for a few seconds and it worked for the first report but when the second report tried to produce BANG the same error.
For some background on what I am trying to accomplish here. I have a table that contains the PersonnelName, email, cc, subject respectively and are all text fields. The code calls a recordset to "lookup" the respective fields and produce an email with the report attached. I am not so worried about saving the report as attaching it to the email to be sent out as this is done daily. Oh I should probably mention this is all done by clicking a command button. So how would I go about producing the pdf for attachment?
My code as it stands now is as follows:
Code:
Private Sub cmdSendReports_Click()
Dim MySet As DAO.Recordset
Set MySet = CurrentDb.OpenRecordset("SELECT * FROM tblEmailAdd")
Do Until MySet.EOF
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
'Call function to start timer for file to finish
Pause (12)
'Output report to pdf file
DoCmd.OutputTo acOutputReport, MySet!ReportName, acFormatPDF, "C:\users\mgay\Desktop" & "\" & MySet!ReportName & ".pdf"
DoEvents
DoCmd.Close acReport, MySet!ReportName
'Create the outlook session
Set objOutlook = CreateObject("Outlook.Application")
'Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
'Add Recipient to the message
Set objOutlookRecip = .Recipients.Add(MySet!email)
objOutlookRecip.Type = olTo
'Add the CC Recipient to the message
Set objOutlookRecip = .Recipients.Add(MySet!CC)
objOutlookRecip.Type = olCC
'Add the BCC Recipient to the message
Set objOutlookRecip = .Recipients.Add("mgay@nativeoilfield.com")
objOutlookRecip.Type = olBCC
'Set the Subject, Body and Importance of the message.
.Subject = MySet!Subject
.Body = "Daily Error Report for " & Date
.Importance = olImportanceNormal
'Add Attachments to the message
If Not IsMissing(MySet!ReportName) Then
Set objOutlookAttach = .Attachments.Add("C:\Users\mgay\Desktop " & MySet!ReportName & ".pdf")
End If
'Resolve each recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
'Should we display the message before we send it?
If DisplayMsg Then
.Display
Else
.Send
' .Save
End If
End With
Set objOutlook = Nothing
MySet.MoveNext
Loop
End Sub
The function called above is as follows:
Code:
Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause
Dim PauseTime As Variant
Dim Start As Variant
PauseTime = NumberOfSeconds
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop
Exit_Pause:
Exit Function
Err_Pause:
MsgBox Err.Number & " _ " & Err.Description, vbCritical, "Pause()"
Resume Exit_Pause
End Function
I know this is a lot but I have been working for awhile on this. Could someone please offer some insight
Thank you in advance,
Max