embed excel file in access and email as PDF (AUTOMATE) Please help
Posted on 2006-04-11
I'm a newbie here and a rookie programmer, for the past few days I've been breaking my head as to how to do this... I'm trying to save an excel file within my Access 2000 DB. in case the excel file gets deleted on the network It could still be opened from Access, but here's the tricky part.
I have a text box [quote] - where I type in a quote excel file name and an unbound object frame [quoteolefile] which searches for the excel file thru a path with this source code..
Private Sub quoteolefile_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
On Error GoTo Error_quoteolefile_MouseUp
.Enabled = True
.Locked = False
' Specify what kind of object can appear in the field.
.OLETypeAllowed = acOLECreateFromFile
' Class statement--optional for Excel worksheet.
.Class = ".xls"
' Specify the file to be linked.
' Type the correct path name.
.SourceDoc = "\\Srv-ndt\main\NDT_Transducers\JFCECNTEST\excelandvmx\" & Me![quote] & ".xls"
' Range statement--optional for Excel worksheet.
.SourceItem = ""
' Create the embedded object.
.action = acOLEembed
' Optional size adjustment.
.SizeMode = acOLESizeClip
MsgBox CStr(Err) & " " & Err.DESCRIPTION
but I get error message 2777, I'm not sure why whether it's linked or embedded I get the same error code 2777.
My mission is to keep a different quote excel file for every record in my database, but because most people don't have access to my network drive I can't link the excel file so I need to have the quote excel file open in access in a way that they can view the file like an email attachment, so they can open the quote in excel.
but in the midst of things I need this automated so that I type the quote# in a text box and it searches thru my drive.
Then I need to send this file as an email attachment in outlook and an attachment of the report as "PDF"
Please help, is this even possible to do? maybe I'm just getting my hopes up. I would really appreciate your help.