Exporting Reports with subreports to excel

ok I did that but it gives me the same error and highlights the

Private Sub Command175_click()

Call eMailOverdue
 
Hmm, please post here what you posted in your Module. Also, what is the name of your Module?
 
I know I will have to change some things I think but I just wanted to get it started...my module is called PROJECTS

Thanks,l


Option Compare Database
Option Explicit

Public olApp As Object
Public olNameSpace As Object
Public objRecipients As Object
Public objNewMail As Object 'Outlook.MailItem

Function InitializeOutlook() As Boolean
' This function is used to initialize the global Application and
' NameSpace variables.

On Error GoTo Init_Err
Set olApp = CreateObject("Outlook.Application", "LocalHost") ' Application object
Set olNameSpace = olApp.GetNamespace("MAPI") ' Namespace object
Set objNewMail = olApp.CreateItem(0)
InitializeOutlook = True
Init_Bye:

Exit Function
Init_Err:
InitializeOutlook = False
Resume Init_Bye

End Function


Function streMailOverdue() As String
On Error GoTo Error_Proc

DoCmd.Hourglass True

'Set global Application and NameSpace object variables, if necessary.
If olApp Is Nothing Then
If InitializeOutlook = False Then
MsgBox "Unable to initialize Microsoft Outlook!"
End If
End If

'Create new MailItem object.
Set objNewMail = olApp.CreateItem(0)

Dim strTo As String
Dim strSQL As String
Dim rs As Recordset
Dim strSubject As String
Dim strBody As String
Dim strAttachment As String

'Get the eMails for those who are to receive a Report
strSQL = "SELECT apAssociateID, apeMailAddress " & _
"FROM qryeMailAddresses"

Set rs = CurrentDb.OpenRecordset(strSQL)

'Save Report outside Access
With rs
.MoveFirst
Do While Not .EOF
DoCmd.OutputTo acOutputReport, "rpteMailReport", acFormatPDF, "\\S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & !apAssociateID & "-ToDoListFor_" & Format(Date, "mm.dd.yyyy") & ".pdf"
strAttachment = "\\S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & !apAssociateID & "-ToDoListFor_" & Format(Date, "mm.dd.yyyy") & ".pdf"
'Send eMail and Report
Set objNewMail = olApp.CreateItem(0)
With objNewMail
.To = rs.Fields("apeMailAddress")
.SUBJECT = Forms!EMAIL!SUBJECT
.BODY = "See attachment..."
If strAttachment <> "" Then
.Attachments.Add strAttachment
End If
.Send
End With
.MoveNext
Loop
'Delete the Reports
If Dir("\\S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\*.pdf") <> "" Then
Kill "\\S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\*.pdf"
End If
End With

rs.Close
Set rs = Nothing

Exit_Proc:
DoCmd.Hourglass False
Exit Function
Error_Proc:
Select Case Err.Number
Case 287:
'On Error Resume Next
Resume Exit_Proc 'ignore the error'
Case Else:
MsgBox "Error encountered streMailOverdue: " & Err.Description
Resume Exit_Proc 'display a message then exit'
End Select

End Function
 
Does the below match what is in your database?

'Get the eMails for those who are to receive a Report
Code: 
strSQL = "SELECT apAssociateID, apeMailAddress " & _
"FROM qryeMailAddresses"

Remember, the queries and field names need to match what is in your database.
 
SELECT [EMAIL ADDRESSES].ID, [EMAIL ADDRESSES].[EMAIL ADDRESSES]
FROM [EMAIL ADDRESSES]

Should be this.
 
Okay, well that is *part* of the problem, you need to go thru the Module and replace those names to match your own.
 
Ok I think I got everything. Here it is.


ption Compare Database
Option Explicit

Public olApp As Object
Public olNameSpace As Object
Public objRecipients As Object
Public objNewMail As Object 'Outlook.MailItem

Function InitializeOutlook() As Boolean
' This function is used to initialize the global Application and
' NameSpace variables.

On Error GoTo Init_Err
Set olApp = CreateObject("Outlook.Application", "LocalHost") ' Application object
Set olNameSpace = olApp.GetNamespace("MAPI") ' Namespace object
Set objNewMail = olApp.CreateItem(0)
InitializeOutlook = True
Init_Bye:

Exit Function
Init_Err:
InitializeOutlook = False
Resume Init_Bye

End Function


Function streMailOverdue() As String
On Error GoTo Error_Proc

DoCmd.Hourglass True

'Set global Application and NameSpace object variables, if necessary.
If olApp Is Nothing Then
If InitializeOutlook = False Then
MsgBox "Unable to initialize Microsoft Outlook!"
End If
End If

'Create new MailItem object.
Set objNewMail = olApp.CreateItem(0)

Dim strTo As String
Dim strSQL As String
Dim rs As Recordset
Dim strSubject As String
Dim strBody As String
Dim strAttachment As String

