email pdf document

[PHisaw]

Hi,

Some time back, I entered a question asking if it was possible to be in
Access, click on a command button, store a created pdf document in a "temp"
file so that I might also be able to include, with code, the Access documents
that I need to send with this file. I had to pull away from this project,
but now am ready to tackle it again. Here is the code that was sent:

Dim strFile(10) ‘assuming you have 11 or less files in the directory that
you are wanting to attach.
Dim tmp as string, i as integer, intFileCount as integer, strPath as string
Dim strEmail as string, strCC as string, strBCC as string, strSubject as
string, strMsg as string

Sub EmailPDF()

strPath = "C:\PathToPDF's\â€

tmp = Dir(strPath & “*.pdfâ€)
While tmp <> ""
strFile(i) = tmp
i=i + 1
intFileCount = i
tmp = Dir()
Wend

Dim outApp As Outlook.Application, outMsg As MailItem
Set outApp = CreateObject("Outlook.Application")
Set outMsg = outApp.CreateItem(olMailItem)

strEmail = "(e-mail address removed)"
strCC = "(e-mail address removed)"
strBCC = "(e-mail address removed)"
strSubject = "My PDF"
strMsg = "Here's your PDF"

With outMsg
.Importance = olImportanceHigh
.To = strEmail
.CC = strCC
.BCC = strBCC
.Subject = strSubject
.Body = strMsg
For i = 1 to intFileCount -1
.Attachments.Add strPath & strFile(i)
Next i
.Display
.Send
End With

Set outApp = Nothing
Set outMsg = Nothing

End Sub


When I copied this code and replaced it with my file path, I received a
message "Compile Error - Invalid or unqualified reference" and the .pdf is
highlighted in the "tmp=" line of code.

Hopefully, I've made what I'm trying to do clear and if anyone can help with
this, it would be greatly appreciated.
Thanks in advance,
Pam

 

[Douglas J. Steele]

I don't see anything in that code that should be causing that problem.

Which "tmp=" line is it complaining about? The first one, where you pass it
the path and wildcard. or the one inside the While loop? If the one inside
the While loop, is it complaining the first time through the loop, or on a
later iteration?

What exactly did you change strPath to?

 

[PHisaw]

Doug,
Thanks for the quick reply. Here is the line of code that the .pdf is
highlighted in:

tmp = Dir(strPath & “ * .pdfâ€)

I changed the strPath to where the file was located on the server. Is this
not right? The info I received with this was that I would have to change the
path.

Pam

 

[Douglas J. Steele]

It might make a difference what you specified, which is why I wanted the
exact value.

I just tested using both UNC and mapped drive, and it worked fine for me.
(I'm assuming you don't have the spaces around the asterisk)

 

[PHisaw]

Sorry - here's the code as I have it now.

Private Sub Command235_Click()
Dim strFile(10)
Dim tmp As String, i As Integer, intFileCount As Integer, strPath As String
Dim strEmail As String, strCC As String, strBCC As String, strSubject As
String, strMsg As String



strPath = "\\dell4lysk21\data\D T S\SCMP DECONTAMINATION
GUIDELINESâ€"

tmp = Dir(strPath & “ * .pdfâ€)
While tmp <> ""
strFile(i) = tmp
i = i + 1
intFileCount = i
tmp = Dir()
Wend

Dim outApp As Outlook.Application, outMsg As MailItem
Set outApp = CreateObject("Outlook.Application")
Set outMsg = outApp.CreateItem(olMailItem)

strEmail = "(e-mail address removed)"
strCC = "(e-mail address removed)"
strBCC = "(e-mail address removed)"
strSubject = "My PDF"
strMsg = "Here's your PDF"

With outMsg
.Importance = olImportanceHigh
.to = strEmail
.CC = strCC
.BCC = strBCC
.Subject = strSubject
.Body = strMsg
For i = 1 To intFileCount - 1
.Attachments.Add strPath & strFile(i)
Next i
.Display
.Send
End With

Set outApp = Nothing
Set outMsg = Nothing

End Sub

 

[Douglas J. Steele]

You appear to have a "smart quote" (") rather than a slash at the end of
strPath.

I also think you need to remove at least the blank before the * in

tmp = Dir(strPath & " * .pdf")

(I don't think it's possible for a file name to start with a space, which is
what that would retrieve)

 

[Steve Schapel]

Pam,

As well as removing the spaces, you might also need to retype this line,
as it appears to be enclosed in “” rather than "" (which probably
indicates it was originally copy/pasted from a word processor or
newsreader which munged it).

 

[PHisaw]

Doug/Steve,
Thanks again for the help. I've got past this line of code, but it's
stopping at the last line of text shown below. I've only entered the first
part up to where it's causing an error. I also wanted you to verify the
changes were correct I made.
On the last line, I get message "User-defined type not defined".

Thanks,
Pam

 

[PHisaw]

Sorry I forgot to paste the code in

strPath = "\\dell4lysk21\data\D T S\SCMP DECONTAMINATION GUIDELINES\"

tmp = Dir(strPath & "* .pdf")
While tmp <> ""
strFile(i) = tmp
i = i + 1
intFileCount = i
tmp = Dir()
Wend

Dim outApp As Outlook.Application, outMsg As MailItem

 

[Douglas J. Steele]

Have you set a reference to Outlook (through Tools | References)?

 

[PHisaw]

No, can you tell me how to set the reference? I'll have to wait until Monday
to do anything further with this.

Thanks again for the time and help!
Pam

 

[Douglas J. Steele]

With any code module open, select Tools | References from the menu.

Scroll through the list of available references until you come to the
appropriate Microsoft Outlook n.0 Object Library entry (n will depend on
what version of Outlook you have).

Check that reference, then back out of the dialog.

Note, though, that if you're distributing this application to other users
and they don't have the exact same version of Outlook, you're going to run
into problems.

 

[PHisaw]

This sounds like it getting even more complicated - by "distributing this
application" are you saying that if I send a pdf with this procedure, it can
cause problems?

 

[Douglas J. Steele]

No, I'm saying you can run into problems if you distribute the MDB to other
people so they can use it to send PDF files.

 

[PHisaw]

If I set the reference (by this do you mean checking the box "Microsoft 11.0
Object Library"?) and then everyone copies the front end to their computer,
will this still cause problems?

 

[Douglas J. Steele]

If they don't have Outlook 2002 on their machine, it will definitely cause a
problem.

If they have a different version of Outlook 2002 than you (i.e.: different
service pack), it may cause a problem.

It's for this reason that many developers choose to use Late Binding, rather
than setting references. Tony Toews has an introduction to the topic at
http://www.granite.ab.ca/access/latebinding.htm

 

[PHisaw]

I really would like to get this to work as I hope it will. I certainly do
appreciate your time and help with this and ask that you please bear with me.
I feel this is way over my head in what little I do know about Access.

This is what I have done - I took out everything under the word Wend and
replaced it with the code below. I ran it and it still stops at
SetolApp=NewOutlook.Application with error msg "Compile Error: User defined
type not defined". Do I need to go to references and check the box
"Microsoft 11.0 Object Library"?

Dim olApp As Outlook.Application
Dim olMailMessage As Outlook.MailItem
Dim olRecipient As Outlook.Recipient
Dim blnKnownRecipient As Boolean

' Create new instance of Outlook or open current instance.
Set olApp = New Outlook.Application
' Create new message.
Set olMailMessage = olApp.CreateItem(olMailItem)
' Prompt for message recipient, attempt to resolve address, and
' then send or display.
With olMailMessage
Set olRecipient = .Recipients.Add(InputBox("Enter name of message
recipient", _
"Recipient Name"))
blnKnownRecipient = olRecipient.Resolve
.Subject = "Testing mail by Automation"
.Body = "This message was created by VBA code running " _
& "Outlook through Automation."
If blnKnownRecipient = True Then
.Send
Else
.Display
End If
End With
Set olMailMessage = Nothing
olApp.Quit
Set olApp = Nothing

End Sub

 

[Douglas J. Steele]

It's not "Microsoft 11.0 Object Library", it's "Microsoft Outlook 11.0
Object Library"

However, to convert your code to Late Binding, you need to make the
following changes to your code:

1)

Dim olApp As Outlook.Application
Dim olMailMessage As Outlook.MailItem
Dim olRecipient As Outlook.Recipient

needs to be

Dim olApp As Object
Dim olMailMessage As Object
Dim olRecipient As Object

2)

Set olApp = New Outlook.Application

needs to be

Set olApp = CreateObject("Outlook.Application")

3)

Set olMailMessage = olApp.CreateItem(olMailItem)

needs to be


Set olMailMessage = olApp.CreateItem(0)

I think that's it.

 

[PHisaw]

Oops! Sorry for the "Outlook" omission. I made the changes and it asked for
the recepient name and a warning box asking if this is what I wanted. I
selected yes and I don't think it agreed with my computer. The open windows
weren't complete screens and it was flickering. This is proving to be a lot
more difficult than I thought it would be.

I originally had documents created in Access and would use CutePdf to
output them to a temp file as a pdf document. Due to necessary changes, one
of the documents had to be a pdf document created outside Access. I was
hoping that I could click a command button send those that are in Access and
"grab" the one outside Access, bring to a temp file, then go to the temp file
and email all. I don't think this is working the way I had hoped. I think
I'll leave it as is.
Thanks again for your time and help. Very much appreciated.
Pam

 

[Douglas J. Steele]

Sorry, we don't use Outlook here, so I can't verify that the code should
work.