none
Outlook VBA batch print attachments from selected emails RRS feed

  • Question

  • Hello,

    About a week ago, I found a macro to batch print attachments, from the emails which I would select.

    It works flawlessly and prints out all of the attachments within the selected emails.

    However I have recognized a problem with the macro afterall, which is that the macro doesn't print the emails in any sort of order.

    Is there by any chance, a way to make the macro print the emails from the top, and work it's way down through the folder?

    At the time of writing, the macro will print in an unwanted order.

    To clarify what I mean, I want it to print out with the most recent email being the 1st, and the 2nd most recent email being the 2nd printed.

    Right now the order is somewhat messed up, and it would probably print in an order like "1, 2, 4, 3, 5, 7, 6, 8", whereas I would like it to print in a chronological order "1, 2, 3, 4, 5, 6, 7, 8".

    Is this possible to adjust the macro, so it will print the attachments in order? :)

    My macro is as listed below:

    Sub BatchPrintAllAttachmentsinMultipleEmails()
        Dim objFileSystem As Object
        Dim strTempFolder As String
        Dim objSelection As Outlook.Selection
        Dim objItem As Object
        Dim objMail As Outlook.MailItem
        Dim objAttachments As Outlook.Attachments
        Dim objAttachment As Outlook.Attachment
        Dim objShell As Object
        Dim objTempFolder As Object
        Dim objTempFolderItem As Object
        Dim strFilePath As String
     
        Set objFileSystem = CreateObject("Scripting.FileSystemObject")
        strTempFolder = objFileSystem.GetSpecialFolder(2).Path & "\Temp for Attachments " & Format(Now, "YYYY-MM-DD_hh-mm-ss")
        'Create a new temp folder
        MkDir (strTempFolder)
     
        Set objSelection = Outlook.Application.ActiveExplorer.Selection
     
        For Each objItem In objSelection
            If TypeOf objItem Is MailItem Then
               Set objMail = objItem
               Set objAttachments = objMail.Attachments
     
               'Save all the attachments in the temp folder
               For Each objAttachment In objAttachments
                   strFilePath = strTempFolder & "\" & objAttachment.FileName
                   objAttachment.SaveAsFile (strFilePath)
     
                   'Print all the files in the temp folder
                   Set objShell = CreateObject("Shell.Application")
                   Set objTempFolder = objShell.NameSpace(0)
                   Set objTempFolderItem = objTempFolder.ParseName(strFilePath)
                   objTempFolderItem.InvokeVerbEx ("print")
               Next objAttachment
            End If
        Next
    End Sub

    Thursday, October 12, 2017 10:20 PM

Answers

  • Is there any other verbs, which might do the trick instead of "shell"?? A verb that might need to know which files to print, but will be able top start from the top/bottom and work its way down/up?

    Let me know if you have another verb, which could somewhat replace "shell".

    There is no verb used by the shell that will handle printing as you desire.  I suspect that anything capable enough to meet your requirement for printing diverse file types in specified orders will require a programming effort that is beyond what is available in VBA, as well as beyond the scope of assistance ordinarily provided in these forums.

    • Marked as answer by PatrickS15 Wednesday, October 25, 2017 12:21 PM
    Wednesday, October 18, 2017 2:29 PM

