Results 1 to 14 of 14
  1. #1
    cgschlangen
    Guest

    E-Mailing OLE Objects (Access 2000)

    Here's the scoop - I have a form that I use to manage sound files here at work... The files are all linked to the database as OLE Objects (allowing a user to click on the icon which opens the file in a new window)... I'm looking from here - to find a way to have a command button open an e-mail and place the OLE Object (in the current record) in the e-mail as an attachment... I know it has to be done through VBScript - but I'm not that knowledgeable when it comes to that... If there is an example that I could pick apart, or a previous post w/ this type of question - please let me know...
    Thanks
    Dan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E-Mailing OLE Objects (Access 2000)

    If you use Outlook, you can use Automation to create an e-mail, append an attachment and send it from Access. See if the example code in HOW TO: Use Automation to Send a Microsoft Outlook Message using Access 2000 gets you started. If you need more assistance, post back.

  3. #3
    cgschlangen
    Guest

    Re: E-Mailing OLE Objects (Access 2000)

    Wonderful!
    One part out of the way...
    One question though - How do I go about making the file that's in my form - the attachment? Am I making sense or should I elaborate a little more...
    Thanks a million!
    Dan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E-Mailing OLE Objects (Access 2000)

    I'll look into this later, no time now, sorry. Perhaps the SourceDoc property of the bound object frame containing the OLE object will help.

  5. #5
    cgschlangen
    Guest

    Re: E-Mailing OLE Objects (Access 2000)

    No prob - I don't have a heavy deadline... just want to get it off my desk - lol
    I have this code which (is supposed to) pull up the file path...
    <pre>Function GetLinkedPath(File As Variant) As Variant
    Dim strChunk As String
    Dim pathStart As Long
    Dim pathEnd As Long
    Dim path As String
    If Not IsNull(File) Then
    ' Convert string to Unicode.
    strChunk = StrConv(File, vbUnicode)
    pathStart = InStr(1, strChunk, ":", 1) - 1

    ' If mapped drive path not found, try UNC path.
    If pathStart <= 0 Then pathStart = _
    InStr(1, strChunk, "", 1)

    ' If either drive letter path or UNC path found, determine
    ' the length of the path by searching for the first null
    ' character Chr(0) after the path was found.
    If pathStart > 0 Then
    pathEnd = InStr(pathStart, strChunk, Chr(0), 1)
    path = Mid(strChunk, pathStart, pathEnd - pathStart)
    GetLinkedPath = path
    Exit Function
    End If
    Else
    GetLinkedPath = path
    End If
    End Function
    </pre>



    But I don't think it works - because of the server paths... Is there any way to incorporate (a working version of) this into the mail script
    designating it as the attachment?
    Thanks
    Dan <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E-Mailing OLE Objects (Access 2000)

    You can use GetLinkedFile(Me.OLEField) where OLEField is the name of the OLE field. Something like (using the names in the sample code from the MSKB article):

    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem

    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
    '...
    .Attachments.Add GetLinkedFile(Me.OLEField)
    '...
    End With

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E-Mailing OLE Objects (Access 2000)

    As the MSKB article states, Access does not provide a built-in way to retrieve the path of a linked OLE object. The recommended workaround is to store the path in a separate text field. The GetLinkedFile function is a bit of a kludge - it looks for the occurrence of ":" or "" in the OLE header, but this can lead to a bogus path.
    If you insert a statement MsgBox GetLinkedFile(Me.OLEField) (where OLEField is the name of the OLE field), you can see what the result of the function is.

    An alternative would be a hyperlink field - this is the equivalent of a hyperlink in HTML pages, or even a standard text field. Say you have a text field SoundPath; your form contains a text box txtSoundPath bound to this field. You could put a command button on the form that executes Application.FollowHyperlink Me.txtSoundPath when clicked.

    When e-mailing, you can use (in terms of the code discussed before) .Attachments.Add Me.txtSoundPath

  8. #8
    cgschlangen
    Guest

    Re: E-Mailing OLE Objects (Access 2000)

    Well - It's working - or trying to rather... which excites me to no end! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    I think the only thing is - that the GetLinkedFile function doesn't work correctly (I'm not sure exactly why - the MSKB article for this function said:

    "NOTE: It is possible for the OLE header to contain an occurrence of either string pattern in a location that does not store the path to the linked object. If this occurs, the procedure returns garbage characters rather than the path to the linked object. "

    And I think that this is one of those occurences... but they didn't include a way to get around this....
    In HTML (which I know isn't anything like VBscript) you can make a link by using something like "..someotherfoldersomefile.html" which would send a user back one folder and to another folder and another file from the file they clicked it to... so if I wanted to go to a file from the database (theoretically) I could use ..filessoundfile.wav --- I know that won't work - - - but is there another way of doing this?

    Thanks for all your help Hans - you've been great! <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
    Dan

    Ahhh - P.S. The reason I think it's the GetLinkedFile function is because when I click the Command Button I get this Error:
    The filename or extension is too long.
    and when I click Debug - it brings me to this line in my code:
    .Attachments.Add GetLinkedFile(Me.File)

  9. #9
    cgschlangen
    Guest

    Re: E-Mailing OLE Objects (Access 2000)

    Alright - I'm gonna follow your advice on this one... I've created a text field - and on my form have the Text Field: File
    This is the module I'm using to send the link from the command button:

    Option Explicit

    Private Sub SendMessage_Click()
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookAttach As Outlook.Attachment
    Dim objAttachment



    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Set objOutlookAttach = .Attachments.Add(Me.File)</span hi>


    End With
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    End Sub

    And I'm getting an error -- The property does not exist. The field you want to modify is not valid for this type of item. -- And when I debug - it takes me to the highlighted line above... Can you see anything wrong w/ it? - is it because the sub thinks I'm trying to send a text field and not the link inside of it?

    Also - as I said I'm using the text field to store the path in - but is there any way - I can use a command button to open the file in the default program for the file itself? When I had it stored as a Linked OLE it was nice cuz it popped the cool little icon in there and when they clicked the button it opened the Sound File in Sound Recorder... I'd like the end user to have some controls - such as Stop, Play, Pause etc....

    Thanks
    Dan

  10. #10
    cgschlangen
    Guest

    Re: E-Mailing OLE Objects (Access 2000)

    Well - if this doesn't drive me nuts - it's bound to drive you nuts... I wanted to let you know before we continue that I've appreciated all your help so far Hans [img]/forums/images/smilies/smile.gif[/img]

    The API Function Call you posted above - isn't layed out correctly (at least VBScript isn't accepting it - but I can work on that later...) The Appliction.FollowHyperlink Me.File.Value will work for now...

    I've included a .zip of a sample database w/ the errors I'm getting - it may help w/ troubleshooting... I can't figure out what is going on... I deleted all the fields out of it - so you'll have to link to a file in it... (I'm using .wav files here at work)

    Thanks Again
    Dan
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E-Mailing OLE Objects (Access 2000)

    Try using Me.File.Value instead of Me.File.

    To open a file with its associated application, you can use Application.FollowHyperlink Me.File.Value, or use the Windows API function ShellExecute:

    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Public Const SW_SHOWNORMAL= 1
    Public Const SW_SHOWMAXIMIZED As Long = 3

    Call it like this:

    Private Sub cmdOpen_Click()
    Dim lngResult As Long
    lngResult = ShellExecute(hWndAccessApp, "Open", _
    Me.File.Value, 0&, 0&, SW_SHOWNORMAL)
    If lngResult <= 32 Then
    MsgBox "Couldn't open file.", vbExclamation
    End If
    End Sub

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E-Mailing OLE Objects (Access 2000)

    Your file field is not a text field, but a hyperlink field. Hyperlink fields are organized in a special way internally, you can't just refer to the Value property. I have changed the field type to text, and it works OK now.
    There was a small error in the code in my previous reply - a left-over quote; I corrected the post. Also, you can't include the code for ShellExecute "as is" in a form module, for you can't declare public constants in an object module. I have put the code in a separate standard module.

    I have attached the modified database.
    Attached Files Attached Files

  13. #13
    cgschlangen
    Guest

    Re: E-Mailing OLE Objects (Access 2000)

    Hans - I haven't gotten a chance to check it out - as I'm at home - but Two Thumbs up for your help! I really appreciate it [img]/forums/images/smilies/smile.gif[/img]

    Dan

  14. #14
    cgschlangen
    Guest

    Re: E-Mailing OLE Objects (Access 2000)

    Hans,
    Works like a dream! Thanks again for everything...
    Dan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •