How send a report and also pdf files located in c: drive?

[ldiaz]

I have a main form Named:frm_Broker and a Subform named: sfrm_Control

Main form datas like:

ID_Broker_ID Control_Num Rec_Date From_Supplier
1 CTRL-001 9/26 Tokio Inc.
and subform is linked to the main form, the subform shows datas like this:

ID_Control Part Number Attachment Qty TN#
1 XXXAAA c:\myfiles\invoice926.pdf 1
1254254
1 XXXAAB c:\myfiles\invoice927.pdf 1
1254254
1 XXXAAC c:\myfiles\invoice928.pdf 1
1254254

I have a query named:qry_broker where I can filter these datas based in the
Control_Num field of the main form.
Like Nz([Forms]![frm_Broker]![Control_Num],"*")


all these datas are shown in a report named: rpt_Broker

I have this code to send the report as attachment:
Private Sub EmailReport_Click()
On Error GoTo Err_EmailReport_Click
Dim stDocName As String
stDocName = "rpt_Broker"
DoCmd.SendObject acReport, stDocName

Exit_EmailReport_Click:
Exit Sub
Err_EmailReport_Click:
MsgBox Err.Description
Resume Exit_EmailReport_Click

End Sub

But I would like also attach pdf files that are shown on the subform;
c:\myfiles\invoice926.pdf
c:\myfiles\invoice927.pdf
c:\myfiles\invoice928.pdf

how can I do that?

Thanks in advanced.

 

[Tom Wickerath]

Hi Lorenzo,

The SendObject method will only allow you to send a single object at a time.
You're going to have to use different code in your EmailReport_Click event
procedure. I show an example of using Outlook (not Outlook Express) to send
multiple attachments in a single e-mail message, in a document that I call
"Access Links.doc". The code is shown on page 19, "Late Bound code for
Outlook to send several file attachments". You are welcome to download a
zipped copy from my web site, here:

http://www.accessmvp.com/TWickerath/


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[ldiaz]

where this code needs to go?

after Private Sub EmailReport_Click() ???


or do I need to create a module for this?
if so how can I call it?

Thanks
LD

--
Lorenzo Díaz
Cad Technician

Hi Lorenzo,

The SendObject method will only allow you to send a single object at a time.
You're going to have to use different code in your EmailReport_Click event
procedure. I show an example of using Outlook (not Outlook Express) to send
multiple attachments in a single e-mail message, in a document that I call
"Access Links.doc". The code is shown on page 19, "Late Bound code for
Outlook to send several file attachments". You are welcome to download a
zipped copy from my web site, here:

http://www.accessmvp.com/TWickerath/


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a main form Named:frm_Broker and a Subform named: sfrm_Control

Main form datas like:

ID_Broker_ID Control_Num Rec_Date From_Supplier
1 CTRL-001 9/26 Tokio Inc.
and subform is linked to the main form, the subform shows datas like this:

ID_Control Part Number Attachment Qty TN#
1 XXXAAA c:\myfiles\invoice926.pdf 1
1254254
1 XXXAAB c:\myfiles\invoice927.pdf 1
1254254
1 XXXAAC c:\myfiles\invoice928.pdf 1
1254254

I have a query named:qry_broker where I can filter these datas based in the
Control_Num field of the main form.
Like Nz([Forms]![frm_Broker]![Control_Num],"*")


all these datas are shown in a report named: rpt_Broker

I have this code to send the report as attachment:
Private Sub EmailReport_Click()
On Error GoTo Err_EmailReport_Click
Dim stDocName As String
stDocName = "rpt_Broker"
DoCmd.SendObject acReport, stDocName

Exit_EmailReport_Click:
Exit Sub
Err_EmailReport_Click:
MsgBox Err.Description
Resume Exit_EmailReport_Click

End Sub

But I would like also attach pdf files that are shown on the subform;
c:\myfiles\invoice926.pdf
c:\myfiles\invoice927.pdf
c:\myfiles\invoice928.pdf

how can I do that?

Thanks in advanced.

Lorenzo Díaz
Cad Technician

 

[Tom Wickerath]

Hi Lorenzo,

You don't absolutely need to create a module for this code, but you might
want to consider doing so, in order to be able to call the function from any
open form.

where this code needs to go?

after Private Sub EmailReport_Click() ???

Here is a general outline that I think will work for you:

1.) In Private Sub EmailReport_Click, use VBA code to create a .pdf file
using the report "rpt_Broker". Save this report to a temporary folder
(perhaps CurrentProject.Path & "\Temp", or the users normal temp. folder.

Dim strTempFolder as String
strTempFolder = CurrentProject.Path & "\Temp"

Note: You may want to include error-handling to trap for error 76 (I think
that's the right error number) if the intended folder is not present. In that
case, use VBA code to create the folder as a part of your error handler.

Access MVP Stephen Lebans has a method for saving the report to .pdf that
should work for you:

http://www.lebans.com/reporttopdf.htm

2.) Copy the appropriate .pdf files indicated in the subform from C:\myfiles
to this temporary folder, strTempFolder. You should be able to use VBA code
to accomplish this. (My assumption is that there are other .pdf files in
C:\myfiles, which are for other customers).

3.) After steps 1 and 2, above, are complete, call my function:

Call SendMail("Recipients", "Subject", _
"BodyText", strTempFolder, "*.pdf") As String

4.) Finally, add the proper error-handler to Private Sub EmailReport_Click,
finishing it off with an End Sub statement.

Save your code periodically, and use Debug | Compile ProjectName (where
ProjectName is the name of your VBA project), to work out any compile errors
as you are writing your code. Also, make sure that all code modules include
Option Explicit as the second line of code:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[ldiaz]

Hi Tom,
I think there is a problem on below code.

let me explain what I want.

on my main form is captured the Control Number (CTRL-xxx) like this:
ID_Broker_ID Control_Num Rec_Date From_Supplier
1 CTRL-001 9/26 Tokio Inc.

Note: A unique line

and my subform is related to the main form, but on the subform I have a
field named: Attachment_Document where it store the link of a pdf file
already stored in my pc (my files), these pdfs are a scan of copies of PO,
Tracking Number, Certificate of orgin and Invoice of the products received.
like this...

c:\myfiles\invoice926.pdf
c:\myfiles\invoice927.pdf
c:\myfiles\invoice928.pdf


but on "Myfiles" folder there are more pdf files related to another receipt.

What I want at the time to press the Button: Private Sub EmailReport_Click()
is that the Email needs to pick up only these pdf files that are shown on
the subform,

Could you check my DB, I can send it to you..

my email is: (e-mail address removed)


Thanks
LD

 

[Tom Wickerath]

Hi Lorenzo,

What I want at the time to press the Button: Private Sub EmailReport_Click()
is that the Email needs to pick up only these pdf files that are shown on
the subform

Yes, I understood that. This is why I had written this statement in my
earlier reply:

"2.) Copy the appropriate .pdf files indicated in the subform from C:\myfiles
to this temporary folder, strTempFolder. You should be able to use VBA code
to accomplish this. (My assumption is that there are other .pdf files in
C:\myfiles, which are for other customers)."

Could you check my DB, I can send it to you..

my email is: {Removed from this reply}

Okay. I will send you a message. It is never a good idea to post a valid
e-mail address to a newsgroup message. Doing so will only invite the unwanted
attention of spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________