Outlook attachments-Link/Autosave? 1

[Rzrbkpk]

I have created a simple Access program, but some of the information that I need is supplied weekly from various senders. These senders use an Excel form that is attached to an Outlook e-mail. I do not get these e-mails in my inbox. They are sent to a shared public folder. I want to know if I can link these attachments with Access or if this is not possible, then is there VBA code to autosave attachments to my Hard drive without having to manually run a macro(how do I associate that code with that folder?)? The e-mails do all have the same subject line and Sent TO if that helps. Any help would be appreciated. Thanks.

 

[bboffin]

You can open an Outlook Application object, open a MAPI session and navigate to any folder including public folders. You can then examine each message in that folder and identify the ones you wish to process.

Once a message has been identified you can download the attachment and import it using TransferSpreadsheet.

It's quite involved and is a pretty steep learning curve but once you've mastered it you'll have a useful technique that can be adapted for any similar requirements.

 

[Rzrbkpk]

Does anyone else have insight on the code. I'm not completely ignorant of VBA.

 

[xvh]

Try this code, you can place it into an access module and then use it to reference data from the attachments as desired if you create an object to open the files into excel.

Sub Extract_Attachement()

Dim olapp As Outlook.Application
Dim olnamespace As NameSpace
Dim olmail As MailItem
Dim olatt As Attachment


Set olapp = CreateObject("Outlook.application")
Set olnamespace = olapp.GetNamespace("MAPI")
Set myolfolder = olnamespace.Folders("Core Folder").Folders("Sub Folder") 'etc. etc.

' cycle through the mailitems in the folder...this method only works if folder only contains mail items

For Each olmail In myolfolder.Items

' Check if mail has attachments

If olmail.Attachments.Count > 0 Then
For Each olatt In olmail.Attachments

' Will only display filename here, but you could save to a specific location, open in Excel andread in certain cells etc

MsgBox olatt.Filename
Next
End If
Next

End Sub

 

[Rzrbkpk]

Thanks xvh! Just a few things:

1. I get a compile error: User-Defined type not defined on my declarations. If I skip or eras them, the code still runs. Should I be concerned with this?

2. Can I change my conditional If criteria to "Sent To" instead of number of attachments?

3. Can I change the method of viewing the filename to a "Save As" dialog box?

Also, when I try to link tables to my attachments (Excel files) I run into an error. All the Excel files have the column titles starting on row 14. I don't want any of the information in rows 1-13. Can I just import/link from row 14 on? How?

Thanks again for your time.

 

[xvh]

Ok. I missed out the fact that you need to reference the Microsoft Outlook Object Library. In VB Editor goto tools, references and then check the entry for this. This should resolve point 1.
Once the above has been fixed you will see all of the 'members' of the Mailitem Object which does include .sento.

Once you have the attachment referenced in the code you do what ever you like with it, open it, print it, save it etc. Again this will be clear once the library is installed.
As for the last point, you need to set up an excel object, ie.

Dim myapp as Excel.Application 'Optional
...

Set myapp = createobject("Excel.Application")

...
'from above
olatt.saveas "c:\temppath.xls"

myapp.workbooks.open "c:\temppath.xls"

You've then got the file open in excel and can reference whatever range you want in the normal VBA ways.

Sorry for the brief reply as am in a bit of a rush.

 

[Rzrbkpk]

I've referenced both MS Outlook Library and Excel. This fixed the user-defined error. The code below is what I have, but I get 'Type Mismatch' error on Set olTo=olmail.To Can you look at my complete code and tell me what needs to be corrected. Thanks.

Sub Extract_Attachment()
Dim olapp As Outlook.Application
Dim olnamespace As NameSpace
Dim olmail As MailItem
Dim olatt As Attachment
Dim myapp As Excel.Application
Dim olTo As Recipients

Set myapp=CreateObject("Excel.Application")
Set olapp=CreateObject("Outlook.Application")
Set olnamespace=olapp.GetNamespace("MAPI")
Set olTo=olmail.To
Set myolfolder=olnamespace.Folders("Close Date Archive")

For Each olmail In myolfolder.Items

If olTo="grdry" Then
For Each olatt In olmail.Attachments

olatt.SaveAsFile "C:\Close Date\"&"olmail.Subject"&".xls"
Next
End If
Next

End Sub

 

[xvh]

Ok, firstly you are setting the olTo variable before olmail has been assigned to anything with the for statement.

Secondly, olmail.To is not a recipients collection, it is a strig, and therefore you will get a mis-match of type. There are two ways of tackling this, you either use 'instr' to look in the string for the address you want, or you cycle through the recipients using the following code

Sub Extract_Attachment()
Dim olapp As Outlook.Application
Dim olnamespace As NameSpace
Dim olmail As MailItem
Dim olatt As Attachment
Dim myapp As Excel.Application
Dim olTo As Recipient


Set myapp = CreateObject("Excel.Application")
Set olapp = CreateObject("Outlook.Application")
Set olnamespace = olapp.GetNamespace("MAPI")

Set myolfolder = olnamespace.Folders("Close Date Archive")

For Each olmail In myolfolder.Items

For Each olTo In olmail.Recipients

If olTo.Name = "grdry" Then
For Each olatt In olmail.Attachments

olatt.SaveAsFile "C:\Close Date\" & "olmail.Subject" & ".xls"
Next
End If

Next

Next

End Sub


Hope this helps.

 

[brettr]

I'm trying to get the body part of an Outlook message but am receiving a type mismatch error.

Sub SaveAs()
Dim i As Integer

Set myOlApp = CreateObject("Outlook.Application")
Set myNamespace = myOlApp.GetNamespace("MAPI")
Set myFolder = myNamespace.GetDefaultFolder(olFolderInbox)

i = 1
Do Until i = 7
If myFolder.Items(i).Subject = "RE: My Message" Then
Set MyItem = myFolder.Items(i).HTMLBody
End If
i = i + 1
Loop

End Sub

I get a type mismatch on
Set MyItem = myFolder.Items(i).HTMLBody

I have tried declaring MyItem as a string but that doesn't work either. I also get the mismatch if I use
myFolder.Items(i).Subject

It works if only myFolder.Items(i) is assigned to MyItems. I'm also doing a watch and can see the vars are Variant/String and have values except on the above assign. The left and right sides are of the same type. Why do I receive a type mismatch?

Thanks,
Brett

 

[xvh]

if MyItem is simply a string then you do not use the 'Set' statement. Remove 'Set' and that should work OK.

 

[brettr]

That worked perfect. Thanks.

Now that I have the message body, how can I save it to a file? I can't use MyItem.SaveAs (path) because MyItem isn't an object.