SendObject - PDF

[RL]

Good afternoon,

We have an Access 2007 customer database. From within a customer's record, I
would like users to be able to email a chosen PDF attachment to that
customer.

I have played with SendObject and am able to open an email, with the
appropriate email address, subject and content displayed.

I have also played with FollowHyperlink to open one of various PDF documents
based on a combo box selection.

However, I am struggling to automativally attach the chosen document to the
email. SendObject only appears to let you send Access Objects.

Can anyone make any suggestions, or am I not able to do this with Access?

Regards,
RL

 

[Scott McDaniel]

Good afternoon,

We have an Access 2007 customer database. From within a customer's record, I
would like users to be able to email a chosen PDF attachment to that
customer.

2007 offers an Addin that might do what you want. check this:

http://www.microsoft.com/downloads/...3C-6D89-4F15-991B-63B07BA5F2E5&displaylang=en

According to the page:

This download allows you to export and save to the PDF format in eight 2007 Microsoft Office programs. It also allows
you to send files as e-mail attachments in the PDF format in a subset of these programs.

I have played with SendObject and am able to open an email, with the
appropriate email address, subject and content displayed.

I have also played with FollowHyperlink to open one of various PDF documents
based on a combo box selection.

However, I am struggling to automativally attach the chosen document to the
email. SendObject only appears to let you send Access Objects.

Can anyone make any suggestions, or am I not able to do this with Access?

Regards,
RL

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Albert D. Kallal]

However, I am struggling to automativally attach the chosen document to
the
email. SendObject only appears to let you send Access Objects.

Can anyone make any suggestions, or am I not able to do this with Access?

Regards,
RL

If you looking to "attach" any "non access" report, then you need to
"automate" outlook.....

eg:

Public Sub MySendObject(strSubject As String, _
strMsgText As String, _
strEmailTo As String, _
strDocName As String)


send to user via email
Dim ol As Object Late binding 10/03/2001 -
Ak
Dim ns As Object Late bind
Dim newmessage As Object Late bind
Dim mymessage As String

Set ol = GetObject(, "Outlook.Application")
Set ns = ol.GetNamespace("MAPI")
ns.Logon
Set newmessage = ol.CreateItem(0) 0 = olMainItem
With newmessage
.Recipients.Add strEmailTo
.Subject = strSubject
.Body = strMsgText
If strDocName <> "" Then
.Attachments.Add (strDocName)
End If
.Display
.Send
End With
End Sub


You could take the above code, and expand it with stephans pdf creater. You
get:

Public Sub EmailReport(strReportName As String, _
strSubject As String, _
strMsgText As String, _
strDocName As String, _
strEmailTo As String)


sends the active report out....
send to user via email

Dim MyReport As Report
Dim ol As Object Late binding 10/03/2001 -
Ak
Dim ns As Object Late bind
Dim newmessage As Object Late bind
Dim mymessage As String

DoCmd.OutputTo acReport, strReportName, acFormatRTF, strDocName, False
Call ConvertReportToPDF(strReportName, , strDocName, False, False)

DoCmd.Close acReport, strReportName

On Error GoTo CreateOutLookApp
Set ol = GetObject(, "Outlook.Application")
On Error Resume Next
Set ns = ol.GetNamespace("MAPI")
ns.Logon

Set newmessage = ol.CreateItem(0) 0 = olMainItem
With newmessage
.Recipients.Add strEmailTo
.Subject = strSubject
.Body = strMsgText
.Attachments.Add (strDocName)
.Display
.Send
End With

Exit Sub

CreateOutLookApp:

Set ol = CreateObject("Outlook.application")
Resume Next

End Sub

 

[RL]

Thanks Albert, your first suggestion seems to be exactly what I need - to
attach non-Access files to an email.

So, I copied your Public Sub code below. Then I created the following
OnClick event to a button on my form:

Private Sub Command48_Click()
MySendObject([ActionDescription],[r_ReminderNotes],,[Document])
End Sub

....where [ActionDescription] is a Text field, [r_ReminderNotes] is a Memo
field and [Email] and [Document] are controls on the form which Dlookup text
fields from other tables based on the contact and document selected from
combo boxes.

When I click the button on the form, I get a 'Compile Error: Syntax Error'
message.

Any ideas where I'm going wrong?

 

[RL]

Afternoon again Albert. I'm still struggling with a 'Compile Error: Syntax
Error'
message when it tries to run the following OnClick event for a button on a
form.

Private Sub Command48_Click()
MySendObject([ActionDescription],[r_ReminderNotes],,[Document])
End Sub

....where [ActionDescription] is a Text field, [r_ReminderNotes] is a Memo
field and [Email] and [Document] are controls on the form which Dlookup text
fields from other tables based on the contact and document selected from
combo boxes.


Any ideas where I'm going wrong?

 

[Albert D. Kallal]

Afternoon again Albert. I'm still struggling with a 'Compile Error: Syntax
Error'
message when it tries to run the following OnClick event for a button on a
form.

You need to check if your code compiles BEFORE you try and run your code.

I assume that the code sample I gave you is to be placed in a standard code
module. (perhaps you call the model basEmailStuff).

Note that the code I posted ONLY will work if your have outlook on your
computer (outlook express or other email systems will not work with the
posted code). If you using a different email system, then try "their"
on-line support systems for code examples.

In addition to placing the posted code in a standard code module (not the
forms module), you want to make sure you have the following at the start of
each module (for any code module that has code in it - both forms modules
and standard code modules).

Option Compare Database
Option Explicit

Once you verified you have the above, then while in the code editor, go go
the menu and use debug->compile. If you get an compile error, you be moved
to where that errors is. You have to continue this "de-bugging" process
until you get NO compile errors. It ONLY at that point in time you should
attempt to run your button code (and then likely at that point fix any
additional problems in the code).

 

[RL]

Mornin Albert,

Thanks for getting back to me.

We are running Microsoft Office Outlook 2007.
The code sample you gave me is a module called EmailAttachment.
I only originally put 'Option Compare Database' at the beginning. I have now
added 'Option Explicit'.

I then used Debug->Compile, which highlighted a few things. So, I tweaked
the code to the following.
(I'm a VBA rookie, so not sure whether I have removed anything crucial - was
mostly trial and error to get rid of the red bits!)
The bits I removed were the late binding and the 0 = olMainItem in the 'Set
newmessage' line.

Option Compare Database
Option Explicit


Public Sub MySendObject(strSubject As String, _
strMsgText As String, _
strEmailTo As String, _
strDocName As String)


'send to user via email
Dim ol As Object
Dim ns As Object
Dim newmessage As Object
Dim mymessage As String

Set ol = GetObject(, "Outlook.Application")
Set ns = ol.GetNamespace("MAPI")
ns.Logon
Set newmessage = ol.CreateItem(0)
With newmessage
.Recipients.Add strEmailTo
.Subject = strSubject
.Body = strMsgText
If strDocName <> "" Then
.Attachments.Add (strDocName)
End If
.Display
.Send
End With
End Sub


Now the only bit that doesn't compile is the OnClick event itself, which
reads as follows:

Private Sub Command48_Click()
MySendObject([ActionDescription],[r_ReminderNotes],,[Document])
End Sub

The message says 'Compile error Expected: ='

I'm not sure where I am expected to put =. I have tried various places that
would seem logical, but with no success.

Any ideas? Would it have anything to do with the [r_ReminderNotes] being a
memo field (all the others are text).

Many thanks again.
RL

 

[Albert D. Kallal]

The message says 'Compile error Expected: ='

I'm not sure where I am expected to put =. I have tried various places
that
would seem logical, but with no success.

If this is code behind a button in a form, then go:

Call MySendObject (me.ActionDescription, me.r_ReminderNotes,
me.Email.Value,me.Document)

(above on one line)

The code sample you gave me is a module called EmailAttachment.

Hum I don't recall giving you a code module.

We have a subroutine called MySendObject. Don't confuse a module name with
that of sub or function we have. A module is a group of functions + subs
placed inside of that module. So we never really
did come up with a name for the code module (it really don't matter what
name you use...as long as the code module name does NOT conflict with any
name of a sub or function that you place inside of that code module....

the MySendObject code we place in that standard code module will look like:

Public Sub MySendObject(strSubject As String, _
strMsgText As String, _
strEmailTo As String, _
strDocName As String)


' send to user via email
Dim ol As Object ' Late binding 10/03/2001 -
Dim ns As Object ' Late bind
Dim newmessage As Object ' Late bind
Dim mymessage As String

Set ol = GetObject(, "Outlook.Application")
Set ns = ol.GetNamespace("MAPI")
ns.Logon
Set newmessage = ol.CreateItem(0) '0 = olMainItem
With newmessage
.Recipients.Add strEmailTo
.Subject = strSubject
.Body = strMsgText
If strDocName <> "" Then
.Attachments.Add (strDocName)
End If
.Display
.send
End With
End Sub


Note that in a few places a " ' " was added..those were just comments in the
original post.

You try the debug->compile until everything works...

I would "test" the code with something like:

Call MySendObject("test subject", "message body", "(e-mail address removed)",
"c:\altry.xls")

again, above would be on one line..but, this newsreader tends to wrap
things....