Exporting Reports with subreports to excel

[GinaWhipp]

Is [EMAIL ADDRESSES] a Table or a Query? Is ID the name of a field?

 

[Snappy1263]

It is a table and yes ID is just a number field.

 

[Snappy1263]

What do you think is wrong with this.

thanks for your help.

 

[GinaWhipp]

I really don't know... Perhaps you can place a screen shot of the Table (in Design View) or Query (in SQL Veiw) you are using

 

[Snappy1263]

ok great i will do that thanks,

 

[Snappy1263]

here is a screen shot of the table in design view

thanks,

 

[GinaWhipp]

Well, that looks fine but I just saw something else. Use this line...

strAttachment = "S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & !ID & ".pdf"

 

[Snappy1263]

ok it got thru that now but it stops at the command button event procedure and highlights on Call eMailOverdue

Private Sub Command175_Click()

Call eMailOverdue


End Sub

 

[GinaWhipp]

Hmm, then still something wrong, please post what code you are using now.

 

[Snappy1263]

this is the module


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 [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\" & !ID & ".pdf"
strAttachment = "S:\ALLFILES\SUE'S STUFF\PROJECTS REPORT\" & !ID & ".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

This is the command button

Private Sub Command175_Click()

Call eMailOverdue


End Sub

 

[GinaWhipp]

Okay, found another problem, replace what you have for what is below...

 .To = rs.Fields("[EMAIL ADDRESSES]")

Side note, this would be much easier to red if you used Code Tags.

 

[Snappy1263]

IT STILL STOPS AT THE COMMAND BUTTON

Private Sub Command175_Click()

Call eMailOverdue


End Sub

I would be happy to do that but I dont know what you mean by Code Tags.

thanks,

 

[GinaWhipp]

Hmm, can you upload the database?

 

[Snappy1263]

When I sent you my database before, I had to send it to your personal email address.

 

[GinaWhipp]

Okay... you can do the same, my eMail has not changed.

 

[Snappy1263]

i sent the file

 

[GinaWhipp]

Got it will look at now...

 

[GinaWhipp]

Can't believe I missed this :banghead:

You need to change your Call line to...

 Call streMailOverdue

 

[GinaWhipp]

Hmm, one other observation... you do need to do some more studying on database normalization and design. What you have is working but in order for it to grow you should consider normalizing it.

 

[Snappy1263]

Hi its me again. I was wondering if you can help me with the same export reports to excel. What you did it working great. I want to do this in another database but it has different names and such so I was trying to do it myself but I think I am in over my head. I need to do the same thing export BOM to excel in the same fashion as we did. I was trying to just replace the different names but I think I was getting myself confused. Would you be able to help me again with this.

thanks,