Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Outlook / Email

Dean Spencer
Guest
  
Posts: n/a
#1: Dec 11 '06
Can anyone help? I am importing Emails from Outlook using the following
code:

Public Function ScanInbox(SubjectLine As String)
Dim TempRst As Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim InboxItems As Outlook.Items
Dim Mailobject As Object
Set OlApp = CreateObject("Outlook.Application")
Set Inbox = OlApp.GetNamespace("Mapi").GetDefaultFolder(olFold erInbox)
Set TempRst = CurrentDb.OpenRecordset("tbl_OutlookTemp")
If SubjectLine <"" Then
Set InboxItems = Inbox.Items.Restrict("[Subject] = """ & SubjectLine
& """")
Else
Set InboxItems = Inbox.Items
End If
For Each Mailobject In InboxItems
With TempRst
On Error Resume Next
.AddNew
!Subject = Mailobject.Subject
!From = Mailobject.SenderName
!To = Mailobject.To
!Body = Mailobject.Body
!DateSent = Mailobject.SentOn
!Attachment = Mailobject.Attachment
.Update
'Mailobject.Delete
Mailobject.Read
End With
Next

Set OlApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set Mailobject = Nothing
Set TempRst = Nothing
End Function


The emails which I would like to import contain an attachment. Does anyone
know how I can either save the attachement in Access as a memo or OLE
object? or even save a link to open the attachement?

I have tried adding !Attachment = Mailobject.Attachment but nothing is added
to the temporary table?

Thank you for your help.

Dean Spencer.

Gord
Guest
  
Posts: n/a
#2: Dec 11 '06

re: Outlook / Email

Dean Spencer wrote:
Quote:
Can anyone help? I am importing Emails from Outlook using the following
code:
>
Public Function ScanInbox(SubjectLine As String)
Dim TempRst As Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim InboxItems As Outlook.Items
Dim Mailobject As Object
Set OlApp = CreateObject("Outlook.Application")
Set Inbox = OlApp.GetNamespace("Mapi").GetDefaultFolder(olFold erInbox)
Set TempRst = CurrentDb.OpenRecordset("tbl_OutlookTemp")
If SubjectLine <"" Then
Set InboxItems = Inbox.Items.Restrict("[Subject] = """ & SubjectLine
& """")
Else
Set InboxItems = Inbox.Items
End If
For Each Mailobject In InboxItems
With TempRst
On Error Resume Next
.AddNew
!Subject = Mailobject.Subject
!From = Mailobject.SenderName
!To = Mailobject.To
!Body = Mailobject.Body
!DateSent = Mailobject.SentOn
!Attachment = Mailobject.Attachment
.Update
'Mailobject.Delete
Mailobject.Read
End With
Next
>
Set OlApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set Mailobject = Nothing
Set TempRst = Nothing
End Function
>
>
The emails which I would like to import contain an attachment. Does anyone
know how I can either save the attachement in Access as a memo or OLE
object? or even save a link to open the attachement?
>
I have tried adding !Attachment = Mailobject.Attachment but nothing is added
to the temporary table?
>
Thank you for your help.
>
Dean Spencer.
Hi, Dean.

Your question is really about the Outlook object model and not about
Access per se. That said, if you check the Object Browser you will see
that there is no "Attachment" property for the MailItem object.
However, there _is_ an "Attachments" collection through which you can
access the attachment(s) for the MailItem. (A single mail message can
have more than one attachment.)

So, your reference to the ".Attachment" property won't work. You can
either arbitrarily select the first attachment, something like this
(warning - air code):


Dim oMail As Outlook.MailItem, oAttach As Outlook.Attachment

' ...omitted code to set up object references, open recordset, etc.

If oMail.Attachments.Count 0 Then
Set oAttach = oMail.Attachments(0)
' do something useful
Set oAttach = Nothing
End If


or you could iterate through the Attachments collection, as in


Dim oMail As Outlook.MailItem, oAttach As Outlook.Attachment

' ...omitted code to set up object references, open recordset, etc.

For Each oAttach In oMail.Attachments
' do something useful
Next
Set oAttach = Nothing
Dean Spencer
Guest
  
Posts: n/a
#3: Dec 12 '06

re: Outlook / Email

Hi Gord,

there is always one attachment with the emails we are processing. The
attachement is a html report which i would either like to open or view from
Access.

Dean



"Gord" <gdt@kingston.netwrote in message
news:1165860242.511610.180500@73g2000cwn.googlegro ups.com...
Quote:
Dean Spencer wrote:
Quote:
>Can anyone help? I am importing Emails from Outlook using the following
>code:
>>
>Public Function ScanInbox(SubjectLine As String)
> Dim TempRst As Recordset
> Dim OlApp As Outlook.Application
> Dim Inbox As Outlook.MAPIFolder
> Dim InboxItems As Outlook.Items
> Dim Mailobject As Object
> Set OlApp = CreateObject("Outlook.Application")
> Set Inbox =
>OlApp.GetNamespace("Mapi").GetDefaultFolder(olFol derInbox)
> Set TempRst = CurrentDb.OpenRecordset("tbl_OutlookTemp")
> If SubjectLine <"" Then
> Set InboxItems = Inbox.Items.Restrict("[Subject] = """ &
>SubjectLine
>& """")
> Else
> Set InboxItems = Inbox.Items
> End If
> For Each Mailobject In InboxItems
> With TempRst
> On Error Resume Next
> .AddNew
> !Subject = Mailobject.Subject
> !From = Mailobject.SenderName
> !To = Mailobject.To
> !Body = Mailobject.Body
> !DateSent = Mailobject.SentOn
> !Attachment = Mailobject.Attachment
> .Update
> 'Mailobject.Delete
> Mailobject.Read
> End With
> Next
>>
> Set OlApp = Nothing
> Set Inbox = Nothing
> Set InboxItems = Nothing
> Set Mailobject = Nothing
> Set TempRst = Nothing
>End Function
>>
>>
>The emails which I would like to import contain an attachment. Does
>anyone
>know how I can either save the attachement in Access as a memo or OLE
>object? or even save a link to open the attachement?
>>
>I have tried adding !Attachment = Mailobject.Attachment but nothing is
>added
>to the temporary table?
>>
>Thank you for your help.
>>
>Dean Spencer.
>
Hi, Dean.
>
Your question is really about the Outlook object model and not about
Access per se. That said, if you check the Object Browser you will see
that there is no "Attachment" property for the MailItem object.
However, there _is_ an "Attachments" collection through which you can
access the attachment(s) for the MailItem. (A single mail message can
have more than one attachment.)
>
So, your reference to the ".Attachment" property won't work. You can
either arbitrarily select the first attachment, something like this
(warning - air code):
>
>
Dim oMail As Outlook.MailItem, oAttach As Outlook.Attachment
>
' ...omitted code to set up object references, open recordset, etc.
>
If oMail.Attachments.Count 0 Then
Set oAttach = oMail.Attachments(0)
' do something useful
Set oAttach = Nothing
End If
>
>
or you could iterate through the Attachments collection, as in
>
>
Dim oMail As Outlook.MailItem, oAttach As Outlook.Attachment
>
' ...omitted code to set up object references, open recordset, etc.
>
For Each oAttach In oMail.Attachments
' do something useful
Next
Set oAttach = Nothing
>

Gord
Guest
  
Posts: n/a
#4: Dec 12 '06

re: Outlook / Email


Dean Spencer wrote:
Quote:
Hi Gord,
>
there is always one attachment with the emails we are processing. The
attachement is a html report which i would either like to open or view from
Access.
>
Dean
The following will write the HTML attachment contents (text) to the
memo field called [Attachment]. To view it, you would write come code
to write it back to a temp file and then use FollowHyperlink to open
it.


Public Function ScanInbox(SubjectLine As String)

Const strTempFileSpec = "C:\__tmp\AttachTemp.txt"

Dim TempRst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim InboxItems As Outlook.Items
Dim OlMessage As Outlook.MailItem
Dim OlAttachment As Outlook.Attachment

Dim strmTemp As ADODB.Stream

Set OlApp = New Outlook.Application
Set Inbox =
OlApp.GetNamespace("Mapi").GetDefaultFolder(olFold erInbox)
Set TempRst = CurrentDb.OpenRecordset("tbl_OutlookTemp")

If SubjectLine <"" Then
Set InboxItems = Inbox.Items.Restrict("[Subject] = """ &
SubjectLine & """")
Else
Set InboxItems = Inbox.Items
End If

For Each OlMessage In InboxItems
With TempRst
.AddNew
!Subject = OlMessage.Subject
!From = OlMessage.SenderName
!To = OlMessage.To
!Body = OlMessage.Body
!DateSent = OlMessage.SentOn
If OlMessage.Attachments.Count 0 Then
' collection index starts at 1, not 0
Set OlAttachment = OlMessage.Attachments(1)
OlAttachment.SaveAsFile strTempFileSpec
Set OlAttachment = Nothing
Set strmTemp = New ADODB.Stream
' HTML attachment is text
strmTemp.Type = adTypeText
strmTemp.Charset = "us-ascii"
strmTemp.Open
strmTemp.LoadFromFile strTempFileSpec
strmTemp.Position = 0
' save to memo field in table
!Attachment = strmTemp.ReadText
strmTemp.Close
Set strmTemp = Nothing
Kill strTempFileSpec
End If
.Update
End With
Next

Set OlApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set OlMessage = Nothing
Set TempRst = Nothing

MsgBox "Done."
End Function



Quote:
"Gord" <gdt@kingston.netwrote in message
news:1165860242.511610.180500@73g2000cwn.googlegro ups.com...
Quote:
Dean Spencer wrote:
Quote:
Can anyone help? I am importing Emails from Outlook using the following
code:
>
Public Function ScanInbox(SubjectLine As String)
Dim TempRst As Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim InboxItems As Outlook.Items
Dim Mailobject As Object
Set OlApp = CreateObject("Outlook.Application")
Set Inbox =
OlApp.GetNamespace("Mapi").GetDefaultFolder(olFold erInbox)
Set TempRst = CurrentDb.OpenRecordset("tbl_OutlookTemp")
If SubjectLine <"" Then
Set InboxItems = Inbox.Items.Restrict("[Subject] = """ &
SubjectLine
& """")
Else
Set InboxItems = Inbox.Items
End If
For Each Mailobject In InboxItems
With TempRst
On Error Resume Next
.AddNew
!Subject = Mailobject.Subject
!From = Mailobject.SenderName
!To = Mailobject.To
!Body = Mailobject.Body
!DateSent = Mailobject.SentOn
!Attachment = Mailobject.Attachment
.Update
'Mailobject.Delete
Mailobject.Read
End With
Next
>
Set OlApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set Mailobject = Nothing
Set TempRst = Nothing
End Function
>
>
The emails which I would like to import contain an attachment. Does
anyone
know how I can either save the attachement in Access as a memo or OLE
object? or even save a link to open the attachement?
>
I have tried adding !Attachment = Mailobject.Attachment but nothing is
added
to the temporary table?
>
Thank you for your help.
>
Dean Spencer.
Hi, Dean.

Your question is really about the Outlook object model and not about
Access per se. That said, if you check the Object Browser you will see
that there is no "Attachment" property for the MailItem object.
However, there _is_ an "Attachments" collection through which you can
access the attachment(s) for the MailItem. (A single mail message can
have more than one attachment.)

So, your reference to the ".Attachment" property won't work. You can
either arbitrarily select the first attachment, something like this
(warning - air code):


Dim oMail As Outlook.MailItem, oAttach As Outlook.Attachment

' ...omitted code to set up object references, open recordset, etc.

If oMail.Attachments.Count 0 Then
Set oAttach = oMail.Attachments(0)
' do something useful
Set oAttach = Nothing
End If


or you could iterate through the Attachments collection, as in


Dim oMail As Outlook.MailItem, oAttach As Outlook.Attachment

' ...omitted code to set up object references, open recordset, etc.

For Each oAttach In oMail.Attachments
' do something useful
Next
Set oAttach = Nothing
Dean Spencer
Guest
  
Posts: n/a
#5: Dec 12 '06

re: Outlook / Email

Hi Gord,

I am getting a 'Object Type not defined' for the line 'Dim strmTemp As
ADODB.Stream'

Any ideas?

Dean


"Gord" <gdt@kingston.netwrote in message
news:1165929409.697870.257460@l12g2000cwl.googlegr oups.com...
Quote:
>
Dean Spencer wrote:
Quote:
>Hi Gord,
>>
>there is always one attachment with the emails we are processing. The
>attachement is a html report which i would either like to open or view
>from
>Access.
>>
>Dean
>
The following will write the HTML attachment contents (text) to the
memo field called [Attachment]. To view it, you would write come code
to write it back to a temp file and then use FollowHyperlink to open
it.
>
>
Public Function ScanInbox(SubjectLine As String)
>
Const strTempFileSpec = "C:\__tmp\AttachTemp.txt"
>
Dim TempRst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim InboxItems As Outlook.Items
Dim OlMessage As Outlook.MailItem
Dim OlAttachment As Outlook.Attachment
>
Dim strmTemp As ADODB.Stream
>
Set OlApp = New Outlook.Application
Set Inbox =
OlApp.GetNamespace("Mapi").GetDefaultFolder(olFold erInbox)
Set TempRst = CurrentDb.OpenRecordset("tbl_OutlookTemp")
>
If SubjectLine <"" Then
Set InboxItems = Inbox.Items.Restrict("[Subject] = """ &
SubjectLine & """")
Else
Set InboxItems = Inbox.Items
End If
>
For Each OlMessage In InboxItems
With TempRst
.AddNew
!Subject = OlMessage.Subject
!From = OlMessage.SenderName
!To = OlMessage.To
!Body = OlMessage.Body
!DateSent = OlMessage.SentOn
If OlMessage.Attachments.Count 0 Then
' collection index starts at 1, not 0
Set OlAttachment = OlMessage.Attachments(1)
OlAttachment.SaveAsFile strTempFileSpec
Set OlAttachment = Nothing
Set strmTemp = New ADODB.Stream
' HTML attachment is text
strmTemp.Type = adTypeText
strmTemp.Charset = "us-ascii"
strmTemp.Open
strmTemp.LoadFromFile strTempFileSpec
strmTemp.Position = 0
' save to memo field in table
!Attachment = strmTemp.ReadText
strmTemp.Close
Set strmTemp = Nothing
Kill strTempFileSpec
End If
.Update
End With
Next
>
Set OlApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set OlMessage = Nothing
Set TempRst = Nothing
>
MsgBox "Done."
End Function
>
>
>
>
Quote:
>"Gord" <gdt@kingston.netwrote in message
>news:1165860242.511610.180500@73g2000cwn.googlegr oups.com...
Quote:
Dean Spencer wrote:
>Can anyone help? I am importing Emails from Outlook using the
>following
>code:
>>
>Public Function ScanInbox(SubjectLine As String)
> Dim TempRst As Recordset
> Dim OlApp As Outlook.Application
> Dim Inbox As Outlook.MAPIFolder
> Dim InboxItems As Outlook.Items
> Dim Mailobject As Object
> Set OlApp = CreateObject("Outlook.Application")
> Set Inbox =
>OlApp.GetNamespace("Mapi").GetDefaultFolder(olFol derInbox)
> Set TempRst = CurrentDb.OpenRecordset("tbl_OutlookTemp")
> If SubjectLine <"" Then
> Set InboxItems = Inbox.Items.Restrict("[Subject] = """ &
>SubjectLine
>& """")
> Else
> Set InboxItems = Inbox.Items
> End If
> For Each Mailobject In InboxItems
> With TempRst
> On Error Resume Next
> .AddNew
> !Subject = Mailobject.Subject
> !From = Mailobject.SenderName
> !To = Mailobject.To
> !Body = Mailobject.Body
> !DateSent = Mailobject.SentOn
> !Attachment = Mailobject.Attachment
> .Update
> 'Mailobject.Delete
> Mailobject.Read
> End With
> Next
>>
> Set OlApp = Nothing
> Set Inbox = Nothing
> Set InboxItems = Nothing
> Set Mailobject = Nothing
> Set TempRst = Nothing
>End Function
>>
>>
>The emails which I would like to import contain an attachment. Does
>anyone
>know how I can either save the attachement in Access as a memo or OLE
>object? or even save a link to open the attachement?
>>
>I have tried adding !Attachment = Mailobject.Attachment but nothing is
>added
>to the temporary table?
>>
>Thank you for your help.
>>
>Dean Spencer.
>
Hi, Dean.
>
Your question is really about the Outlook object model and not about
Access per se. That said, if you check the Object Browser you will see
that there is no "Attachment" property for the MailItem object.
However, there _is_ an "Attachments" collection through which you can
access the attachment(s) for the MailItem. (A single mail message can
have more than one attachment.)
>
So, your reference to the ".Attachment" property won't work. You can
either arbitrarily select the first attachment, something like this
(warning - air code):
>
>
Dim oMail As Outlook.MailItem, oAttach As Outlook.Attachment
>
' ...omitted code to set up object references, open recordset, etc.
>
If oMail.Attachments.Count 0 Then
Set oAttach = oMail.Attachments(0)
' do something useful
Set oAttach = Nothing
End If
>
>
or you could iterate through the Attachments collection, as in
>
>
Dim oMail As Outlook.MailItem, oAttach As Outlook.Attachment
>
' ...omitted code to set up object references, open recordset, etc.
>
For Each oAttach In oMail.Attachments
' do something useful
Next
Set oAttach = Nothing
>
>

Gord
Guest
  
Posts: n/a
#6: Dec 12 '06

re: Outlook / Email


Dean Spencer wrote:
Quote:
Hi Gord,
>
I am getting a 'Object Type not defined' for the line 'Dim strmTemp As
ADODB.Stream'
>
Any ideas?
In the VBA editor choose

Tools References...

and make sure that your ADO reference says "Microsoft ActiveX Data
Objects 2.5 Library" (or some value >= 2.5)


Quote:
Dean
>
>
"Gord" <gdt@kingston.netwrote in message
news:1165929409.697870.257460@l12g2000cwl.googlegr oups.com...
Quote:

Dean Spencer wrote:
Quote:
Hi Gord,
>
there is always one attachment with the emails we are processing. The
attachement is a html report which i would either like to open or view
from
Access.
>
Dean
The following will write the HTML attachment contents (text) to the
memo field called [Attachment]. To view it, you would write come code
to write it back to a temp file and then use FollowHyperlink to open
it.
{snip}
Dean Spencer
Guest
  
Posts: n/a
#7: Dec 14 '06

re: Outlook / Email

Hi Gord,

The code you have given doesn't seem to work?

I am not getting any errors?

The code doesn't create a .txt file in the specified location, and nothing
is written to the Attachment field?

Are you able to help?

Dean


"Gord" <gdt@kingston.netwrote in message
news:1165942975.504180.264250@79g2000cws.googlegro ups.com...
Quote:
>
Dean Spencer wrote:
Quote:
>Hi Gord,
>>
>I am getting a 'Object Type not defined' for the line 'Dim strmTemp As
>ADODB.Stream'
>>
>Any ideas?
>
In the VBA editor choose
>
Tools References...
>
and make sure that your ADO reference says "Microsoft ActiveX Data
Objects 2.5 Library" (or some value >= 2.5)
>
>
>
Quote:
>Dean
>>
>>
>"Gord" <gdt@kingston.netwrote in message
>news:1165929409.697870.257460@l12g2000cwl.googleg roups.com...
Quote:
>
Dean Spencer wrote:
>Hi Gord,
>>
>there is always one attachment with the emails we are processing. The
>attachement is a html report which i would either like to open or view
>from
>Access.
>>
>Dean
>
The following will write the HTML attachment contents (text) to the
memo field called [Attachment]. To view it, you would write come code
to write it back to a temp file and then use FollowHyperlink to open
it.
>
>
{snip}
>

Gord
Guest
  
Posts: n/a
#8: Dec 14 '06

re: Outlook / Email


Dean Spencer wrote:
Quote:
Hi Gord,
>
The code you have given doesn't seem to work?
It works for me....
Quote:
I am not getting any errors?
I noticed that your original code had

On Error Resume Next

at the beginning of the loop. Is it possible that your code is throwing
an error and you are suppressing it?
Quote:
The code doesn't create a .txt file in the specified location, and nothing
is written to the Attachment field?
>
Are you able to help?
I don't have much to go on. If the temp file is not being created then
it appears that

OlAttachment.SaveAsFile strTempFileSpec

is failing. If it is, then perhaps it is a difference in behaviour
between different versions of Outlook. (I used Outlook_2000 for
testing.) If you set a breakpoint and step through the code can you
verify that OlAttachment.SaveAsFile is not doing its job?

Quote:
>
Dean
>
>
"Gord" <gdt@kingston.netwrote in message
news:1165942975.504180.264250@79g2000cws.googlegro ups.com...
Quote:

Dean Spencer wrote:
Quote:
Hi Gord,
>
I am getting a 'Object Type not defined' for the line 'Dim strmTemp As
ADODB.Stream'
>
Any ideas?
In the VBA editor choose

Tools References...

and make sure that your ADO reference says "Microsoft ActiveX Data
Objects 2.5 Library" (or some value >= 2.5)


Quote:
Dean
>
>
"Gord" <gdt@kingston.netwrote in message
news:1165929409.697870.257460@l12g2000cwl.googlegr oups.com...

Dean Spencer wrote:
Hi Gord,
>
there is always one attachment with the emails we are processing. The
attachement is a html report which i would either like to open or view
from
Access.
>
Dean

The following will write the HTML attachment contents (text) to the
memo field called [Attachment]. To view it, you would write come code
to write it back to a temp file and then use FollowHyperlink to open
it.
{snip}
Closed Thread

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,590 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved