Drag and Drop Outlook Email to Access DB

[BikeToWork]

I tried to incorporate some code I found online. I keep getting runtime error 287 when I attempt to save the email to Access. Thanks in advance for any advice.

Private Sub cmdSave_Click()
    'I got the guts of this sub from Remou on tek-tips.com. S/he told me I can drag and drop an
    'email to a memo field, then gave me the object control code to save the file.
    
    Dim olApp As Outlook.Application
    Dim olExp As Outlook.Explorer
    Dim olSel As Outlook.Selection
    Dim i, intCounter, intResponse As Integer
    Dim strFilename, strSQL, strFolderPath, strPathAndFile, strMsg As String
    Dim fs As Object
    Dim fsFolder As Object
    Dim blnFolderExists, blnFileExists As Boolean
    
    On Error GoTo BAIL
    
    'This field is used to control attaching emails by dropping them on the field.
    'To allow this the field must be editable. This means the user could accidentally
    'type in the field and trigger the code to attach an email. Therefore, this user
    'verification makes sure the user intentionally dropped an email on the field.
    
    strMsg = "WARNING: You have triggered the E-mail Attachment Function. CHOOSE CAREFULLY ..." & vbCr & vbCr
    strMsg = strMsg & "If you intended to attach an e-mail to this note, answer Yes below. "
    strMsg = strMsg & "If you did not intend to attach an e-mail and don't know what's going on, "
    strMsg = strMsg & "answer No below." & vbCr & vbCr
    strMsg = strMsg & "Did you intentionally drag and drop an e-mail to attach it to this note?"
    
    intResponse = MsgBox(strMsg, vbYesNo)
    
    If intResponse = 7 Then 'No
        Cancel = True
        Exit Sub
    End If
    
    'My network consultant advises not putting too many files in a folder - like our Permanent Images.
    'Therefore, I will separate emails into a new folder each year. This code allows me
    'to never check on it, by creating the folder automatically when the year changes.
    
    Set fsFolder = CreateObject("Scripting.FileSystemObject")
    strFolderPath = "D:\HHS\Email"
    
    If fsFolder.FolderExists(strFolderPath) = False Then
        fsFolder.CreateFolder (strFolderPath)
    End If

    'Create the filename as a message file from the ClientID and the NoteID - which will be unique
    'strFilename = Me.TxtClientID & "_" & Me![SvcNoteID] & ".msg"
    strFilename = "TestEmailAttach_" & Format(Date, "yyyymmdd") & ".msg"
    
    
    'Combine for full path and file name
    strPathAndFile = strFolderPath & "\" & strFilename
    
    'Make sure this file does not already exist to avoid overwriting email files when there is a
    'system glitch.
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    blnFileExists = fs.FileExists(strPathAndFile)
    
    If blnFileExists = False Then
        'There's not already a file for this client and noteID.
        'This is the way it always should be.
        'But stuff happens. So, I'm checking.
        'Save the email to the filename just created as a message file.
        Set olApp = GetObject(, "Outlook.Application")  'First argument is blank to return the currently
                                                        'active Outlook object, otherwise runtime fails
        Set olExp = olApp.ActiveExplorer
        Set olSel = olExp.Selection
        For i = 1 To olSel.Count
            olSel.Item(1).SaveAs strPathAndFile, olMSG
        Next
    Else
        'There's already a file for this client and noteID. This should be impossible,
        'but stuff happens. In this case we notify the user and then re-establish the links
        'so the user can handle it.
        strMsg = "ATTENTION: The system detected an e-mail file already created for this note. "
        strMsg = strMsg & "That e-mail is now linked to this note ID. Please do the following:" & vbCr & vbCr
        strMsg = strMsg & "1. View the e-mail normally." & vbCr
        strMsg = strMsg & "2. If it is the correct e-mail, you don't need to do anything else." & vbCr
        strMsg = strMsg & "3. If it is the wrong e-mail, use the Un-Attach E-mail button to get rid of it. "
        strMsg = strMsg & "Then attach the correct e-mail."
        MsgBox strMsg
    End If
    
    'Update the location field with the location.
    Cancel = True   'To roll back changes caused by the drop.
    Me![EmailLocation] = strPathAndFile
    Me.EmailMemo = "EMAIL ATTACHED: Click Here To View"
    Me.EmailMemo.Locked = True
    Me.Dirty = False    'To save the changes.
    
BAIL:
   Select Case Err.Number
    Case 287:
      Resume Next
    Case Else:
      MsgBox "Error encountered: " & Err.Description
      Resume Exit_Proc 'display a message then exit'
    End Select
    
Exit_Proc:
    Exit Sub
    
    Set fsFolder = Nothing
    Set fs = Nothing
    Set olSel = Nothing
    Set olExp = Nothing
    Set olApp = Nothing

End Sub

[NeoPa]

Just so it doesn't sound like you're simply asking us to do your work for you, may I suggest you :

1. Do some dubugging (Debugging in VBA) on your problem first and find out what's going on and where.
2. Describe what the code's doing and expected to do, as you'd expect if someone were asking you for help.
3. Find and post the error message that goes along with error 287.
4. Determine where the error is thrown and identify that by line number.

After more than one hundred posts I admit to being a little surprised I have to take time to explain some of the basics of posting questions.

[BikeToWork]

I tried debugging and the code always throws "applicatio n defined runtime error 287 at line 69

"olSel.Item(1). SaveAs strPathAndFile, olMSG"

What I am trying to do with the code is paste an outlook email to a memo field and then save the email to file. Sorry about posting all the code. I just thought it might be best to have too much than too little. By the way, the code was originally for the On Dirty event of the text box. I tried to use a command button to execute the code since the "On Dirty" event was not firing when I pasted an email to the textbox. Thanks for any advice.

[BikeToWork]

what I really want to do is copy and paste an Outlook email to a form in Access and save the email to a .msg file via Access. I am not wed to the code posted. I would really like it better if there were an easier way of doing it. Does anyone have any experience with such a process? Thanks in advance for any help/advice.

[BikeToWork]

Here is the condensed form of the code I am trying to run, generating Application or object defined error 287 on
the line:

olSel.item(i).S aveAs CurrentProject. Path & "\mail.txt"

The saveas method does not show up in intellisense for the olsSel.item(I) object. How does one go about saving an outlook email to Access?

Private Sub MemoMemo_AfterUpdate()
Dim olApp As Outlook.Application
Dim olExp As Outlook.Explorer
Dim olSel As Outlook.Selection
Dim i

Set olApp = GetObject("", "Outlook.Application")

Set olExp = olApp.ActiveExplorer
Set olSel = olExp.Selection
For i = 1 To olSel.Count
    MsgBox olSel.item(i).EntryID
    olSel.item(i).SaveAs CurrentProject.Path & "\mail.txt", olTXT
Next
End Sub

[NeoPa]

BikeToWork:
Sorry about posting all the code. I just thought it might be best to have too much than too little.

I wouldn't worry about including so much code. That's generally fine as long as the explanation directs where your main concern is. What was much more worrying was the lack of anything else. It all looks a lot better now so I'll see what I can see. Not the best expert at Outlook work though I do have some Application Automation experience.

[NeoPa]

Let's just make a quick point here. It will be easier to work together by simply using the line number in order to reference a line. In this case what you've posted in the text, and line #13 of your post #5, differ as the latter includes a reference to olTXT which I believe may be important. It doesn't appear to be DIMmed anywhere in your code, which could be a problem.

Another point I noticed is that where you've referenced olSel.item(i) the word Item hasn't been updated and is still all lower case. This tells me that the item referenced is not recognised by VBA. As this code appears to be running from within Access but using an instance of an Outlook application, I would guess you maybe haven't added any reference to Outlook in your project references. This isn't always important for the code to work, but it is what provides the intellisense that can help you avoid problems.

Once you have that you can certainly convert your code to late-binding if you wish, but that's another discussion. Certainly get it working with early-binding first, and don't forget that much of what might be available to you by default when working within Outlook itself, may be missing when using Application Automation.

The last point I'd make would be a question about what you're doing. I mentioned earlier I'm no great afficionado of Outlook coding, but I read what you're doing as taking what may be a bunch of selected objects from the main Outlook window, a bunch of selected e-mails from within a particular folder for instance, and saving them one at a time to the file system. I noticed that when I went to my own Outlook window and selected an individual e-mail from within the list, I didn't see the option to SaveAs in the right-click menu. That would lead me to believe that such an option isn't possible. Not definitive I know, but surely worth checking.

[BikeToWork]

My goal is simply to drag and drop an email from Outlook into an Access form and save the email in Access. All I have been able to find online on the subject are code snippets. These snippets contain variables like "olTXT" that make no sense in the context of the snippet and the full code is not there. There must be some way to do it...

[NeoPa]

BikeToWork:
There must be some way to do it...

I'm sure there is, but how do you expect specific help with your code if it isn't your code and you don't even understand what it's trying to do.

At some point, if you look and can't find, then you need to consider doing it yourself - possibly with help. Even to get help you need more of an understanding of what you're doing than you seem to have at the moment.

I would sugest, if you're still sure you're up for what it is likely to take from you, that you keep searching for both code and understanding (a blog on the subject maybe) until you find something that either works, or that you understand well enough to discuss properly when you ask for help. That may take your getting your head down into some serious learning/understanding. I doubt you'll be sorry if you commit to such an endeavour but obviously it's your choice how you proceed from here.

Good luck whatever you decide.

[BikeToWork]

NeoPa, thanks for the encouragement. I have year's of experience with VBA code, but Access and Excel only. I have not done Outlook automation before. I actually do understand the code snippet I posted pretty well. OlTxt is a constant for the SaveAs method. I just don't understand why it always errors out at the SaveAs line of code. I will keep plugging away, but could use some help from someone who has actually done this.

[NeoPa]

That looks promising BtW. Understanding now that it (olTXT) is a constant rather than a variable is a sign of progress.

BikeToWork:
I just don't understand why it always errors out at the SaveAs line of code.

I did make a comment designed to encourage you to look into this further in my earlier post. Did you get anywhere with that? I've looked into (See Debugging in VBA for usage of the Object Explorer pane.) the Selection object and it's Items are non-specific Objects. This would certainly explain why IntelliSense isn't much help. As they are all still unopened Items I suspect it simply isn't possible to apply .SaveAs() to them (Again, as I suggested might be the case earlier).

BikeToWork:
I will keep plugging away, but could use some help from someone who has actually done this.

Good for you on the attitude, and I agree someone with more experience of Outlook would be helpful. Until then, particularly as we're in an Access forum and they might be thin on the ground, I'll hang with you in case there's anything further I can offer.

PS. Use the Object Explorer. It's invaluable when starting to work in a new application such as Outlook. Obviously your friendly search engine can also be enormously helpful to you as well.

[Rabbit]

According to Microsoft's documentation on the selection object, you shouldn't make assumptions about the item's return type. You should handle all return types.

My guess is that the selection is returning items other than MailItems, perhaps even items that don't have a SaveAs method.

The reason the SaveAs method doesn't show up in Intellisense is because the Item method returns a Variant. It can return any type of item, so Intellisense has no way of knowing what is going to be returned and therefore no way of knowing what methods and properties will be available.

Also, you keep mentioning dragging and dropping but I don't see in the code where you handle that. The code is just triggering off an AfterUpdate and grabs whatever is selected in the active outlook explorer. As far as I'm aware, dragging and dropping of files is not something native to Access VBA.

[NeoPa]

Rabbit:
... the Item method returns a Variant.

Technically an Object I believe, but it comes down to almost exactly the same thing as far as this discussion is concerned. There can be no assumptions about what type of Object is actually being used. I too, suspect the Objects don't support the SaveAs() method.

Thanks for jumping in. Most of what I know about this is what I've looked up and worked out since the question was posted. What would I ever do without Object Explorer?

[BikeToWork]

What I've been testing is dragging and dropping an email from Outlook to a memo field on an Access form. When I drag and drop an Outlook email to the textbox, something like the following shows up:

From Subject Received Size Categories
DoNotReply@conc ursolutions.com EXTERNAL: Password Reset Request 12:41 PM 22 KB

This looks like the email header, but it should not affect the code which tries to save the Outlook message. Thanks for any advice.

[Rabbit]

You should still test for the correct object type as suggested by Microsoft. It's possible the explorer is returning multiple items unbeknownst to you.