423,335 Members | 1,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,335 IT Pros & Developers. It's quick & easy.

Drag and Drop Outlook Email to Access DB

P: 124
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2.     'I got the guts of this sub from Remou on tek-tips.com. S/he told me I can drag and drop an
  3.     'email to a memo field, then gave me the object control code to save the file.
  5.     Dim olApp As Outlook.Application
  6.     Dim olExp As Outlook.Explorer
  7.     Dim olSel As Outlook.Selection
  8.     Dim i, intCounter, intResponse As Integer
  9.     Dim strFilename, strSQL, strFolderPath, strPathAndFile, strMsg As String
  10.     Dim fs As Object
  11.     Dim fsFolder As Object
  12.     Dim blnFolderExists, blnFileExists As Boolean
  14.     On Error GoTo BAIL
  16.     'This field is used to control attaching emails by dropping them on the field.
  17.     'To allow this the field must be editable. This means the user could accidentally
  18.     'type in the field and trigger the code to attach an email. Therefore, this user
  19.     'verification makes sure the user intentionally dropped an email on the field.
  21.     strMsg = "WARNING: You have triggered the E-mail Attachment Function. CHOOSE CAREFULLY ..." & vbCr & vbCr
  22.     strMsg = strMsg & "If you intended to attach an e-mail to this note, answer Yes below. "
  23.     strMsg = strMsg & "If you did not intend to attach an e-mail and don't know what's going on, "
  24.     strMsg = strMsg & "answer No below." & vbCr & vbCr
  25.     strMsg = strMsg & "Did you intentionally drag and drop an e-mail to attach it to this note?"
  27.     intResponse = MsgBox(strMsg, vbYesNo)
  29.     If intResponse = 7 Then 'No
  30.         Cancel = True
  31.         Exit Sub
  32.     End If
  34.     'My network consultant advises not putting too many files in a folder - like our Permanent Images.
  35.     'Therefore, I will separate emails into a new folder each year. This code allows me
  36.     'to never check on it, by creating the folder automatically when the year changes.
  38.     Set fsFolder = CreateObject("Scripting.FileSystemObject")
  39.     strFolderPath = "D:\HHS\Email"
  41.     If fsFolder.FolderExists(strFolderPath) = False Then
  42.         fsFolder.CreateFolder (strFolderPath)
  43.     End If
  45.     'Create the filename as a message file from the ClientID and the NoteID - which will be unique
  46.     'strFilename = Me.TxtClientID & "_" & Me![SvcNoteID] & ".msg"
  47.     strFilename = "TestEmailAttach_" & Format(Date, "yyyymmdd") & ".msg"
  50.     'Combine for full path and file name
  51.     strPathAndFile = strFolderPath & "\" & strFilename
  53.     'Make sure this file does not already exist to avoid overwriting email files when there is a
  54.     'system glitch.
  56.     Set fs = CreateObject("Scripting.FileSystemObject")
  57.     blnFileExists = fs.FileExists(strPathAndFile)
  59.     If blnFileExists = False Then
  60.         'There's not already a file for this client and noteID.
  61.         'This is the way it always should be.
  62.         'But stuff happens. So, I'm checking.
  63.         'Save the email to the filename just created as a message file.
  64.         Set olApp = GetObject(, "Outlook.Application")  'First argument is blank to return the currently
  65.                                                         'active Outlook object, otherwise runtime fails
  66.         Set olExp = olApp.ActiveExplorer
  67.         Set olSel = olExp.Selection
  68.         For i = 1 To olSel.Count
  69.             olSel.Item(1).SaveAs strPathAndFile, olMSG
  70.         Next
  71.     Else
  72.         'There's already a file for this client and noteID. This should be impossible,
  73.         'but stuff happens. In this case we notify the user and then re-establish the links
  74.         'so the user can handle it.
  75.         strMsg = "ATTENTION: The system detected an e-mail file already created for this note. "
  76.         strMsg = strMsg & "That e-mail is now linked to this note ID. Please do the following:" & vbCr & vbCr
  77.         strMsg = strMsg & "1. View the e-mail normally." & vbCr
  78.         strMsg = strMsg & "2. If it is the correct e-mail, you don't need to do anything else." & vbCr
  79.         strMsg = strMsg & "3. If it is the wrong e-mail, use the Un-Attach E-mail button to get rid of it. "
  80.         strMsg = strMsg & "Then attach the correct e-mail."
  81.         MsgBox strMsg
  82.     End If
  84.     'Update the location field with the location.
  85.     Cancel = True   'To roll back changes caused by the drop.
  86.     Me![EmailLocation] = strPathAndFile
  87.     Me.EmailMemo = "EMAIL ATTACHED: Click Here To View"
  88.     Me.EmailMemo.Locked = True
  89.     Me.Dirty = False    'To save the changes.
  91. BAIL:
  92.    Select Case Err.Number
  93.     Case 287:
  94.       Resume Next
  95.     Case Else:
  96.       MsgBox "Error encountered: " & Err.Description
  97.       Resume Exit_Proc 'display a message then exit'
  98.     End Select
  100. Exit_Proc:
  101.     Exit Sub
  103.     Set fsFolder = Nothing
  104.     Set fs = Nothing
  105.     Set olSel = Nothing
  106.     Set olExp = Nothing
  107.     Set olApp = Nothing
  109. End Sub
1 Week Ago #1
Share this Question
Share on Google+
15 Replies

Expert Mod 15k+
P: 31,004
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.
1 Week Ago #2

P: 124
I tried debugging and the code always throws "application 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.
1 Week Ago #3

P: 124
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.
1 Week Ago #4

P: 124
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).SaveAs 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?
Expand|Select|Wrap|Line Numbers
  1. Private Sub MemoMemo_AfterUpdate()
  2. Dim olApp As Outlook.Application
  3. Dim olExp As Outlook.Explorer
  4. Dim olSel As Outlook.Selection
  5. Dim i
  7. Set olApp = GetObject("", "Outlook.Application")
  9. Set olExp = olApp.ActiveExplorer
  10. Set olSel = olExp.Selection
  11. For i = 1 To olSel.Count
  12.     MsgBox olSel.item(i).EntryID
  13.     olSel.item(i).SaveAs CurrentProject.Path & "\mail.txt", olTXT
  14. Next
  15. End Sub
1 Week Ago #5

Expert Mod 15k+
P: 31,004
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.
1 Week Ago #6

Expert Mod 15k+
P: 31,004
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.
1 Week Ago #7

P: 124
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...
1 Week Ago #8

Expert Mod 15k+
P: 31,004
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.
1 Week Ago #9

P: 124
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.
1 Week Ago #10

Expert Mod 15k+
P: 31,004
That looks promising BtW. Understanding now that it (olTXT) is a constant rather than a variable is a sign of progress.
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).
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.
1 Week Ago #11

Expert Mod 10K+
P: 12,252
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.
1 Week Ago #12

Expert Mod 15k+
P: 31,004
... 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?
1 Week Ago #13

P: 124
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@concursolutions.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.
1 Week Ago #14

Expert Mod 10K+
P: 12,252
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.
1 Week Ago #15

P: 124
Thanks for the advice, Rabbit. Other methods of the same object work in the same code, like .EntryID and .Save. Only .SaveAs always throws error #287 about an "application defined or object defined error".
1 Week Ago #16

Post your reply

Sign in to post your reply or Sign up for a free account.