Consulting

Results 1 to 17 of 17

Thread: To read Outlook Mailbox

  1. #1

    To read Outlook Mailbox

    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?
    Attached Files Attached Files

  2. #2
    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
    Attached Files Attached Files
    Last edited by Aussiebear; 08-11-2023 at 12:32 PM. Reason: Added the code for others to see

  3. #3
    VBAX Tutor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    270
    Location
    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.

    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

  5. #5
    VBAX Tutor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    270
    Location
    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.
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Totally understand and will follow

  7. #7
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,855
    Location
    Quote Originally Posted by rolling_zep View Post
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  8. #8
    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
    Last edited by rolling_zep; 08-11-2023 at 01:07 PM.

  9. #9
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,855
    Location
    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  10. #10
    VBAX Tutor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    270
    Location
    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.
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

  12. #12
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,855
    Location
    I don't know Outlook vba, so all Im following is the example that Microsoft offered.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  13. #13
    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")
    
    

  14. #14
    Not much information on your reply to that post

  15. #15
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    70
    Location
    Quote Originally Posted by rolling_zep View Post
    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

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

  17. #17
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    70
    Location
    I did say that syntax for the folder did not look right to me in the other forum
    Glad you got it sorted.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •