File/GetExternalData/Link/Exchange()

**timmg** · Jun 27 '08, 07:24 PM

Re: File/GetExternalData/Link/Exchange()

On May 19, 12:01 am, Salad <o...@vinegar.c omwrote:

In A2003 one can create a linked table using
File/GetExternalData/Link/Exchange(). It's a nice feature but by and
large fairly useless if I am reading the table structure correctly.
>
The From & SenderName columns may contain data like joeb...@test.co m or
it may simply be Joe Blow. The HasAttachments column may be true or
false but accessing the attachments isn't available if the value is true.
>
Is there any way this linked table could be of use in getting the email
addresses as well as the name of the sender as well as associating or
getting the attachments? Or can one access the email via automation
with the data contained in the linked table?

I've been working on a similar problem - I have a bunch of attachments
stored in messages in a public folder that needed to be associated
with an Access database.

The quickest way I found was to attach code to Outlook to interogate
the selected folder, then walk through the messages looking for
attachments and, if found, copy them to a directory whilst copying
info about the message and file name (I had to rename a number of
attachments because their original name was form.pdf) to a text file.
Based on the time contraints and what I didn't originally understand
about the project I ended up with quite a kludge. Here's the first
part (just before it gets reall ugly)"

Public Sub SaveAllAttachme nts()

'Note, this assumes you are in the a folder with e-mail messages
when you run it.
'It does not have to be the inbox, simply any folder with e-mail
messages
'On Error GoTo ErrorHandler

Dim App As New Outlook.Applica tion
Dim Exp As Outlook.Explore r
Dim Sel As Outlook.Items
Dim sSub As String
Dim sFile As String
Dim att As Attachment
Dim attName As String

Dim AttachmentCnt As Integer
Dim AttTotal As Integer
Dim MsgTotal As Integer

Dim outputDir As String
Dim outputFile As String
Dim fileExists As Boolean
Dim cnt As Integer
Dim lf As Integer
Dim iNS As Integer

'Requires reference to Microsoft Scripting Runtime (SCRRUN.DLL)
Dim fso As FileSystemObjec t

Set Exp = App.ActiveExplo rer
Set Sel = Exp.CurrentFold er.Items
Set fso = New FileSystemObjec t
lf = FreeFile()

outputDir = "S:\public\proj ect_folder\Form s\"

'Loop thru each selected item in the inbox
Open outputDir & "file_list. txt" For Output As #lf
Write #lf, "Subject", "Attachment ", "FileName", "Status"
For cnt = 1 To Sel.Count
'If the e-mail has attachments...
If Sel.Item(cnt).A ttachments.Coun t 0 Then

### end sample ####

The original code I modified is at http://arcanecode.files.wordpress.co...tachments3.txt

If I was smarter I imagine that I could do all of this from within
Access and move the files one at a time.

I too am interested in hints and guidance on how to do that.

Tim Mills-Groninger

**Salad** · Jun 27 '08, 07:24 PM

Re: File/GetExternalData/Link/Exchange()

timmg wrote:

On May 19, 12:01 am, Salad <o...@vinegar.c omwrote:
>

>>In A2003 one can create a linked table using
>>File/GetExternalData/Link/Exchange(). It's a nice feature but by and
>>large fairly useless if I am reading the table structure correctly.
>>
>>The From & SenderName columns may contain data like joeb...@test.co m or
>>it may simply be Joe Blow. The HasAttachments column may be true or
>>false but accessing the attachments isn't available if the value is true.
>>
>>Is there any way this linked table could be of use in getting the email
>>addresses as well as the name of the sender as well as associating or
>>getting the attachments? Or can one access the email via automation
>>with the data contained in the linked table?

>
>
I've been working on a similar problem - I have a bunch of attachments
stored in messages in a public folder that needed to be associated
with an Access database.
>
The quickest way I found was to attach code to Outlook to interogate
the selected folder, then walk through the messages looking for
attachments and, if found, copy them to a directory whilst copying
info about the message and file name (I had to rename a number of
attachments because their original name was form.pdf) to a text file.
Based on the time contraints and what I didn't originally understand
about the project I ended up with quite a kludge. Here's the first
part (just before it gets reall ugly)"
>
Public Sub SaveAllAttachme nts()
>
'Note, this assumes you are in the a folder with e-mail messages
when you run it.
'It does not have to be the inbox, simply any folder with e-mail
messages
'On Error GoTo ErrorHandler
>
Dim App As New Outlook.Applica tion
Dim Exp As Outlook.Explore r
Dim Sel As Outlook.Items
Dim sSub As String
Dim sFile As String
Dim att As Attachment
Dim attName As String
>
Dim AttachmentCnt As Integer
Dim AttTotal As Integer
Dim MsgTotal As Integer
>
Dim outputDir As String
Dim outputFile As String
Dim fileExists As Boolean
Dim cnt As Integer
Dim lf As Integer
Dim iNS As Integer
>
'Requires reference to Microsoft Scripting Runtime (SCRRUN.DLL)
Dim fso As FileSystemObjec t
>
Set Exp = App.ActiveExplo rer
Set Sel = Exp.CurrentFold er.Items
Set fso = New FileSystemObjec t
lf = FreeFile()
>
outputDir = "S:\public\proj ect_folder\Form s\"
>
'Loop thru each selected item in the inbox
Open outputDir & "file_list. txt" For Output As #lf
Write #lf, "Subject", "Attachment ", "FileName", "Status"
For cnt = 1 To Sel.Count
'If the e-mail has attachments...
If Sel.Item(cnt).A ttachments.Coun t 0 Then
>
### end sample ####
>
The original code I modified is at http://arcanecode.files.wordpress.co...tachments3.txt
>
If I was smarter I imagine that I could do all of this from within
Access and move the files one at a time.
>
I too am interested in hints and guidance on how to do that.

The File/GetExternalData/Link/Exchange() seems like someone created a
demo of what a person could do between Access and Outlook. It's like a
teaser, somebody did it at MS to show some capabilities and then either
dropped it or else decided it was a bit too complicated to follow thru
to make it a full fledged process. Why it was even included in Access
is beyond me.

There's code in Google for using automation but using built in features
to use queries and recordsets would have been nice.

Good luck in your design.

>
Tim Mills-Groninger

File/GetExternalData/Link/Exchange()

File/GetExternalData/Link/Exchange()

Comment

Comment