UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Mail merge to PDF?, Office 2007    
 
   
LumberJack
post Oct 8 2010, 02:30 AM
Post #1

UtterAccess Member
Posts: 46



Hi I have a form which shows all the charges due for a contact. I have been given a template (as a word document) for invoices which have an extra sheet of terms and conditions after the main page which should contain all the relevant data to the charge as well as some formating such as a graphic with the organisation letterhead. I know I can mail merge the data to the word document, however the desired end product is that from the form the user clicks a button to output the selected charge as an invoice in pdf, the pdf is attached to an email, and a copy of it is saved for record keeping purposes. I've had success in the past emailing a report as a pdf using the docmd.sendobject method but in this case I don't think i'll be able to make the invoice an access report. Is there a way to mail merge to a pdf? Have searched a whole bunch of other threads but none seem to relate to exactly what I want.

LumberJack
Go to the top of the page
 
+
pere_de_chipstic...
post Oct 8 2010, 09:35 AM
Post #2

UtterAccess VIP
Posts: 8,584
From: South coast, England



Hi LumberJack
This is the code I use (extracted from a longer function) :

CODE
    Dim wdApp As Object     'Word.Application - Late Binding
    Dim wdDoc As Object     'Word.Document - Late Binding
    strSrceDoc  = "Your Word Doc Name"  '(path and file name)

    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Open(strSrceDoc)

    'Save the document to the pdf file name
    'FileFormat = 17 = PDF Format
    wdApp.activedocument.SaveAs FileName:=strSrcePDF, FileFormat:=17, _
            LockComments:=False, Password:="", AddToRecentFiles:=True, _
            WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
            SaveNativePictureFormat:=False, SaveFormsData:=False, _
            SaveAsAOCELetter:=False
        
    wdDoc.Close 0
    Set wdDoc = Nothing
    wdApp.Quit


hth

Edit: Changed to show code in [ code ] tags rather than [ codebox ] tags as last lines didn't show (IMG:style_emoticons/default/shrug.gif)

This post has been edited by pere_de_chipstick: Oct 8 2010, 12:18 PM
Go to the top of the page
 
+
AlbertKallal
post Oct 9 2010, 05:40 AM
Post #3

UtterAccess VIP
Posts: 1,941
From: Edmonton, Alberta Canada



I would consider building a report, and then the terms and conditions would be an additional second report that you insert as a sub report into the one main report.

The reason for the above it's a lot less code than merging into word.

I think word gives you better formatting capabilities for lots of text, but for the data part, you're probably still better off to use the access report writer.

The other advantage to using the access report writer is of course you can you use send object which converts the report into a PDF, and launches a standard default e-mail client that you have installed on your computer. This one simple command represents a fair bit of coding on your part if you stray away from it.

You could in theory merge to word, then use the word save as PDF option, then launch a copy of outlook by a automation, and then attach that PDF document you saved.

However you're going from one simple command of send object as to now using word automation, saving the document in some folder as PDF , and then launching a copy of outlook, then automating outlook, and then attaching the PDF document you saved. This is all certainly possible, but it does mean you'll have to assume the target machine has both word and outlook.

The beauty of the new access send object command s it works with Outlook express, live mail, Outlook, or just about any standard e-mail client installed on your computer.

And, sendobject works with far less hassle than just about any other approach I can think of.

So I think word and the NEW ability of word to save as PDF is an possbile. You would then have to use outlook automation. This is all possible, however send object with a single access report is so much less work.

Less work and less automation (and less interaction with several layers of different software packages ) as a general rule also increase the reliability of your solution.

To my knowledge outlook express, or something like office live mail can not be automated in code to attach any document of your choosing (and you would need this ability if you use word).

If you use outlook, then yes you can attach any document off the hard drive, as in this case you would need this ability to attach the saved PDF document you create by automating word. So, going down this road quite much means you assume Outlook. Nothing wrong with this apporach, but I not sure you gain the benefits over using sendobject as you have now.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
+
LumberJack
post Oct 11 2010, 05:20 AM
Post #4

UtterAccess Member
Posts: 46



Thankyou both for your advice. I was leaning towards pere_de_chipsticks approach and thanks a lot for the code. It is extremely handy to know that such a thing can be done. But Albert's given a pretty succint description of why it may be better to just use an access report so I'm now thinking I'll just try and make it look as much like the word document as possible.
QUOTE
Less work and less automation (and less interaction with several layers of different software packages ) as a general rule also increase the reliability of your solution.

Yes i think this may be another good reason to stick to sendobject. Used the reliability line to my boss to justify making it in access and just making it look as much as possible like the word report. Have done a little bit on it so far and will try and implement it fully and report back. The idea of the terms and conditions as a subreport sounds like the way to go.

QUOTE
I think word gives you better formatting capabilities for lots of text, but for the data part, you're probably still better off to use the access report writer.


When i tried copy and past direct from word to access it turned some selected text into an OLE unbound object. This seems to keep it all in the original formatting. I think this may be the way to go. You've brought round to another thought Albert is that if its in an access report it will always be there under the charges section so I don't have to worry about the user trying to find it in a mail merge or saved documents file. I think then to keep track of which charges have been invoiced I will need to add a Invoiced yes/no field to charges. This is probably a better solution than storing the invoice themselves, and they can always be re called up from access this way.

Thanks for your help, will post back if running into difficulties.
LumberJack
Go to the top of the page
 
+
pere_de_chipstic...
post Oct 11 2010, 06:16 AM
Post #5

UtterAccess VIP
Posts: 8,584
From: South coast, England



Hi Lumberjack

Whilst I do not disagree with Albert's suggestions about keeping it all under the same roof, I believe there are some other considerations you should take into account before you make your final decision.

Firstly once the report is wholly an access database report the report format is essentially fixed - yes the data can be changed, and you can use code to change the report formatting to an extent - but once you start doing this then the report design becomes more complex then creating a mail merge to Word function. Secondly, should your organisation decide to alter the report format then the design change has to be within Access and even 'simple' changes cannot be implemented by a user.

Using Mail Merge allows your users to create whatever format report they desire independently of you the Access developer, (provided they keep to the fields available to them from the mail merge data source file). Word is far more flexible in terms of formatting than Access reports,

A third issue is that if you are using graphical images then the built in A2007 PDF converter / addin is pretty awfull and result in very poor aliasing - if your PDF documents have graphics (e.g. your company logo) and are sent to clients then it is unlikely that the PDF conversion will be of good enough quality and you will then need to change to a third party PDF converter (e.g. Lebans).

There are a number of code examples in UA for creating a Mail Merge Word documents and IMHO these are not particularly complex, the database and word format reports are more flexible and in my experience do not have reliabilty problems.

Albert states "You could in theory merge to word, then use the word save as PDF option, then launch a copy of outlook by a automation, and then attach that PDF document you saved." - This is more than 'In Theory' as I use it 'In Practice' on virtually all my database designs.

my 2c worth!
Go to the top of the page
 
+
LumberJack
post Oct 12 2010, 05:19 AM
Post #6

UtterAccess Member
Posts: 46



Hmm you've given me food for thought Bernie. I think I shall be going with Alberts approach to try and get this up and running quickly but maybe in future I will try yours. Thanks a lot though.
Go to the top of the page
 
+
pere_de_chipstic...
post Oct 12 2010, 05:28 AM
Post #7

UtterAccess VIP
Posts: 8,584
From: South coast, England



Hi LumberJack

No Problem - good luck with your project.

Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 17th April 2014 - 04:35 AM