By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Outlook / Email

P: n/a
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.
Dec 11 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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(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

Dec 11 '06 #2

P: n/a
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" <gd*@kingston.netwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...
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

Dec 12 '06 #3

P: n/a

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.
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

"Gord" <gd*@kingston.netwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...
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(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
Dec 12 '06 #4

P: n/a
Hi Gord,

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

Any ideas?

Dean
"Gord" <gd*@kingston.netwrote in message
news:11**********************@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.
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

>"Gord" <gd*@kingston.netwrote in message
news:11**********************@73g2000cwn.googlegr oups.com...
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

Dec 12 '06 #5

P: n/a

Dean Spencer wrote:
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)
Dean
"Gord" <gd*@kingston.netwrote in message
news:11**********************@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}

Dec 12 '06 #6

P: n/a
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" <gd*@kingston.netwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
>
Dean Spencer wrote:
>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)
>Dean
"Gord" <gd*@kingston.netwrote in message
news:11**********************@l12g2000cwl.googleg roups.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}

Dec 14 '06 #7

P: n/a

Dean Spencer wrote:
Hi Gord,

The code you have given doesn't seem to work?
It works for me....
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?
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?

>
Dean
"Gord" <gd*@kingston.netwrote in message
news:11**********************@79g2000cws.googlegro ups.com...

Dean Spencer wrote:
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)
Dean
"Gord" <gd*@kingston.netwrote in message
news:11**********************@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}
Dec 14 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.