Reading an Outlook attachment in VBA - 2nd try - please 1

[PizMac]

I asked this question back in March and got no replies - I'm still wanting to do this - so am trying again...

I need to read an attachment to an email in Access - VBA on a form. I can get at my inbox with File-Import/Attach but this only tells me IF there is an attachment or not - I actually need to be able to read the attachment - does anyone out there have any ideas??

Please and Thank you!

 

[pdldavis]

Hi,

Create a new form and insert an ActiveX Microsoft Outlook View Control. Also ensure you have the Outlook library referenced.

While it doesn't open the attachment within the form, you can open the attachment from the form.

Hope that helps.

 

[Remou]

As far as I know, you will have to save the attachment first. You can then use FollowHyperlink to open it with its registered program.

 

[PizMac]

pdldavis - thanks for replying but (maybe I'm being dense) I can't work out how to add the ActiveX control. I have a tool called "activeX plug in object" but when I put it on the form I get "doesn't support his SctiveX control". have tried the help but got stuck at "Replace <ActiveX Control GUID> with the globally unique identifier (GUID) that identifies the ActiveX control that you want to enable in expressions." - where do I find the GUID? do I need to do something in Outlook?
Thanks in advance

 

[pdldavis]

Hi,

In design view, select "Insert ActiveX Control". Scroll down and select "Microsoft Outlook View Control" and press ok. Size the view control the way you want.

While in design view, go to the code builder and select Tools/References. You should see "Microsoft Outlook View Control" checked. See if "Microsoft Outlook Object XX Libray" is checked. If Not, check it as well. Might as well do the same for DAO 3.6 if that is not checked too.

Exit out of the code builder and view the form. You should see your inbox, with attachments. Click on an attachment and it will open.

There are also options for selecting what you want to veiw in the control but I am kind of rusty on that.

The guid thing I don't know about. I am using Office 2002
and have not had any trouble.

 

[PizMac]

thanks pdldavis - I'm getting there... any idea how I can program a loop to read through the inbox and open attachments automatically from code rather than having to click on them?

 

[pdldavis]

Hi, I am afraid that is a bit above my paygrade. If I was to guess, I think it would be something like this:

Get some code for importing the inbox into a table:

http://www.blueclaw-db.com/read_email_access_outlook.htm

The code there imports unread emails.

See if you can tweak it to bring the attachment in as a hyper link and then loop through the links to read them.

That's just a guess.....

 

[PizMac]

I have worked out how to (in code) read Outlook emails, intergogate the subject line, and save the attachments (if they exist) to a folder on a drive for later processing - if anyone's interested just ask

 

[romnew]

Hi Pizmac,
Your code will be much appreciated.
Thanks
Romnew

 

[PizMac]

While in design view, go to the code builder and select Tools/References. Check "Microsoft Outlook Object XX Libray". Might as well do the same for DAO 3.6 if that is not checked too. Good luck - it does work.

in code:-

if SaveAttach = false then
' error - no files
endif


Function SaveAttach() As Boolean
Dim oOutlook As Outlook.Application
Dim oNs As Outlook.NameSpace
Dim oTopFldr As Outlook.MAPIFolder
Dim oFldr As Outlook.MAPIFolder
Dim omessage As Object
Dim osubject As String

Dim iCtr As Integer, myFileCt As Integer, myFname As String
Dim iattachCnt As Integer

Set oOutlook = New Outlook.Application
Set oNs = oOutlook.GetNamespace("MAPI")
Set oTopFldr = oNs.GetDefaultFolder(olFolderInbox) ' position to inbox
Set oFldr = oTopFldr.Folders("Pending") ' and sub folder "pending"


SaveAttach = False
myFileCt = 0
For Each omessage In oFldr.Items

With omessage.Attachments
iattachCnt = .Count
If iattachCnt > 0 Then ' at least 1 attachment
osubject = Trim(omessage.Subject)
If Left(osubject, 8) <> "ACardSys" Then ' already detached/processed - optional see below
For iCtr = 1 To iattachCnt
myFname = .Item(iCtr).FileName
.item(iCtr).SaveAsFile myIpPath & myFname
myFileCt = myFileCt + 1
Next iCtr
omessage.Subject = "ACardSys" & Now & osubject ' mark detached on date so don't do again
omessage.Save ' these 2 optional to mark when you've saved the attachment
End If
End If
End With

Next omessage

If myfileCt > 0 Then
SaveAttach = True
Else
MsgBox "No file attachments found", vbCritical, "Process abandoned"
End If
Set omessage = Nothing
Set oFldr = Nothing
Set oNs = Nothing
Set oOutlook = Nothing

End Function