All replies

  • Hi PatrickS15,

    Outlook Selection Object does not provide any Property/Method for sorting.

    I think you have to export selected mails to an array and then sort the array. After sorting, you could iterate through the array in order.

    Here is the example.Sub BatchPrintAllAttachmentsinMultipleEmails()

        Dim objFileSystem As Object
    
        Dim strTempFolder As String
    
        Dim objSelection As Outlook.Selection
    
        Dim objItem As Object
    
        Dim objMail As Outlook.MailItem
    
        Dim objAttachments As Outlook.Attachments
    
        Dim objAttachment As Outlook.Attachment
    
        Dim objShell As Object
    
        Dim objTempFolder As Object
    
        Dim objTempFolderItem As Object
    
        Dim strFilePath As String
    
        Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    
        strTempFolder = objFileSystem.GetSpecialFolder(2).Path & "\Temp for Attachments " & Format(Now, "YYYY-MM-DD_hh-mm-ss")
    
        'Create a new temp folder
    
        MkDir (strTempFolder)
    
        Set objSelection = Outlook.Application.ActiveExplorer.Selection
    
        Dim arr() As MailItem
    
        Count = 0
    
        'put mail with attachments into an array
    
         For Each objItem In objSelection
    
            If TypeOf objItem Is MailItem Then
    
               If objItem.Attachments.Count > o Then
    
                        Set objMail = objItem
    
                         Count = Count + 1
    
                        ReDim Preserve arr(1 To Count)
    
                       Set arr(Count) = objMail
    
                End If
    
            End If
    
        Next objItem
    
        'sort the array
    
        For i = 1 To Count - 1
    
            For j = i + 1 To Count
    
                If arr(i).ReceivedTime < arr(j).ReceivedTime Then
    
                  Set objMail = arr(i)
    
                  Set arr(i) = arr(j)
    
                  Set arr(j) = objMail
    
                End If
    
            Next j
    
      Next i
    
       
    
        For i = 1 To Count
    
        Set objAttachments = arr(i).Attachments
    
                For Each objAttachment In objAttachments
    
                    strFilePath = strTempFolder & "\" & objAttachment.FileName
    
                   objAttachment.SaveAsFile (strFilePath)
    
                   'Print all the files in the temp folder
    
                   Set objShell = CreateObject("Shell.Application")
    
                   Set objTempFolder = objShell.NameSpace(0)
    
                   Set objTempFolderItem = objTempFolder.ParseName(strFilePath)
    
                   objTempFolderItem.InvokeVerbEx ("print")
    
               Next objAttachment
    
        Next i

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 13, 2017 9:52 AM
  • Hi PatrickS15,

    Outlook Selection Object does not provide any Property/Method for sorting.

    I think you have to export selected mails to an array and then sort the array. After sorting, you could iterate through the array in order.

    Here is the example.Sub BatchPrintAllAttachmentsinMultipleEmails()

        Dim objFileSystem As Object
    
        Dim strTempFolder As String
    
        Dim objSelection As Outlook.Selection
    
        Dim objItem As Object
    
        Dim objMail As Outlook.MailItem
    
        Dim objAttachments As Outlook.Attachments
    
        Dim objAttachment As Outlook.Attachment
    
        Dim objShell As Object
    
        Dim objTempFolder As Object
    
        Dim objTempFolderItem As Object
    
        Dim strFilePath As String
    
        Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    
        strTempFolder = objFileSystem.GetSpecialFolder(2).Path & "\Temp for Attachments " & Format(Now, "YYYY-MM-DD_hh-mm-ss")
    
        'Create a new temp folder
    
        MkDir (strTempFolder)
    
        Set objSelection = Outlook.Application.ActiveExplorer.Selection
    
        Dim arr() As MailItem
    
        Count = 0
    
        'put mail with attachments into an array
    
         For Each objItem In objSelection
    
            If TypeOf objItem Is MailItem Then
    
               If objItem.Attachments.Count > o Then
    
                        Set objMail = objItem
    
                         Count = Count + 1
    
                        ReDim Preserve arr(1 To Count)
    
                       Set arr(Count) = objMail
    
                End If
    
            End If
    
        Next objItem
    
        'sort the array
    
        For i = 1 To Count - 1
    
            For j = i + 1 To Count
    
                If arr(i).ReceivedTime < arr(j).ReceivedTime Then
    
                  Set objMail = arr(i)
    
                  Set arr(i) = arr(j)
    
                  Set arr(j) = objMail
    
                End If
    
            Next j
    
      Next i
    
       
    
        For i = 1 To Count
    
        Set objAttachments = arr(i).Attachments
    
                For Each objAttachment In objAttachments
    
                    strFilePath = strTempFolder & "\" & objAttachment.FileName
    
                   objAttachment.SaveAsFile (strFilePath)
    
                   'Print all the files in the temp folder
    
                   Set objShell = CreateObject("Shell.Application")
    
                   Set objTempFolder = objShell.NameSpace(0)
    
                   Set objTempFolderItem = objTempFolder.ParseName(strFilePath)
    
                   objTempFolderItem.InvokeVerbEx ("print")
    
               Next objAttachment
    
        Next i

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi Terry,

    First of all I'm very greatful, for your answer on this post!

    I have tried using your example for a couple of times, but it haven't solved the issue.
    Actually the example that you have posted in the above, is more inconsistant in sorting the emails, than the one i posted. When I tried it twice in a row, on the same selection, it would swap the files around giving two different results.

    As you say, Outlook does not provide a sort on "Selection". And therefore you want Outlook to make an array of the emails, sorting in the fictionary coloumn "Received"?? At least, that is how I understand it from reading your example.

    Does your example work for you?

    I don't know if Outlook does make the table, which you have in your example, but is it possible, that the print function will look past the table and just batch print as my macro does?
    Is it possible to implement the sorting coding into the print option?
    Forcing the VBA to print in order of the time of receiving the email?

    Do let me know what you think, and if it works well for you :)

    Best regards,
    Patrick

    Monday, October 16, 2017 9:46 AM
  • You may find this informative in understanding the order of printing since the macro invokes the shell verb to do printing -

    Why are documents printed out of order when you multiselect and choose “Print”?

    Monday, October 16, 2017 9:56 AM
  • You may find this informative in understanding the order of printing since the macro invokes the shell verb to do printing -

    Why are documents printed out of order when you multiselect and choose “Print”?

    Thank you, it makes sense what the post saying.
    This might help me search for a solution onwards...

    I don't know how familiar you are with the post, but if so the "shell" verb is what is causing the problem.
    Is there by any chance, any other verb that I can use, so the macro will send it towards the printer in order?

    Best regards,
    Patrick

    Tuesday, October 17, 2017 6:33 AM
  • You may find this informative in understanding the order of printing since the macro invokes the shell verb to do printing -

    Why are documents printed out of order when you multiselect and choose “Print”?

    Thank you, it makes sense what the post saying.
    This might help me search for a solution onwards...

    I don't know how familiar you are with the post, but if so the "shell" verb is what is causing the problem.
    Is there by any chance, any other verb that I can use, so the macro will send it towards the printer in order?

    The advantage of using the shell "print" verb is that the macro does not need to have any specific knowledge about the type of file to be printed or the program that must be used to do the printing.

    One possibility might be to introduce a delay in the loop that invokes the "print" verb so that the shell has more time to invoke the associated program and that program has more time get the print job going.  You would have to determine the delay by trial and error, and there are no guarantees.  For example, a delay that is sufficient to allow a small document to be printed before the loop cycles might not be adequate to handle the time required for a very large document.  And the downside would be that it would take longer for all the printing to complete.

    Tuesday, October 17, 2017 10:07 AM
  • You may find this informative in understanding the order of printing since the macro invokes the shell verb to do printing -

    Why are documents printed out of order when you multiselect and choose “Print”?

    Thank you, it makes sense what the post saying.
    This might help me search for a solution onwards...

    I don't know how familiar you are with the post, but if so the "shell" verb is what is causing the problem.
    Is there by any chance, any other verb that I can use, so the macro will send it towards the printer in order?

    The advantage of using the shell "print" verb is that the macro does not need to have any specific knowledge about the type of file to be printed or the program that must be used to do the printing.

    One possibility might be to introduce a delay in the loop that invokes the "print" verb so that the shell has more time to invoke the associated program and that program has more time get the print job going.  You would have to determine the delay by trial and error, and there are no guarantees.  For example, a delay that is sufficient to allow a small document to be printed before the loop cycles might not be adequate to handle the time required for a very large document.  And the downside would be that it would take longer for all the printing to complete.

    Yeah, it's a shame that shell, doesn't allow printing in order... At this point I have only the solution of printing one email at a time with the macro, which is still more efficient, than opening ever mail to open the PDF file and then printing it.

    I can't figure out how I should addapt the macro to be able to print out in order given by time and date...

    Is there any other verbs, which might do the trick instead of "shell"?? A verb that might need to know which files to print, but will be able top start from the top/bottom and work its way down/up?

    Let me know if you have another verb, which could somewhat replace "shell".

    Wednesday, October 18, 2017 1:46 PM
  • Is there any other verbs, which might do the trick instead of "shell"?? A verb that might need to know which files to print, but will be able top start from the top/bottom and work its way down/up?

    Let me know if you have another verb, which could somewhat replace "shell".

    There is no verb used by the shell that will handle printing as you desire.  I suspect that anything capable enough to meet your requirement for printing diverse file types in specified orders will require a programming effort that is beyond what is available in VBA, as well as beyond the scope of assistance ordinarily provided in these forums.

    • Marked as answer by PatrickS15 Wednesday, October 25, 2017 12:21 PM
    Wednesday, October 18, 2017 2:29 PM
  • Hi Patrick,

    Forgive me if I'm overlooking something here, however I've just finished attempting something similar, albeit that the printing is completed manually via selecting all files within the folder to which they have been saved, right-click, print.

    Essentiallly what I did was to save each attachment to a concatenated Path & Format(objMail.ReceivedTime, "YYYYMMDDHHMMSS") & objMail.Sender & objAttachment.FileName

    Admittedly the next step entails opening the folder to which the attachments have been saved, clicking on the "Name" title in the header to sort, and then right-click print.  Again - final step is manual, and I am not presently in the office to test the output, however I would invisage that it would subsequently print in chronological order.

    Realise that the post it old, but hope that has helped.

    Kind regards,

    Ben

    Wednesday, November 21, 2018 10:03 AM