'Get the eMails for those who are to receive a Report
strSQL = "SELECT [EMAIL ADDRESSES].ID, [EMAIL ADDRESSES].[EMAIL ADDRESSES] " & _
"FROM [EMAIL ADDRESSES]"

Set rs = CurrentDb.OpenRecordset(strSQL)

'Save Report outside Access
With rs
.MoveFirst
Do While Not .EOF
DoCmd.OutputTo acOutputReport, "rptPROJECTS REPORT", acFormatPDF, "\\S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & ![EMAIL ADDRESSES].ID & "-ToDoListFor_" & Format(Date, "mm.dd.yyyy") & ".pdf"
strAttachment = "\\S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & ![EMAIL ADDRESSES].ID & "-ToDoListFor_" & Format(Date, "mm.dd.yyyy") & ".pdf"
'Send eMail and Report
Set objNewMail = olApp.CreateItem(0)
With objNewMail
.To = rs.Fields("[EMAIL ADDRESSES].[EMAIL ADDRESSES]")
.SUBJECT = Forms!EMAIL!SUBJECT
.BODY = "See attachment..."
If strAttachment <> "" Then
.Attachments.Add strAttachment
End If
.Send
End With
.MoveNext
Loop
'Delete the Reports
If Dir("\\S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\*.pdf") <> "" Then
Kill "\\S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\*.pdf"
End If
End With

rs.Close
Set rs = Nothing

Exit_Proc:
DoCmd.Hourglass False
Exit Function
Error_Proc:
Select Case Err.Number
Case 287:
'On Error Resume Next
Resume Exit_Proc 'ignore the error'
Case Else:
MsgBox "Error encountered streMailOverdue: " & Err.Description
Resume Exit_Proc 'display a message then exit'
End Select

End Function
 
Hmm, What is this?

Code: 
\\S:\ALLFILES\

If the drive is mapped to *S* then remove *\\* and rerun...
 
I click on the button then the error comes up Compile error: Sub or Function not defined and highlights the Call eMailOverdue
 
i just created a module and named in PROJECTS. Sorry Im such a pain lol.
 
Not a pain at all, that's why we're all here! :D

I just can't understand why it can't find the Function. Hmm, have you compiled your database? In the VB Editor select Debug > Compile and see if any errors come up.
 
I did Debug and error comes up saying Compile error: Method or data member not found on this line and highlighted on the word .ID.

DoCmd.OutputTo acOutputReport, "rptPROJECTS REPORT", acFormatPDF, "S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & ![EMAIL ADDRESSES].ID
 
Let's try...

Code: 
 DoCmd.OutputTo acOutputReport, "[rptPROJECTS REPORT]", acFormatPDF, "S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & ![EMAIL ADDRESSES].ID

Because your Report name has spaces in it. I would also confirm the spelling of the Report name.
 
ok i checked the spelling and then I Debugged and it stop at the same place.
 
Okay, well then let's confirm the Path... double check you have the FULL path.
 
ok here is the path....i understand up until this ![Email Addresses].ID.& "ToDoListFor_".....not sure why i need this??


DoCmd.OutputTo acOutputReport, "rptPROJECTS REPORT", acFormatPDF, "S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & ![EMAIL ADDRESSES].ID & "-ToDoListFor_" & Format(DATE, "mm.dd.yyyy") & ".pdf"
strAttachment = "S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & ![EMAIL ADDRESSES].ID & "-ToDoListFor_" & Format(DATE, "mm.dd.yyyy") & ".pdf"
 
You don't need that part, so...

Code: 
DoCmd.OutputTo acOutputReport, "rptPROJECTS REPORT", acFormatPDF, "S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & ![EMAIL ADDRESSES].ID & ".pdf"
Code: 
strAttachment = "S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & ![EMAIL ADDRESSES].ID & ".pdf"

...and try again
 
ok i changed the code and ran it and it stops on the command button event procedure

Private Sub Command175_Click()

Call eMailOverdue


End Sub


Then I debugged the module and it stops and highlights .ID still.

I took out the [EMAIL ADDRESSES].ID from the codes and then ran it but it stops on the event procedure for the command button. Not sure why I need this in the code.

thanks,
 
You must log in or register to reply here.

Similar threads

F
Help request for creating Reports with pictures
Replies
3
Views
192
Mark_
M
S
Is there a way to have print/save options for filtered form without utilizing right click?
Replies
10
Views
482
Gasman
Gasman
Safaa
How to display a specific number of records in a subreport linked to the original report with the patient code
2
Replies
20
Views
732
Pat Hartman
P
F
Solved Mask with sub-mask filtered by 2 combined drop-down boxes and a checkbox, opening two different reports depending on the checkbox
Replies
6
Views
330
Faoineag
F
P
Solved current record PDF issues
2
Replies
23
Views
447
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom