PDF to SendObject

  • Thread starter Thread starter AJ
  • Start date Start date
A

AJ

I have just written a VBA script (below) that successfully prints a single
record to pre-defined report, then uses SendObject to load it onto an E-mail
(and inserts the E-mail address, etc ). Not bad for a newbie.

But the report has some formatting that makes it difficult to use HTML, RTF,
etc. so I am currently using the Snapshot format, for the report. But what I
see is that not everyone can read that, who I might send it to. I think you
need MS Access loaded to read that format.

What about PDF?

I have Acrobat 4.0 and also the open source PDF Creator on my machine. How
can I make the SendObject code print the report in PDF format, and attach
THAT? (Any code critiqing would also be welcome)

Existing code:

Private Sub cmdSend_Click()
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & Me!ID
strEmail = Me!ParentEmail
strSubject = Me!FirstName & "'s Registration Confirmation"

DoCmd.OpenReport strDocName, acPreview, , strWhere
DoCmd.SendObject acSendReport, strDocName, acFormatSNP, strEmail, , ,
strSubject, , True
 
As far as I know, the only way to do what you described is with Adobe Acrobat; however, you might consider making this a two step operation to accomplish the same thing.
1.. Print the report to a specific printer (your PDF driver).
2.. Save the PDF document to a specific directory.
3.. Use code to fill a listbox with the PDF files located in your specified directory (see below).
4.. Open a form which allows you to fill in email addresses and other email particulars.
5.. The form can send the email for you. See code below:

Code to fill listbox:

Private Sub GetFileList()
On Error GoTo ErrorHandler
' Dimension variables.
Dim myarray()
Dim fs As Object
Dim i As Integer

' Declare filesearch object.
Set fs = Application.FileSearch

' Set folder to search.
fs.LookIn = "P:\quotes\"

' Set file name to search for. This example assumes that you
' want to search for .txt files.
fs.FileName = "*.pdf"

' Execute the file search, and check to see if the file(s) are
' present.
If fs.Execute > 0 Then

' Redimension the array to the number of files found.
ReDim myarray(fs.FoundFiles.Count)

' Loop through all found file names and fill the array.
For i = 1 To fs.FoundFiles.Count
myarray(i) = fs.FoundFiles(i)
Next i
Else
' Display message if no files were found.
MsgBox "No files were found"
End If

' Loop through the array and fill the list box on the UserForm.
If fs.FoundFiles.Count > 1 Then
Me.List1.RowSource = myarray(1)
For i = 2 To fs.FoundFiles.Count
'Me.List1.RowSource = Me.List1.RowSource & ";" & myarray(i)
Me.List1.RowSource = Me.List1.RowSource & ";" & myarray(i)
Next i
Else
For i = 1 To fs.FoundFiles.Count
Me.List1.RowSource = myarray(i)
Next i
End If

' Display the UserForm.

ErrorHandler_Exit:
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Number & ": " & Err.Description
GoTo ErrorHandler_Exit

End Sub

Code to send an email with attachments from Access:

Private Sub Command0_Click()
On Error GoTo ErrorHandler

Dim msgbody As String

Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Me
Set ctl = Me.List1



Dim myApp As New Outlook.Application
Dim myItem As Outlook.MailItem

Set myItem = myApp.CreateItem(olMailItem)

msgbody = "your message here"


With myItem
.To = Nz(Me.MailTo, "")
.CC = Nz(Me.Mailcc, "")
.BCC = Nz(Me.Mailbcc, "")
.Subject = Nz(Me.MailSubject, "")
.Body = Nz(Me.MailBody, msgbody)


For Each varItm In ctl.ItemsSelected
.Attachments.Add ctl.ItemData(varItm)
Next varItm

.Display

End With

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & " Description: " & Err.Description
GoTo ErrorHandlerExit



End Sub

Linda




AJ said:
I have just written a VBA script (below) that successfully prints a single
record to pre-defined report, then uses SendObject to load it onto an E-mail
(and inserts the E-mail address, etc ). Not bad for a newbie.

But the report has some formatting that makes it difficult to use HTML, RTF,
etc. so I am currently using the Snapshot format, for the report. But what I
see is that not everyone can read that, who I might send it to. I think you
need MS Access loaded to read that format.

What about PDF?

I have Acrobat 4.0 and also the open source PDF Creator on my machine. How
can I make the SendObject code print the report in PDF format, and attach
THAT? (Any code critiqing would also be welcome)

Existing code:

Private Sub cmdSend_Click()
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & Me!ID
strEmail = Me!ParentEmail
strSubject = Me!FirstName & "'s Registration Confirmation"

DoCmd.OpenReport strDocName, acPreview, , strWhere
DoCmd.SendObject acSendReport, strDocName, acFormatSNP, strEmail, , ,
strSubject, , True
Click to expand...
 
We sell a module to create pdf files. We also have a little example that
creates some pdf files
and then creates emails with appropriate attachments. For emailing I used a
third party email component
called ASPMail in the example.

Basically a new module in your MDB and a third party DLL and you have a
solution.

Fell free to send me an email at the support email address from our site for
help,
Mark Andrews
RPT Software
http://www.rptsoftware.com
 
Fantastic! I will give that a try.

As far as I know, the only way to do what you described is with Adobe Acrobat; however, you might consider making this a two step operation to accomplish the same thing.
1.. Print the report to a specific printer (your PDF driver).
2.. Save the PDF document to a specific directory.
3.. Use code to fill a listbox with the PDF files located in your specified directory (see below).
4.. Open a form which allows you to fill in email addresses and other email particulars.
5.. The form can send the email for you. See code below:

Code to fill listbox:

Private Sub GetFileList()
On Error GoTo ErrorHandler
' Dimension variables.
Dim myarray()
Dim fs As Object
Dim i As Integer

' Declare filesearch object.
Set fs = Application.FileSearch

' Set folder to search.
fs.LookIn = "P:\quotes\"

' Set file name to search for. This example assumes that you
' want to search for .txt files.
fs.FileName = "*.pdf"

' Execute the file search, and check to see if the file(s) are
' present.
If fs.Execute > 0 Then

' Redimension the array to the number of files found.
ReDim myarray(fs.FoundFiles.Count)

' Loop through all found file names and fill the array.
For i = 1 To fs.FoundFiles.Count
myarray(i) = fs.FoundFiles(i)
Next i
Else
' Display message if no files were found.
MsgBox "No files were found"
End If

' Loop through the array and fill the list box on the UserForm.
If fs.FoundFiles.Count > 1 Then
Me.List1.RowSource = myarray(1)
For i = 2 To fs.FoundFiles.Count
'Me.List1.RowSource = Me.List1.RowSource & ";" & myarray(i)
Me.List1.RowSource = Me.List1.RowSource & ";" & myarray(i)
Next i
Else
For i = 1 To fs.FoundFiles.Count
Me.List1.RowSource = myarray(i)
Next i
End If

' Display the UserForm.

ErrorHandler_Exit:
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Number & ": " & Err.Description
GoTo ErrorHandler_Exit

End Sub

Code to send an email with attachments from Access:

Private Sub Command0_Click()
On Error GoTo ErrorHandler

Dim msgbody As String

Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Me
Set ctl = Me.List1



Dim myApp As New Outlook.Application
Dim myItem As Outlook.MailItem

Set myItem = myApp.CreateItem(olMailItem)

msgbody = "your message here"


With myItem
.To = Nz(Me.MailTo, "")
.CC = Nz(Me.Mailcc, "")
.BCC = Nz(Me.Mailbcc, "")
.Subject = Nz(Me.MailSubject, "")
.Body = Nz(Me.MailBody, msgbody)


For Each varItm In ctl.ItemsSelected
.Attachments.Add ctl.ItemData(varItm)
Next varItm

.Display

End With

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & " Description: " & Err.Description
GoTo ErrorHandlerExit



End Sub

Linda




AJ said:
I have just written a VBA script (below) that successfully prints a single
record to pre-defined report, then uses SendObject to load it onto an E-mail
(and inserts the E-mail address, etc ). Not bad for a newbie.

But the report has some formatting that makes it difficult to use HTML, RTF,
etc. so I am currently using the Snapshot format, for the report. But what I
see is that not everyone can read that, who I might send it to. I think you
need MS Access loaded to read that format.

What about PDF?

I have Acrobat 4.0 and also the open source PDF Creator on my machine. How
can I make the SendObject code print the report in PDF format, and attach
THAT? (Any code critiqing would also be welcome)

Existing code:

Private Sub cmdSend_Click()
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & Me!ID
strEmail = Me!ParentEmail
strSubject = Me!FirstName & "'s Registration Confirmation"

DoCmd.OpenReport strDocName, acPreview, , strWhere
DoCmd.SendObject acSendReport, strDocName, acFormatSNP, strEmail, , ,
strSubject, , True
Click to expand...
 
Back
Top