Access VB Send Attachment Button By Grp Level No Data

[Mac1206]

When I click on the send attachment button for email or folder, you can see the PDF is attached with the group title but no data is showing so i'm missing something but can't figure out what:

Private Sub CmdSendSave_Click()
On Error GoTo ErrTrap
Dim Rep As String
Dim Qst As String, AddressString As String
Dim rst As DAO.Recordset

' Get name of selected report
Rep = Nz(Me.LstReports, "")

' Get concatenated string covering all
' selected mail addresses
Qst = "SELECT MailAddress FROM " & _
"T_MailAddresses WHERE SendMark = True"
AddressString = ""
Set rst = CurrentDb.OpenRecordset(Qst)
If Not rst.EOF Then
Do Until rst.EOF
AddressString = AddressString & ";" & rst.Fields(0)
rst.MoveNext
Loop
' Get rid of leading semicolon
AddressString = Mid(AddressString, 2)
End If

If Len(Rep) > 0 Then
SendReportPartsByGrpLevel Rep, AddressString
Else
MsgBox "No report selected"
End If

ExitPoint:
On Error Resume Next
rst.Close
Set rst = Nothing
On Error GoTo 0
Exit Sub

ErrTrap:
MsgBox Err.Number & " - " & Err.Description
Resume ExitPoint
End Sub

 

[Jacobhatz]

To the OP, if y

 

[Mac1206]

Hi All,

It's been a while, disregard the above code below is the code which i need help with and the attachments pertain to the code below....Long day

When I click on the send attachment button for email or folder, you can see the PDF is attached with the group title but no data is showing so i'm missing something but can't figure out what:

Option Compare Database
Option Explicit

Public ReportMode As String
Public ReportCaption As String
Public GrpSourceField As String
Public GrpArray() As String, GrpVal As String

Sub SendReportPartsByGrpLevel(Repname As String, _
Optional DestnMailAddress As String = "")
On Error Resume Next
' Outputs report parts as per group level, to pdf format
' and stores the files in folder ReportParts placed in the
' parent folder housing this db. ReportParts folder
' gets created programmatically if not already existing.
' If optional argument SendByMail is True, a copy is
' sent as attachment to eMail.
' Names of saved files as well as report captions
' reflect the group names.

Dim Cnt As Long, DestnFilePath As String
Dim RepFolderPath As String, Msg As String

' Get path for folder in which saved report files are
' to be stored.
RepFolderPath = CurrentProject.Path & "\ReportParts"

' Create Report Folder if not existing
Call Fn_MakeFolder(RepFolderPath)

' Close the report - if already open
DoCmd.Close acReport, Repname

' Open report in mode "A" and then close it.
' This stage is used to build an array of grouping
' field values.
' (In Access 2000, DoCmd.OpenReport does not
' have provision for opening the report hidden).
ReportMode = "A"
DoCmd.OpenReport Repname, _
acViewPreview
' Allow processing time (400 CPU cycles) before
' closing the report.
P_Wait 400
DoCmd.Close acReport, Repname

' Send report parts as per grp level
' Set flag - guiding report's internal code
ReportMode = "B"

' Cycle through the array of grouping field values
' and save / send report parts group-wise.
For Cnt = 0 To UBound(GrpArray)
' Get GrpVal and replace spaces by underscores
' (This is because GrpVal is used as part of
' DestnFilePath)
GrpVal = Replace(GrpArray(Cnt), " ", "_")
DestnFilePath = RepFolderPath & "\" & _
Repname & "_" & GrpVal & ".PDF"
ReportCaption = Repname & "_" & GrpVal

' Delete DestnFile of same name - if existing
Kill DestnFilePath

' Save the report as an pdf file
DoCmd.OutputTo acOutputReport, _
Repname, acFormatPDF, DestnFilePath

' If optional argument for destnMailAddress is
' supplied, send the report parts group-wise as
' attachments in PDF format.
If Len(DestnMailAddress) > 0 Then
DoCmd.SendObject acSendReport, Repname, _
acFormatPDF, DestnMailAddress, _
, , ReportCaption, "Sending Report " & _
"Part (Covering Group " & _
GrpVal & ")", False
End If
Next

' Use of Cnt instead of Cnt + 1 is adequate as Cnt is
' already incremented by 1 beyond the last used value,
' before exiting the For / Next loop.
Msg = Cnt & " Report parts (group-wise) saved " & _
"in folder below:" & vbCrLf & RepFolderPath
If Len(DestnMailAddress) > 0 Then
Msg = Msg & vbCrLf & vbCrLf & _
"(These have also been sent to destn " & _
"address as attachments)"
End If
MsgBox Msg, vbOKOnly, "Report " & _
Repname & " - Send / Save " & _
"(Group-wise) Completed"

' Reset ReportMode so as to permit normal
' independent opening of report
ReportMode = ""

On Error GoTo 0
End Sub

 

[welshgasman]

Perhaps a timing issue?
You are not giving the report time to be created before sending it.?

Go through the code in steps. Perhaps use DoEvents

Put a breakpoint on the SendObject and see what is created.

Might be worth commenting out On Error Resume Next until you have it working?

HTH

 

[Mac1206]

I'm not receiving any errors, that's what puzzling to me...I will try your solution though, thanks...

 

[welshgasman]

Well you will not receive any errors I would have thought because you are saying to Access to ignore them?