Macro to create PDF from Excel 2003 and send as attachment in email

[Kas2401]

It would be nice if the solution were posted, regardless, as I am also learning!

[Kas2401]

Hello Ken

Started to apply your code and the 1st problem is with the following line. I get the response 'Compile error' User defined type not defined. As I am a novice I have no clue as to what is wrong. Please assist.


Dim pdfjob As PDFCreator.clsPDFCreator

[tommyt61]

Kas,

This is how that code should read

Dim pdfjob As Object
Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

[Kas2401]

OK Done that. Noe failes on

Set pdfjob = New PDFCreator.clsPDFCreator

which appears several times?

[Ken Puls]

http://www.excelguru.ca/content.php?...pe-Not-Defined

[tommyt61]

Here is an example for you to study.

[tommyt61]

here is .xls file for 2003

[Ken Puls]

...I get the response 'Compile error' User defined type not defined...

You haven't set the reference to the PDFCreator library for your code. Unless you change the whole thing to late bound (which is going to be above you right now), it's not going to work. Check the article I linked you to above. That will show you how to set the reference.

[Kas2401]

Hi Ken

Thank you for the input. Yes, things are definitely 'above me right now', as when I first read this I started to look at previous posts(literally above) and then twigged to what you were saying. I read the article and did what was required as will now proceed with testing. As the list of 'set references' is quite extensive and they are not automatically set I can only assume that setting them all would have a devastating impact. Could you lead me through this so I can better understand what I am doing and why?

[Ken Puls]

Hi,

I don't think setting all references would bother you too much, to be honest, but if you sent your workbook to someone else it could screw them up right royally.

So... computers talk, in their barest form, in binary. Being that we can't really understand that, we built languages to convert all the 1's and 0'2 into something human readable. Using those languages, people build programs. And in many programs people build libraries that other coders can use to talk to their programs. Typically these libraries get rolled into a DLL file (Dynamic Link Library), and when you install the program on your computer, the libraries are registered for use.

The references list that you see in the VBE is a listing of all the DLL files that are registered on your machine. And yes, there are LOTS of them.

Each DLL has it's own code pieces in it. Classes, Objects, Properties, Methods, Functions, etc... The complexity depends, of course, upon how complex the original program was and how much the developer decided to expose in the DLL file. The nice thing for us is that we can set a reference to these files and use the object, properties and methods contained within.

Now, there are actually two ways to use these libraries... you can connect to them and have full access to the object model that goes with them, or you can declare all of your objects as "Object" and manifest them dynamically at runtime. The difference is what we call Early Binding (setting a reference) vs Late Binding (manifest at runtime). You can read a bit more about the difference here. The issue you were facing is that the code posted on my site is Early Bound code, and you did not set a reference. Without that reference, Excel doesn't have a clue what you're talking about.

Now, just because you set a reference doesn't mean that you have to use anything from the library. Hence my comment that you could set all the references. BUT... if you do set a reference to a library that I don't have, then email me your workbook, I get an error. Usually it manifests in a valid VBA function throwing an error. After finding that, I would need to go into the references list and remove any "MISSING:" references in order to let the code run.

At any rate, I'd avoid setting references unless you're told you need to. But if you ever see the "User Defined Type Not Defined" error, the first thing I'd check is if the code is Early Bound and a reference hasn't been set.

Does that help?