To read Outlook Mailbox

[rolling_zep]

I had sought help with Mr Excel in this thread link, Run Outlook rule | MrExcel Message Board
They asked me to seek help here.


We manually run a rule everyday, which executes a script in Outlook. This reads attachments in the inbox from folder, WeeklyProceedings Mailbox and saves the attachments to a folder in the shared directory, S.
The Access database imports these Outlook attachments files into a table.
Since this job needs to be done before all users log in, it is done manually by a User at 8.30 am everyday.


I am trying to automate and run this as a Scheduled task without manual intervention.
How do I convert the Outlook script to Access VBA script so that it can be executed either by a macro or an event?

[rolling_zep]

I modified my code but it is still not reading the mails or the attachments.
The count is still zero



Private Sub cmdOutlook_Click()
Dim olApp As Object
Set olApp = CreateObject("Outlook.Application")
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olMi As Object
Set olMi = olApp.CreateItem(0)
Dim olAtt As Outlook.Attachments
Dim strFile As String
Dim objSubject As String
Dim strFolderpath As String
Dim i As Long
Dim objDestfolder As Outlook.Folder

Set olNs = olApp.GetNamespace("MAPI")
'strFolderpath = "S:\beData\prof_data"
strFolderpath = "C:\Users\prasad.govindarajan\Testing"
objSubject = olMi.Subject
sreplace = "_"
'create an array to loop through and replace any potential illegal characters
For Each mychar In Array("/", "\", "^", "*", "%", "$", "#", "@", "~", "`", "{", "}", "[", "]", "|", ";", ":", ",", ".", "'", "+", "=", "?", "!", " ", Chr(34), "<", ">", "¦")
objSubject = Replace(objSubject, mychar, sreplace)
Next mychar
' Set the Attachment folder.
strFolderpath = strFolderpath & "\Attachments\"
Set objDestfolder = olNs.Folders.item("WeeklyProceedings Mailbox").Folders.item("Folders").Folders.item("Archive_Proc")

Set olAtt = olMi.Attachments
lngCount = olAtt.Count
If lngCount > 0 Then
For i = lngCount To 1 Step -1
strFile = objSubject & ".XML"

' Combine with the path to the Temp folder.
strFile = strFolderpath & strFile

' Save the attachment as a file.
olAtt.item(i).SaveAsFile strFile
Next i

olMi.Body = olMi.Body & vbCrLf & "The file was processed " & Now()

olMi.Subject = "Processed - " & objSubject
olMi.Save

End If
'olMi.Move objDestfolder
'Next

Set olAtt = Nothing
Set olMi = Nothing

[June7]

Should paste that code into your post between CODE tags. Use the # icon from post edit toolbar. Can edit your post.

Duplicate thread http://www.vbaexpress.com/forum/showthread.php?71025-Access-to-read-Outlook-script

Should not post same question in multiple threads.

[rolling_zep]

I apologize for the duplicate posting since I am new to this forum and was under the impression that each forum was different.

[June7]

They each have different focus. You should pick one that your question primarily concerns and post in there. This is same as cross-posting questions in multiple sites. People spend time on a thread for a question that might already have been answered in another thread or offering ideas that already proved to fail. If you do cross-post, inform your readers and provide links to each of the other thread.

[rolling_zep]

Totally understand and will follow

[Aussiebear]

I apologize for the duplicate posting since I am new to this forum and was under the impression that each forum was different.
Welcome to VBAX rolling_zep. You are right in some ways, all the forums are indeed slightly different and at first somewhat mystifying to the new people. But hey, you have to start somewhere. So here's a couple of things we very much prefer.

Any question relating to the Office suite. Whilst we are predominately VBA orientated we still try to answer object questions as well; and
We prefer workbooks with examples whenever possible when posting questions; and
Code scripts rather than images; and
When the issue is posted elsewhere, that a link is provided, so we can see what help if any has already been provided; and
A little understanding that at times maybe even our members may not be able to assist.

From what I have seen so far you will be a wonderful addition to our membership. Reach out if you have any queries and we'll see what we can do to assist.

[rolling_zep]

Thank you and if you can see my VBA question in my original post, I need a solution.

I since modified my entire code but the count is zero, meaning it is not reading the mailbox and attachments.

Private Sub cmdOutlook_Click()
Dim OlItems As Outlook.Items
Dim olItem As Outlook.MailItem
Dim olFolder As Outlook.MAPIFolder
Dim X, y As Long
Dim strFileName As String
Dim strFile As String




Set olFolder = Outlook.GetNamespace("MAPI").Folders("WeeklyProceedings Mailbox").Folders("Inbox")



'providing the object of the mails in the inbox
Set OlItems = olFolder.Items
For Each olItem In OlItems

X = olItem.Attachments.Count
If X > 0 Then
For y = 1 To X
'getting the attachment name
strFileName = olItem.Attachments.item(y).FileName
'saving the attachment in a temporary location
olItem.Attachments.item(y).SaveAsFile "C:\Users\prasad.govindarajan\Testing\" & olItem.Attachments.item(y).FileName
Next y
End If

olItem.Save
Next


This is my mail setup. I need to read the mails in the Inbox in WeeklyProceedings Mailbox.
I suspect this line of code
Set olFolder = Outlook.GetNamespace("MAPI").Folders("WeeklyProceedings Mailbox").Folders("Inbox"). Probably it is not the correct way

30978

[Aussiebear]

Microsoft uses this example


Set myNameSpace = Application.GetNameSpace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderContacts)
Set myNewFolder = myFolder.Folders("Old Contacts")

Perhaps if you were to try this approach?

[June7]

Another example using Namespace
https://www.accessforums.net/showthread.php?t=38275

I seem to remember helping someone in another thread and discovering that images embedded in email, not just those that might be Attachments, are also downloaded.

[rolling_zep]

so myFolder.Folders("Old Contacts") is equals to
myFolder.Folders("WeeklyProceedings Mailbox")?

[Aussiebear]

I don't know Outlook vba, so all Im following is the example that Microsoft offered.

[rolling_zep]

I did try but it gives an error at the line bolded as, "An Object could not be found"



Set myNameSpace = Application.GetNameSpace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
Set olFolder = MYFolder.Folders("Misc")

[rolling_zep]

Not much information on your reply to that post :(

[Gasman]

I did try but it gives an error at the line bolded as, "An Object could not be found"



Set myNameSpace = Application.GetNameSpace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
Set olFolder = MYFolder.Folders("Misc")



You have to supply your own folder names? :(
That is just an example of the syntax.

https://www.youtube.com/results?search_query=read+outlook+folder+access+vba

[rolling_zep]

I was able to resolve the issue (thanks to the experts here for the tips!).
This one runs from a Command button click event in Access VBA.
It reads the emails from the Inbox in WeeklyProceedings Mailbox and saves the XML attachments in C:\Users\Testing



Dim olApp As Object
Dim MYFOLDER As Object
Dim OlItems As Object
Dim OlMail As Object
Dim x As Integer
Dim strFile As String
Dim strFolderpath As String


Set olApp = GetObject(, "Outlook.Application")
If Err.Number = 429 Then
Set olApp = CreateObject("Outlook.Application")
End If


strFolderpath = "C:\Users\Testing"
'On Error Resume Next


' Set the Attachment folder.
strFolderpath = strFolderpath & "\Attachments\"


Set MYFOLDER = olApp.GetNamespace("MAPI").Folders("WeeklyProceedings Mailbox").Folders("Inbox")
Set OlItems = MYFOLDER.Items


For Each OlMail In OlItems
strFile = OlMail & ".XML"
strFile = strFolderpath & strFile
If OlMail.Attachments.Count > 0 Then
For x = 1 To OlMail.Attachments.Count
OlMail.Attachments.item(x).SaveAsFile strFile
Next x
End If
Next

Set MYFOLDER = Nothing
Set OlMail = Nothing
Set OlItems = Nothing
Set olApp = Nothing

[Gasman]

I did say that syntax for the folder did not look right to me in the other forum :)
Glad you got it sorted.