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

MailBox.jpg

[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?sear...der+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.