Hi all,

I would like some guidance on which way you would tackle this mission I have.

The goal is, from a JOB screen in Access 2003, click a button which:
-1- gets a filename by way of concatenating fields etc.
-2- using the calculated filename, which is always an Excel (XLS) file, convert all the pages in that workbook to PDF files.
-3- attach all those PDFs to an e-mail in outlook.
-4- show the email in outlook and leave it there for the user to complete and send when they are ready.

That should give you the goal my customer is after.

I'm only look for a solution to -2- at this point. 3 and 4 I'll tackle once I have 2 under control

Any advice, links or anything would be greatly appreciated!

Cheers,

ST

Oh, I forgot to mention, if there is completely different way of tackling this to achieve the same result, please suggest away

ST,

From your post I assume you already know how to gen the Excel, so all you need in a PDF gen tool like PrimoPDF or CutePDF.

Add code to your VBA, after saving and closing the .xls file, to print using the PDF printer, these create.

Should be rather easy, just look for code examples on how to call different printers in the printing process.

DBS4M

How you getting on with this ST - I can do this for you if you get stuck...?

Excellent, thanks for the suggestion DBS4M, tbh, I hadn't even considered a PDF printer and then just printing it...

Come to think of it, would that work? Wouldn't it produce a print PDF dialog to be filled in? Can that be automated? Also, I should point out that the Excel file is not generated by me, but already exists.

I also have VERY little experience in automating with either Excel or Outlook from Access -- it's just never come up before for me. I know it has for countless others though, but I have been either lucky or unlucky... depending on your point of view... to not have had to wrestle with it before.

And Gareth, I haven't gotten anywhere yet, just planning at this point. Is going the way of a PDF printer what you would suggest as well?

Hey ST,

I used Stephan Leban's code a few years ago on a project to get my report to pdf. I can't remember the details enough to remember if it's easily applicable to Excel or not sorry. I'll see if I can remember.

As for working with Excel from Access, I learned quite a bit by dabbling with Poot's post in the Code Bank.

HTH

Did some Internetting and found this from Granite's site.

Yes, I also have a form of the code to make PDFs from Reports. I was thinking along the lines of that kind of code to do the same with Excel, but I think that has to be done from Excel, not from Access. Not sure of course.

Thanks for the link to the codebank post, I'll use that when the time comes

Edit: Re: post #7, thanks for those links too Nick! That first one might just be able to be converted to run in Access. I'll check it out in detail and let you know

Great Links (Specially Nick).

Did a mod of this ST - Works as far as automating Excel from Accesss and saving individual sheets to PDFs.

Any particulars ref Outlook - Just attach the individual .PDFs?

Subject: ??
Body message: ??

Do you need to keep the PDFs after they have been eMailed or can they just be killed after eMailing?

Done a few trials and I think the original code might need a bit of expansion.

Not sure how consistent your Excel spreadsheets are in terms of used rows / columns?

Anyhow - answer my Outlook queries when you have five and I'll drop you what I've got so far.

Hey, great Gareth!! I really appreciate that, it will save me quite a bit of time... which is something I don't have much of atm!

To answer your questions:

Just attach the PDFs.

The subject and body message can be set to either nothing or something like "Quote", since the user will want to edit them prior to sending.

The PDFs can be killed.

Okey dokey - hang on in there...

HI ST,

Here is a mod of Ken Puls Excel code which runs as a MS Access function;

Give this a try and see what issues you come up with - I've found that PDF Driver a little temperemental but this might just be the machine I'm on at the moment.

You should consider this a starting point only and I will help to tidy it up but I'm going to be chocca for the next few days so anyone else is more than welcome to chip in if they wish!

It could do with some error trapping and a bit of polishing up but hope it gets you started.

Anyhow - give this a try and let me know whether it's what you were after.

Option Compare Database
Option Explicit

Public Function PrintToPDF_MultiSheet_Early(sPDFPath As String, sPDFName As String, strExcelPath As String, strRecipient As String, Optional strSubject As String = "Quotation", Optional strBody As String = "Dear Sirs,")

'Author : Ken Puls (Excelguru.ca | Tips and pointers for Excel and other MS Office applications)
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from SourceForge.net: PDFCreator)
' Designed for early bind, set reference to PDFCreator

'12th May 2009 - Modifed as access function by Gareth Dart
'Creates an Outlook messsage and adds individual .PDFs of each worksheet
'Changed sPDFPath and sPDFName to function variables
'Also added strExcelPath, strRecipient
'Also strSubject and strBody as optional variables
'Dont forget to add references for Excel, pdfcreator and Outlook

'call PrintToPDF_MultiSheet_Early("c:\", "TESTpdf", "c:\xlsTest.xls", "someperson@domain.com", "Revised Quotation", "Dear Sirs,")

Dim pdfjob As PDFCreator.clsPDFCreator
Dim lSheet As Long

Dim strOriginalName As String
strOriginalName = sPDFName

'Variables for excel
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open(strExcelPath)

Dim olApp As Outlook.Application
Dim olMessage As Outlook.MailItem
Dim fsoTemp As FileSystemObject

Set olApp = New Outlook.Application
Set olMessage = olApp.CreateItem(olMailItem)

olMessage.Recipients.Add strRecipient
olMessage.Subject = strSubject
olMessage.Body = strBody

Set pdfjob = New PDFCreator.clsPDFCreator
sPDFPath = xlWb.Path & xlApp.PathSeparator

If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "PrtPDFCreator"
Exit Function
End If

For lSheet = 1 To xlWb.Sheets.Count
'Check if worksheet is empty and skip if so
If Not IsEmpty(xlWb.ActiveSheet.UsedRange) Then
With pdfjob
'/// Change the output file name here! ///
sPDFName = strOriginalName & xlWb.Sheets(lSheet).Name & ".pdf"
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

'Print the document to PDF
'Syntax changed
xlWb.Sheets(lSheet).PrintOut , , 1, 0, "PDFCreator"

'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False

'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop

End If
'Add the latest attachment to the Outlook message
olMessage.Attachments.Add (sPDFPath & sPDFName)
'Delete the temporary PDF
Kill (sPDFPath & sPDFName)

Next lSheet

pdfjob.cClose
Set pdfjob = Nothing

'Close workbook without saving changes, exit Excel then clear variables.
xlWb.Close False
xlApp.Quit
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

'Display the eMail and allow user to edit then send
olMessage.Display

'Clear variables
Set olApp = Nothing
Set olMessage = Nothing

End Function

Hey Gareth, I haven't had a chance to test it yet, but it looks like this would be an excellent addition to the Code Bank!

Hi Nick,

When finished yes but I think there are a few things that need addressing;

Whilst testing, the format wasn't perfect when I tried with various excel layouts. However once ST is happy, we can modify to a more general set of functions.