Drag and drop Outlook email into Excel Userform to capture email details and attachments

[bradyboyy88]

Basically I am trying to see if this is even possible. Taking an email and dragging it over some userform object such that when the user lets go it captures information from the email such as a unique ID of some sort and the attachments ( I am going to extract these and save in another directory). Starting my research now but curious if anyone knows any shortcuts to making this happen?

 

[bradyboyy88]

Any ideas if this is even possible?

I see threads about draggin and dropping files but nothing about emails.

 

[bradyboyy88]

I have been looking into this again. I have found the treeview tool allows for native drag and drop events. I however cannot seem to get it working though. Here is what I have so far which is for dragging and droping files (not what I want as I want to drag and drop emails but I figure this is a start):

Option Explicit


Sub UserForm1_Initialize()
    TreeView1.OLEDropMode = ccOLEDropManual
    UserForm1.Show vbModeless
End Sub


Private Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
    MsgBox "test"
    strPath = Data.Files(1)
End Sub

 

[RoryA]

IIRC, VB6 couldn't handle Outlook message formats (they don't drag as files) natively, so I suspect VBA can't either.

 

[bradyboyy88]

So is there no way to even get the email ID from the drag and drop object so that I can manually create a mailitem to search for it in outlook and do whatever?

Thanks!

 

[RoryA]

Not that I know of.

 

[bradyboyy88]

Do you have any other ideas for some ways to automate this concept. If I cant drag and drop the outlook email to excel. Since this program is portable and should run from one application. Does it seem feasible to have excel add a button to excel ribbon or a right click menu option on the fly?

 

[RoryA]

Does it seem feasible to have excel add a button to excel ribbon or a right click menu option on the fly?

Yes, that's reasonably simple. What would the button do?

 

[bradyboyy88]

The buttons would basically do an ADO connection to update some database stuff then reload the excel userform with the fresh data which includes send date and a few other things in the email. How would I go about adding an outlook ribbon item from excel? Is there any permission errors you think I would come across?

 

[RoryA]

How would I go about adding an outlook ribbon item from excel?

That's not what you said earlier - you said an Excel ribbon item.

You should be able to use GetObject to get the Outlook application, then use its Commandbars collection to add a new button to the AddIns tab (I have not tested so this may be wrong - Outlook can be weird!) but how you would get that to call code in your Excel workbook might be trickier. It may be simpler to use events to monitor Outlook temporarily - I'll have a think.