Hi I'm relatively new to VBA and could really do with some help please!! This is going to sound really long winded i'm sorry but I hope it will paint a picture of what i'm trying to do.
Basically here is what I want to do: I want a form (Selector) to have 4 check boxes and a Run command button. When the user clicks run another form (ReportsMenu) will appear giving various options. Importantly though, when the user clicks OK (in the ReportsMenu form) I want (depending on which check boxes are checked in the Selector form) to output the reports as pdf files. The code currently outputs the reports as snapshot or rich text format (depending on which radio button is checked in ReportsMenu) but they would prefer it if instead of this it just outputted straight to PDF when OK was clicked.
However currently the Selector form has 5 radio buttons (one is used to select all) so the user can only select all or one of the 4 options (which is why they want it to have check boxes instead so they can pick more than one but not necessarily all (which I have changed in the Selector form)). The problem (eventually got there i'm sorry!!) is with the ReportsMenu form. The original code for the ReportsMenu is very messy and, as i'm not very familiar with VBA (nor with the logic of the person who wrote the code), i'm not sure which parts to change (to print as PDF) and what to leave as it is. (I would just start the whole thing from scratch but there is some code linked to outlook that im not sure affects anything else at all or not)
Here is the code (it is very long I apologise): -
Option Compare Database
-
-
Function ExSnap1()
-
On Error GoTo Macro1_Err
-
-
Dim MyChoice As String
-
Dim MyReport As String
-
Dim MySchool As String
-
-
If [Forms]![ReportsMenu]![ReportFrame] = 1 Then
-
MyChoice = "Full Special Needs (Students and Invigilators) by Faculty"
-
MyReport = "FullSpecialNeeds"
-
Else
-
If [Forms]![ReportsMenu]![ReportFrame] = 2 Then
-
MyChoice = "FullTimetablebyFaculty"
-
MyReport = "FullTimetable"
-
Else
-
If [Forms]![ReportsMenu]![ReportFrame] = 3 Then
-
MyChoice = "StudentTimeTablesbyProgFaculty"
-
MyReport = "StudentTimeTables"
-
Else
-
If [Forms]![ReportsMenu]![ReportFrame] = 4 Then
-
If [Forms]![Selector]![chkall].Value = False Then
-
Call TimetableBySchool 'individual school
-
GoTo Ending
-
Else 'all schools
-
MyChoice = "Date Order Timetable with Locations"
-
MyReport = "DateOrderTimeTables"
-
End If
-
Else
-
If [Forms]![ReportsMenu]![ReportFrame] = 5 Then
-
Call AllSnap 'all of the above
-
Exit Function
-
End If
-
End If
-
End If
-
End If
-
End If
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
DoCmd.OutputTo acReport, MyChoice, "SnapshotFormat(*.snp)", "c:\" & MyReport & ".snp", False, ""
-
Else
-
DoCmd.OutputTo acReport, MyChoice, acFormatRTF, "c:\" & MyReport & ".rtf", False, ""
-
End If
-
-
-
-
If [Forms]![ReportsMenu]![ReportFrame] <> 4 Then
-
MySchool = [Forms]![Selector]![School]
-
-
Dim rst As Recordset
-
Set rst = CurrentDb.OpenRecordset("Select Contact, Faculty_ID from SchoolContacts where Faculty_ID = '" & MySchool & "'")
-
-
rst.MoveFirst
-
-
Dim myName As String
-
Dim mySchoolName As String
-
myName = rst.Fields(0).Value
-
End If
-
-
Dim oOutlook As Outlook.Application
-
Dim oMessage As Outlook.MailItem
-
Dim sFileNames As String
-
Dim oRecip As Outlook.Recipient
-
Dim oAttach As Outlook.Attachment
-
-
DoCmd.Echo True, "Emailing Report"
-
-
Set oOutlook = CreateObject("Outlook.Application")
-
-
Set oMessage = oOutlook.CreateItem(olMailItem)
-
-
With oMessage
-
.ReadReceiptRequested = True
-
If [Forms]![ReportsMenu]![ReportFrame] <> 4 Then
-
Set oRecip = .Recipients.Add(myName)
-
oRecip.TYPE = olTo
-
oRecip.Resolve
-
Else
-
MySchool = "All Schools"
-
End If
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Snapshot Report"
-
.Body = "Find attached Snapshot Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
.Body = .Body & "If you do not have Snapshot viewer, download it from http://www.microsoft.com/downloads/details.aspx?amp;amp;displaylang=en&familyid=B73DF33F-6D74-423D-8274-8B7E6313EDFB&displaylang=en" & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".snp")
-
-
Else
-
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Report"
-
.Body = "Find attached Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
-
End If
-
-
.Save
-
'.send
-
-
End With
-
Set oOutlook = Nothing
-
Macro1_Exit:
-
Exit Function
-
-
Macro1_Err:
-
MsgBox Error$
-
Resume Macro1_Exit
-
Ending:
-
End Function
-
-
Function TimetableBySchool() 'date order timetable for the selected school
-
On Error GoTo Macro1_Err
-
-
Dim MyChoice As String
-
Dim MyReport As String
-
Dim MySchool As String
-
-
MyChoice = "Date Order Timetable with Locations by School"
-
MyReport = "DateOrderTimeTablesBySchool"
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
DoCmd.OutputTo acReport, MyChoice, "SnapshotFormat(*.snp)", "c:\" & MyReport & ".snp", False, ""
-
Else
-
DoCmd.OutputTo acReport, MyChoice, acFormatRTF, "c:\" & MyReport & ".rtf", False, ""
-
End If
-
-
MySchool = [Forms]![Selector]![School]
-
-
Dim rst As Recordset
-
Set rst = CurrentDb.OpenRecordset("Select Contact, Faculty_ID from SchoolContacts where Faculty_ID = '" & MySchool & "'")
-
-
rst.MoveFirst
-
-
Dim myName As String
-
Dim mySchoolName As String
-
myName = rst.Fields(0).Value
-
-
Dim oOutlook As Outlook.Application
-
Dim oMessage As Outlook.MailItem
-
Dim sFileNames As String
-
Dim oRecip As Outlook.Recipient
-
Dim oAttach As Outlook.Attachment
-
-
DoCmd.Echo True, "Emailing Report"
-
-
Set oOutlook = CreateObject("Outlook.Application")
-
-
Set oMessage = oOutlook.CreateItem(olMailItem)
-
-
With oMessage
-
.ReadReceiptRequested = True
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Snapshot Report"
-
.Body = "Find attached Snapshot Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
.Body = .Body & "If you do not have Snapshot viewer, download it from http://www.microsoft.com/downloads/details.aspx?amp;amp;displaylang=en&familyid=B73DF33F-6D74-423D-8274-8B7E6313EDFB&displaylang=en" & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".snp")
-
-
Else
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Report"
-
.Body = "Find attached Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
-
End If
-
.Save
-
'.send
-
-
End With
-
Set oOutlook = Nothing
-
Macro1_Exit:
-
Exit Function
-
-
Macro1_Err:
-
MsgBox Error$
-
Resume Macro1_Exit
-
-
End Function
-
-
-
Function AllSnap() 'all of the above option
-
On Error GoTo Macro1_Err
-
-
Dim MyChoice As String
-
Dim MyReport As String
-
Dim MySchool As String
-
-
MyChoice = "Full Special Needs (Students and Invigilators) by Faculty"
-
MyReport = "FullSpecialNeeds"
-
MyChoice1 = "FullTimetablebyFaculty"
-
MyReport1 = "FullTimetableby"
-
MyChoice2 = "StudentTimeTablesbyProgFaculty"
-
MyReport2 = "StudentTimeTables"
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
DoCmd.OutputTo acReport, MyChoice, "SnapshotFormat(*.snp)", "c:\" & MyReport & ".snp", False, ""
-
DoCmd.OutputTo acReport, MyChoice1, "SnapshotFormat(*.snp)", "c:\" & MyReport1 & ".snp", False, ""
-
DoCmd.OutputTo acReport, MyChoice2, "SnapshotFormat(*.snp)", "c:\" & MyReport2 & ".snp", False, ""
-
Else
-
DoCmd.OutputTo acReport, MyChoice, acFormatRTF, "c:\" & MyReport & ".rtf", False, ""
-
DoCmd.OutputTo acReport, MyChoice1, acFormatRTF, "c:\" & MyReport1 & ".rtf", False, ""
-
DoCmd.OutputTo acReport, MyChoice2, acFormatRTF, "c:\" & MyReport2 & ".rtf", False, ""
-
End If
-
-
MySchool = [Forms]![Selector]![School]
-
-
Dim rst As Recordset
-
-
Set rst = CurrentDb.OpenRecordset("Select Contact, Faculty_ID from SchoolContacts where Faculty_ID = '" & MySchool & "'")
-
-
rst.MoveFirst
-
-
Dim myName As String
-
Dim mySchoolName As String
-
myName = rst.Fields(0).Value
-
-
Dim oOutlook As Outlook.Application
-
Dim oMessage As Outlook.MailItem
-
Dim sFileNames As String
-
Dim oRecip As Outlook.Recipient
-
Dim oAttach As Outlook.Attachment
-
Dim oAttach1 As Outlook.Attachment
-
Dim oAttach2 As Outlook.Attachment
-
-
DoCmd.Echo True, "Emailing Report"
-
-
Set oOutlook = CreateObject("Outlook.Application")
-
-
Set oMessage = oOutlook.CreateItem(olMailItem)
-
-
With oMessage
-
.ReadReceiptRequested = True
-
If [Forms]![ReportsMenu]![ReportFrame] <> 4 Then
-
Set oRecip = .Recipients.Add(myName)
-
Else
-
Set oRecip = .Recipients.Add("rgbf1")
-
End If
-
oRecip.TYPE = olTo
-
oRecip.Resolve
-
-
If [Forms]![Selector]![frmFileFormat] = 1 Then
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Snapshot Report"
-
.Body = "Find attached Snapshot Reports: " & MyChoice & ", " & MyChoice1 & " and " & MyChoice2 & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
.Body = .Body & "If you do not have Snapshot viewer, download it from http://www.microsoft.com/downloads/details.aspx?amp;amp;displaylang=en&familyid=B73DF33F-6D74-423D-8274-8B7E6313EDFB&displaylang=en" & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".snp")
-
Set oAttach1 = .Attachments.Add("c:\" & MyReport1 & ".snp")
-
Set oAttach2 = .Attachments.Add("c:\" & MyReport2 & ".snp")
-
Else
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Report"
-
.Body = "Find attached Snapshot Reports: " & MyChoice & ", " & MyChoice1 & " and " & MyChoice2 & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
Set oAttach1 = .Attachments.Add("c:\" & MyReport1 & ".rtf")
-
Set oAttach2 = .Attachments.Add("c:\" & MyReport2 & ".rtf")
-
-
-
End If
-
.Save
-
'.send
-
-
End With
-
Set oOutlook = Nothing
-
Macro1_Exit:
-
Exit Function
-
-
Macro1_Err:
-
MsgBox Error$
-
Resume Macro1_Exit
-
-
End Function
-
-
As you can see the code is very messy and hard to follow for a simpleton like me. If you could help me to adjust the code accordingly that would be great!
If you need me to clarify anything else let me know
Thankyou I really appreciate it!
Please look at this link about printing out to a PDF, I understand you are just learning VBA but you look like you are picking it up quickly and this should get you in the right direction. Lebans Report to PDF
-AJ
22 9241
Please look at this link about printing out to a PDF, I understand you are just learning VBA but you look like you are picking it up quickly and this should get you in the right direction. Lebans Report to PDF
-AJ
If you're using Access 2007, printing to a pdf is reduced to a simple one line command: - DoCmd.OutputTo acReport, MyChoice, acFormatPDF, "c:\" & MyReport & ".pdf", False, ""
Access 2007 RTM & 2007 Service Pack 1 require you have the Convert to PDF/XPS add-in installed.
Access 2007 Service Pack 2 adds native support for the conversions.
Now, adding this line into your routines above is the tricky part. If I have time later, and nobody else assists you, I'll revisit the code and make some suggestions. But let us know which version you and your clients are using first.
Thanks for the link ajalwaysus I will check it out now.
If you're using Access 2007, printing to a pdf is reduced to a simple one line command:
Expand|Select|Wrap|Line Numbers DoCmd.OutputTo acReport, MyChoice, acFormatPDF, "c:\" & MyReport & ".pdf", False, ""
Access 2007 RTM & 2007 Service Pack 1 require you have the Convert to PDF/XPS add-in installed.
Access 2007 Service Pack 2 adds native support for the conversions.
Now, adding this line into your routines above is the tricky part. If I have time later, and nobody else assists you, I'll revisit the code and make some suggestions. But let us know which version you and your clients are using first.
Thank you mate. I'm using Access 2003 if that helps at all?
Anyone at all able to help me please?
Thanks
If it helps, here are the names of the checkboxes I will be replacing the radio buttons with:
(OLD - Radio) [Forms]![ReportsMenu]![ReportFrame] = 1
(NEW - Check) SNInvigBySchool
(OLD - Radio) [Forms]![ReportsMenu]![ReportFrame] = 2
(NEW - Check) InvigTTBySchool
(OLD - Radio) [Forms]![ReportsMenu]![ReportFrame] = 3
(NEW - Check) StuTTBySchool
(OLD - Radio) [Forms]![ReportsMenu]![ReportFrame] = 4
(NEW - Check) DateOrderTimetables
(OLD - Radio) [Forms]![ReportsMenu]![ReportFrame] = 5
(NEW - Check) Select All
Although the code is over 200 lines long in post 1 there is, im guessing by looking at the code, only about 20 lines or so that actually need changing the problem is I don't really know how to do it. Help would be greatly appreciated!!
OK I have spent all week working through the code (on my own...) and I have now sorted out the problem with the code (I think so anyways!) All I need to do now is convert it to PDF (instead of rtf) so i'l retry that link thanks AJalwaysus
NeoPa 32,556
Expert Mod 16PB
I'm sure once you reply indicating how you got on with it then AJ will respond G_Diddy.
OK thanks NeoPa.
So far I have changed the code so that it now accomodates for check boxes rather than radio buttons and have also got rid of code that was redundant. The only problem i'm stuck with now is that I don't know how to change it to PDF. I have looked at that site and have found the function: -
Private Sub cmdReportToPDF_Click()
-
' Save the Report as a PDF document.
-
' The selected report is first exported to Snapshot format.
-
' The Snapshot file is then broken out into its
-
' component Enhanced Metafiles(EMF), one for each page of the report.
-
' Finally, the EMF's are converted to PDF pages within the master
-
' PDF document.
-
-
' The function call is:
-
'Public Function ConvertReportToPDF( _
-
'Optional RptName As String = "", _
-
'Optional SnapshotName As String = "", _
-
'Optional OutputPDFname As String = "", _
-
'Optional ShowSaveFileDialog As Boolean = False, _
-
'Optional StartPDFViewer As Boolean = True, _
-
'Optional CompressionLevel As Long = 150, _
-
'Optional PasswordOpen As String = "", _
-
'Optional PasswordOwner As String = "", _
-
'Optional PasswordRestrictions As Long = 0, _
-
'Optional PDFNoFontEmbedding as Long = 0, _
-
'Optional PDFUnicodeFlags As Long = 0 _
-
') As Boolean
-
-
' RptName is the name of a report contained within this MDB
-
' SnapshotName is the name of an existing Snapshot file
-
' OutputPDFname is the name you select for the output PDF file
-
' ShowSaveFileDialog is a boolean param to specify whether or not to display
-
' the standard windows File Dialog window to select an exisiting Snapshot file
-
' CompressionLevel - Resolution in DPI(Dots per Inch) to apply to embedded Images
-
' PasswordOpen - Users require to Open PDF
-
' PasswordOwner - Users require to modify PDF
-
' PasswordRestrictions - Restrictions for viewing/editing/printing PDF - See modReportToPDF for comments
-
' PDFNoFontEmbedding - Do not Embed fonts in PDF. Set to 1 to stop the
-
' default process of embedding all fonts in the output PDF. If you are
-
' using ONLY - any of the standard Windows fonts
-
' using ONLY - any of the standard 14 Fonts natively supported by the PDF spec
-
'The 14 Standard Fonts
-
'All version of Adobe's Acrobat support 14 standard fonts. These fonts are always available
-
'independent whether they're embedded or not.
-
'Family name PostScript name Style
-
'Courier Courier fsNone
-
'Courier Courier-Bold fsBold
-
'Courier Courier-Oblique fsItalic
-
'Courier Courier-BoldOblique fsBold + fsItalic
-
'Helvetica Helvetica fsNone
-
'Helvetica Helvetica-Bold fsBold
-
'Helvetica Helvetica-Oblique fsItalic
-
'Helvetica Helvetica-BoldOblique fsBold + fsItalic
-
'Times Times-Roman fsNone
-
'Times Times-Bold fsBold
-
'Times Times-Italic fsItalic
-
'Times Times-BoldItalic fsBold + fsItalic
-
'Symbol Symbol fsNone, other styles are emulated only
-
'ZapfDingbats ZapfDingbats fsNone, other styles are emulated only
-
-
' PDFUnicodeFlags controls how each metafile text record is interpreted in terms
-
' of Unicode and BiDi language. See modDocumentor for details.
-
'
-
' You must pass either RptName or SnapshotName or set the ShowSaveFileDialog param to TRUE.
-
' Any file names you pass to this function must include the full path. If you only include the
-
' filename for the output PDF then your document will be saved to your My Documents folder.
-
-
-
Dim blRet As Boolean
-
' Call our convert function
-
' Please note the last param signals whether to perform
-
' font embedding or not. I have turned font embedding ON for this example.
-
blRet = ConvertReportToPDF(Me.lstRptName, vbNullString, _
-
Me.lstRptName.Value & ".pdf", False, True, 150, "", "", 0, 0, 0)
-
' To modify the above call to force the File Save Dialog to select the name and path
-
' for the saved PDF file simply change the ShowSaveFileDialog param to TRUE.
-
-
End Sub
-
However i'm not sure how to call it in my code. In the code below I want to replace all rtf with PDF -
Option Compare Database
-
-
Function ExSnap1()
-
On Error GoTo Macro1_Err
-
-
Dim MyChoice As String
-
Dim MyChoiceOne As String
-
Dim MyChoiceTwo As String
-
Dim MyChoiceThree As String
-
Dim MyReport As String
-
Dim MyReportOne As String
-
Dim MyReportTwo As String
-
Dim MyReportThree As String
-
Dim MySchool As String
-
Dim Choice As Boolean
-
Choice = Nz(SNInvigBySchool, False)
-
Dim ChoiceOne As Boolean
-
ChoiceOne = Nz(InvigTTBySchool, False)
-
Dim ChoiceTwo As Boolean
-
ChoiceTwo = Nz(StuTTBySchool, False)
-
Dim ChoiceThree As Boolean
-
ChoiceThree = Nz(DateOrderTimetables, False)
-
Dim ChoiceFour As Boolean
-
ChoiceFour = Nz(Select_All, False)
-
-
If [Forms]![ReportsMenu]![SNInvigBySchool].Value = True Then
-
Choice = True
-
MyChoice = "Full Special Needs (Students and Invigilators) by Faculty"
-
MyReport = "FullSpecialNeeds"
-
Else
-
If [Forms]![ReportsMenu]![InvigTTBySchool].Value = True Then
-
ChoiceOne = True
-
MyChoiceOne = "FullTimetablebyFaculty"
-
MyReportOne = "FullTimetable"
-
Else
-
If [Forms]![ReportsMenu]![StuTTBySchool].Value = True Then
-
ChoiceTwo = True
-
MyChoiceTwo = "StudentTimeTablesbyProgFaculty"
-
MyReportTwo = "StudentTimeTables"
-
Else
-
If [Forms]![ReportsMenu]![DateOrderTimetables].Value = True Then
-
ChoiceThree = True
-
If [Forms]![Selector]![chkall].Value = False Then
-
Call TimetableBySchool 'individual school
-
GoTo Ending
-
Else 'all schools
-
MyChoiceThree = "Date Order Timetable with Locations"
-
MyReportThree = "DateOrderTimeTables"
-
End If
-
Else
-
If [Forms]![ReportsMenu]![Select All].Value = True Then
-
ChoiceFour = True
-
MyChoice = "Full Special Needs (Students and Invigilators) by Faculty"
-
MyReport = "FullSpecialNeeds"
-
MyChoiceOne = "FullTimetablebyFaculty"
-
MyReportOne = "FullTimetable"
-
MyChoiceTwo = "StudentTimeTablesbyProgFaculty"
-
MyReportTwo = "StudentTimeTables"
-
If [Forms]![Selector]![chkall].Value = False Then
-
Call TimetableBySchool 'individual school
-
GoTo Ending
-
Else 'all schools
-
MyChoiceThree = "Date Order Timetable with Locations"
-
MyReportThree = "DateOrderTimeTables"
-
-
Exit Function
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
-
If Choice = True Then
-
DoCmd.OutputTo acReport, MyChoice, acFormatRTF, "c:\" & MyReport & ".rtf", False, ""
-
Else
-
If ChoiceOne = True Then
-
DoCmd.OutputTo acReport, MyChoiceOne, acFormatRTF, "c:\" & MyReportOne & ".rtf", False, ""
-
Else
-
If ChoiceTwo = True Then
-
DoCmd.OutputTo acReport, MyChoiceTwo, acFormatRTF, "c:\" & MyReportTwo & ".rtf", False, ""
-
Else
-
If ChoiceThree = True Then
-
DoCmd.OutputTo acReport, MyChoiceThree, acFormatRTF, "c:\" & MyReportThree & ".rtf", False, ""
-
Else
-
If ChoiceFour = True Then
-
DoCmd.OutputTo acReport, MyChoice, acFormatRTF, "c:\" & MyReport & ".rtf", False, ""
-
DoCmd.OutputTo acReport, MyChoiceOne, acFormatRTF, "c:\" & MyReportOne & ".rtf", False, ""
-
DoCmd.OutputTo acReport, MyChoiceTwo, acFormatRTF, "c:\" & MyReportTwo & ".rtf", False, ""
-
DoCmd.OutputTo acReport, MyChoiceThree, acFormatRTF, "c:\" & MyReportThree & ".rtf", False, ""
-
End If
-
End If
-
End If
-
End If
-
End If
-
-
MySchool = [Forms]![Selector]![School]
-
-
Dim rst As Recordset
-
Set rst = CurrentDb.OpenRecordset("Select Contact, Faculty_ID from SchoolContacts where Faculty_ID = '" & MySchool & "'")
-
-
rst.MoveFirst
-
-
Dim myName As String
-
Dim mySchoolName As String
-
myName = rst.Fields(0).Value
-
-
Dim oOutlook As Outlook.Application
-
Dim oMessage As Outlook.MailItem
-
Dim sFileNames As String
-
Dim oRecip As Outlook.Recipient
-
Dim oAttach As Outlook.Attachment
-
Dim oAttach1 As Outlook.Attachment
-
Dim oAttach2 As Outlook.Attachment
-
Dim oAttach3 As Outlook.Attachment
-
-
DoCmd.Echo True, "Emailing Report"
-
-
Set oOutlook = CreateObject("Outlook.Application")
-
-
Set oMessage = oOutlook.CreateItem(olMailItem)
-
-
With oMessage
-
.ReadReceiptRequested = True
-
If [Forms]![ReportsMenu]![DateOrderTimetables] = True Then
-
MySchool = "All Schools"
-
Else
-
Set oRecip = .Recipients.Add(myName)
-
oRecip.TYPE = olTo
-
oRecip.Resolve
-
End If
-
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Report"
-
If Choice = True Then
-
.Body = "Find attached Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
-
If ChoiceOne = True Then
-
.Body = "Find attached Report: " & MyChoiceOne & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach1 = .Attachments.Add("c:\" & MyReportOne & ".rtf")
-
-
If ChoiceTwo = True Then
-
.Body = "Find attached Report: " & MyChoiceTwo & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach2 = .Attachments.Add("c:\" & MyReportTwo & ".rtf")
-
-
If ChoiceThree = True Then
-
.Body = "Find attached Report: " & MyChoiceThree & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach3 = .Attachments.Add("c:\" & MyReportThree & ".rtf")
-
-
If Choice = True And ChoiceOne = True Then
-
.Body = "Find attached Report: " & MyChoice & ", " & MyChoiceOne & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
Set oAttach1 = .Attachments.Add("c:\" & MyReportOne & ".rtf")
-
-
If Choice = True And ChoiceTwo = True Then
-
.Body = "Find attached Report: " & MyChoice & ", " & MyChoiceTwo & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
Set oAttach2 = .Attachments.Add("c:\" & MyReportTwo & ".rtf")
-
-
If Choice = True And ChoiceThree = True Then
-
.Body = "Find attached Report: " & MyChoice & ", " & MyChoiceThree & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
Set oAttach3 = .Attachments.Add("c:\" & MyReportThree & ".rtf")
-
-
If ChoiceOne = True And ChoiceTwo = True Then
-
.Body = "Find attached Report: " & MyChoiceOne & ", " & MyChoiceTwo & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach1 = .Attachments.Add("c:\" & MyReportOne & ".rtf")
-
Set oAttach2 = .Attachments.Add("c:\" & MyReportTwo & ".rtf")
-
-
If ChoiceOne = True And ChoiceThree = True Then
-
.Body = "Find attached Report: " & MyChoiceOne & ", " & MyChoiceThree & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach1 = .Attachments.Add("c:\" & MyReportOne & ".rtf")
-
Set oAttach3 = .Attachments.Add("c:\" & MyReportThree & ".rtf")
-
-
If ChoiceTwo = True And ChoiceThree = True Then
-
.Body = "Find attached Report: " & MyChoiceTwo & ", " & MyChoiceThree & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach2 = .Attachments.Add("c:\" & MyReportTwo & ".rtf")
-
Set oAttach3 = .Attachments.Add("c:\" & MyReportThree & ".rtf")
-
-
If Choice = True And ChoiceOne = True And ChoiceTwo = True Then
-
.Body = "Find attached Report: " & MyChoice & ", " & MyChoiceOne & ", " & MyChoiceTwo & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
Set oAttach1 = .Attachments.Add("c:\" & MyReportOne & ".rtf")
-
Set oAttach2 = .Attachments.Add("c:\" & MyReportTwo & ".rtf")
-
-
If Choice = True And ChoiceOne = True And ChoiceThree = True Then
-
.Body = "Find attached Report: " & MyChoice & ", " & MyChoiceOne & ", " & MyChoiceThree & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
Set oAttach1 = .Attachments.Add("c:\" & MyReportOne & ".rtf")
-
Set oAttach3 = .Attachments.Add("c:\" & MyReportThree & ".rtf")
-
-
If Choice = True And ChoiceTwo = True And ChoiceThree = True Then
-
.Body = "Find attached Report: " & MyChoice & ", " & MyChoiceTwo & ", " & MyChoiceThree & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
Set oAttach2 = .Attachments.Add("c:\" & MyReportTwo & ".rtf")
-
Set oAttach3 = .Attachments.Add("c:\" & MyReportThree & ".rtf")
-
-
If ChoiceOne = True And ChoiceTwo = True And ChoiceThree = True Then
-
.Body = "Find attached Report: " & MyChoiceOne & ", " & MyChoiceTwo & ", " & MyChoiceThree & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach1 = .Attachments.Add("c:\" & MyReportOne & ".rtf")
-
Set oAttach2 = .Attachments.Add("c:\" & MyReportTwo & ".rtf")
-
Set oAttach3 = .Attachments.Add("c:\" & MyReportThree & ".rtf")
-
-
If Choice = True And ChoiceOne = True And ChoiceTwo = True And ChoiceThree = True Then
-
.Body = "Find attached Report: " & MyChoice & ", " & MyChoiceOne & ", " & MyChoiceTwo & ", " & MyChoiceThree & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
Set oAttach1 = .Attachments.Add("c:\" & MyReportOne & ".rtf")
-
Set oAttach2 = .Attachments.Add("c:\" & MyReportTwo & ".rtf")
-
Set oAttach3 = .Attachments.Add("c:\" & MyReportThree & ".rtf")
-
-
If ChoiceFour = True Then
-
.Body = "Find attached Report: " & MyChoice & ", " & MyChoiceOne & ", " & MyChoiceTwo & ", " & MyChoiceThree & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
Set oAttach1 = .Attachments.Add("c:\" & MyReportOne & ".rtf")
-
Set oAttach2 = .Attachments.Add("c:\" & MyReportTwo & ".rtf")
-
Set oAttach3 = .Attachments.Add("c:\" & MyReportThree & ".rtf")
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
-
-
.Save
-
'.send
-
-
End With
-
Set oOutlook = Nothing
-
Macro1_Exit:
-
Exit Function
-
-
Macro1_Err:
-
MsgBox Error$
-
Resume Macro1_Exit
-
Ending:
-
End Function
-
-
Function TimetableBySchool() 'date order timetable for the selected school
-
On Error GoTo Macro1_Err
-
-
Dim MyChoice As String
-
Dim MyReport As String
-
Dim MySchool As String
-
-
MyChoice = "Date Order Timetable with Locations by School"
-
MyReport = "DateOrderTimeTablesBySchool"
-
-
DoCmd.OutputTo acReport, MyChoice, acFormatRTF, "c:\" & MyReport & ".rtf", False, ""
-
-
MySchool = [Forms]![Selector]![School]
-
-
Dim rst As Recordset
-
Set rst = CurrentDb.OpenRecordset("Select Contact, Faculty_ID from SchoolContacts where Faculty_ID = '" & MySchool & "'")
-
-
rst.MoveFirst
-
-
Dim myName As String
-
Dim mySchoolName As String
-
myName = rst.Fields(0).Value
-
-
Dim oOutlook As Outlook.Application
-
Dim oMessage As Outlook.MailItem
-
Dim sFileNames As String
-
Dim oRecip As Outlook.Recipient
-
Dim oAttach As Outlook.Attachment
-
-
DoCmd.Echo True, "Emailing Report"
-
-
Set oOutlook = CreateObject("Outlook.Application")
-
-
Set oMessage = oOutlook.CreateItem(olMailItem)
-
-
With oMessage
-
.ReadReceiptRequested = True
-
.Subject = Format(Now(), "yyyy-mm-dd") & " Exam Timetabling: Report"
-
.Body = "Find attached Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".rtf")
-
-
.Save
-
'.send
-
-
End With
-
Set oOutlook = Nothing
-
Macro1_Exit:
-
Exit Function
-
-
Macro1_Err:
-
MsgBox Error$
-
Resume Macro1_Exit
-
-
End Function
-
I really appreciate your help!
g diddy,
What the Lebans code does, is that you need to open your report and then you are supposed to feed it to the Leban function from the link, it will in turn convert it to a PDF.
Here is a sample of what I do... -
Private Sub ReportToPDF()
-
Dim blRet As Boolean
-
Dim strFullPath As String
-
-
DoCmd.OpenReport "MY_REPORT", acViewPreview, , , acHidden
-
strFullPath = "C:\MY_REPORT.pdf"
-
' Call our convert function
-
' Please note the last param signals whether to perform
-
' font embedding or not. I have turned font embedding ON for this example.
-
blRet = ConvertReportToPDF("MY_REPORT", vbNullString, _
-
strFullPath, False, False, 150, "", "", 0, 0, 0)
-
' To modify the above call to force the File Save Dialog to select the name and path
-
' for the saved PDF file simply change the ShowSaveFileDialog param to TRUE.
-
DoCmd.Close acDefault, "MY_REPORT", acSaveYes
-
End Sub
-
This is with the expectation that you have saved the 2 modules Leban provided in his sample DB to your DB.
Let me know if this makes sense,
AJ
NeoPa 32,556
Expert Mod 16PB @g diddy
I hope you appreciate that doing that for you is not what we're about.
We will provide examples of what's required but we expect you to go through your own code and apply it where necessary. We don't need to see large quantities of your code in the post.
Thank you for your quick response ajalwaysus I will give it a try now.
you need to open your report and then you are supposed to feed it to the Leban function from the link, it will in turn convert it to a PDF.
I've changed the code with one check box for the time being to test it and I get the compile error: Sub or function not defined with the following highlighted (ConvertReportToPDF). -
blRet = ConvertReportToPDF("MyChoice", vbNullString, _
-
strFullPath, False, False, 150, "", "", 0, 0, 1)
-
Just wondering where was ConvertReportToPDF defined? Or is this something that I need to change for my code?
Thanks
Best Regards
Did you import the "modReportToPDF" and "clsCommonDialog" modules that were in the Leban database? Because you need this before you can run my code.
-AJ
No I hadn't, my apologies. I have added them in and now get the error Compile Error - Exit Sub not allowed in Function or Property. Then the "Exit Sub" line is highlighted. Sorry to be a pain but I really am a novice :$ I tried removing it then I got an error saying that Macro1 wasn't there. Here is the end of the code: -
Macro1_Exit:
-
Exit Sub
-
-
Macro1_Err:
-
MsgBox Error$
-
Resume Macro1_Exit
-
Ending:
-
End Function
-
With just a quick glance, because this is a whole lot of code, I think it is because you are using an "Exit Sub" but the final line in your code indicates this is a Function. Perhaps replacing "Exit Sub" with "Exit Function" will work.
Let me know if this works,
-AJ
Yes that was the problem there, school boy error by me missing that! thanks for spotting it so quickly!
I now get the error: -
The report name 'MyChoice' you entered in either the property
-
sheet or macro is misspelled or refers to a report that doesn't exist.
-
Dim MyChoice As String is the variable and it holds the name of the report. I have a few of these variables as there are a few reports. MyReport is the name of the file when it is opened/sent/saved - it has worked with rtf so i've obviously messed up with the new code somewhere. How do you call the variable in the code?
To give you an idea of what I have for the check box I am testing: -
*chunk of code declaring variables etc missed out*
-
MyChoice = "Full Special Needs (Students and Invigilators) by Faculty"
-
MyReport = "FullSpecialNeeds"
-
DoCmd.OpenReport "MyChoice", acViewPreview, , , acHidden
-
strFullPath = "C:\MyReport.pdf"
-
blRet = ConvertReportToPDF("MyChoice", vbNullString, _
-
strFullPath, False, False, 150, "", "", 0, 0, 1
-
-
* big chunk of code to do with other things missed out*
-
-
.Body = "Find attached Report: " & MyChoice & " for School Code: " & MySchool & vbCrLf & vbCrLf
-
Set oAttach = .Attachments.Add("c:\" & MyReport & ".pdf")
-
If I can get one working I should hopefully be able to fix the rest myself. The only problem is i'm useless at troubleshooting!!
Thanks again!!
NeoPa 32,556
Expert Mod 16PB
In lines #4 & #6 you use the string "MyChoice" instead of the variable MyChoice. Have fun.
I love you
Thank you NeoPa and Ajalwaysus for all your help!!
I really appreciate it. Thank you :D
NeoPa 32,556
Expert Mod 16PB
Well, it was mainly AJ's knowledge of the PDF stuff that did the trick, but always glad to see a happy camper anyway :)
It's an all around effort. Glad it works. =)
-AJ
gawd the hoops access and adobe make you go through to do what should be simple. complete failure on their part.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: LarsenMTL |
last post by:
I have a long running python cgi which in the end returns a link to a
pdf file. While it runs it generates a log that uses stdout right into
the html. I use the sys.stdout.flush() to make this log...
|
by: Jacek Dziedzic |
last post by:
Hello!
I have a piece of code that needs to display a formatted
table of pointers using streams, with the pointers represented
as hex values. It looks more or less like this:
#include...
|
by: Andy |
last post by:
Hi
I'm really stuck outputting a double number to the console with three
decimal places if the furthest right value is a zero.
I can coutput the number 4.546 as 4.546 but then if I output...
|
by: cephelo |
last post by:
I have no problems outputting the attribute value when the node is in
context, for example, @id when an <status> node is in context.
However, I am having trouble outputting it in a <xsl:value-of...
|
by: DeniseY |
last post by:
I have an Access report that is created on the fly by the user selecting the fields to be included. The Access report comes out fine, but I want it to automatically output to an Excel spreadsheet....
|
by: Peter Nimmo |
last post by:
Hi,
I am writting a windows application that I want to be able to act as if
it where a Console application in certain circumstances, such as error
logging.
Whilst I have nearly got it, it...
|
by: Matt |
last post by:
Hello. I'm having a very strange problem that I would like ot check
with you guys.
Basically whenever I insert the following line into my programme to
output the arguments being passed to the...
|
by: phong.lee |
last post by:
Hello all,
I was wondering if someone can assist me in outputting 6 reports into
a pdf file? I created a macro that generates the 6 reports and right
now it's saved as a snapshot on my drive. ...
|
by: billelev |
last post by:
This is probably a very easy question to answer:
I have been outputting some text to a message box, similar to the following:
strOutput = "---" & Chr(10) & Chr(10)
strOutput = strOutput &...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |