Email of Embedded-Ole PDF

[Janetzky]

Hi All,


I am loking desperateley for a solution. I store PDF files in a table
in Access 2003 database using the OLE format. Even though the files
are PDF files the field displays them as"Outlook File attachments".
Dbl Clicking makes them opening. Every file is assigned to a
responsible person. In a master table related to that table i store
the respective email-adresses of those induviduals.Weekly i need to
follow-up on the objects by attaching and emailing them to the
responsible persons. To achieve this requirement I was thinking on 2
possible solutions:
1st Solution
Filtering a Query with the Email and Objects by a form controlled by a
dropdown box. This query builds the basis for a report i want to
attach to the email to he respective person. Using the Send Report
buttons...
Problem: The report does not show the OLE-Objects. How can this be
solved or is this even workable?
2nd Solution
Using a form to chose the repsonsible person in order to filter a
querry containing the mentioned files. Creating a vb code in order to
pick email adress, subject and body text from the master table. Then
some loop shall attach the relevant attachments to the email.
Problem: I don't know how to work this...Please help.

By the way I am using Outlook 2003.

Thanks for your most apreciated input.

Sven

 

[RoLaAus]

I'm pretty certain that the "send report" option is only meant to send
out Access reports as either Spreadsheets, Snapshots (a PDF type file
format that is specific to Access and requires a free Snapshot viewer
download from Microsoft), or some other predefined format.

I think your best bet is your 2nd option, though you would have to
create a "Send mail" type function, it is completely possible to
automate. Here is some sample code taken from a procedure I have
implemented, hopefully you can use it as a model and I can help you
fill in any blanks.

one thing to note, is that you will need to go into Tools ->
References in the VBA editor and include Microsoft Outlook 11.0 Object
Library (or whichever version you have installed - hopefully the code
will work with any of them)

Dim appOutlook As Outlook.Application
Dim olMailItem As Outlook.MailItem
Dim rsRecipients as Recordset, rsFiles as recordset

Set appOutlook = CreateObject("Outlook.Application")
Set olMailItem = appOutlook.CreateItem(0)

'retrieve a list of email address that you are sending files to
set rsRecipients = currentdb.openrecordset("Select fld_PersonNameorID,
fld_EmailAddress from tbl_TableName GROUP BY fld_EmailAddress;")
rsRecipients.movefirst

With olMailItem
do until rsrecipients.eof

.To = 'Need code to fill in email address from your database
.Subject = "SUBJECT LINE"
Set rsFiles = currentdb.openrecordset("Select fld_FileNames from
tbl_Tablename where fld_responsibleparty = '" &
rsrecipients("fld_PersonNameorID") & "'"
rsFiles.movefirst
do until rsFiles.eof
.Attachments.Add Source:= rsFiles("fld_FileNames")
rsFiles.movenext
loop
.send
rsRecipients.movenext
loop
End With

Set olMailItem = Nothing
Set appOutlook = Nothing

This should give you a good starting point, but you'll obviously have
to do some filling in of the fied and table names. Let me know if
this produces any errors and I'll see what I can do ... try to CC me
directly and I'll better know if you need further assistance.

 

[bhavdahl]

I don't know if anyone is monitoring this post any more, but when I run this code I get the error that the, "object doesn't support this property or method" on the .Attachments.Add

Hopefully someone out there is. I'm storing a word document, excel, and a PDF in an OLE field. So I should have got 3 attachments here. Also I'm using Access 2003.

Any help would be greatly appreciated.

Here is the code I'm using...

	Dim Conn As ADODB.Connection
	Dim rs As New ADODB.Recordset
	Dim strSQL As String
	Dim msg As String
	Dim olApp As Outlook.Application
	Dim objMail As Outlook.MailItem
 
	Set Conn = CurrentProject.Connection
	Set rs = New ADODB.Recordset
	strSQL = "SELECT * FROM [qry_EmailData] WHERE [TaskSoccerNo] = '111';"
	rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
 
		msg = "A message"
 
	'Create e-mail item
	Set olApp = Outlook.Application
	Set objMail = olApp.CreateItem(olMailItem)
 
	'Send the email
	With objMail
		.BodyFormat = olFormatRichText
		Do While Not rs.EOF
			.Attachments.Add Source:=rs("DocObject")
			rs.MoveNext
		Loop
		.Body = msg
		.Subject = "For " & rs("X")
		.To = rs("email")
		.Display
	End With
 
	Set rs = Nothing