Modify File before saving it.

[josros60]

Hi,

found very helpful code to print multiples attachments in outlook, just wonder if can prompt to when open file to fix like columns showing ##### because is narrow after modifying saving it.

here is the code ( and after will put a sample that you can see that date cannot see it because columns no wide enough is excel file converted to pdf)

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

Sample:

 

[Domenic]

In your code, try adding the following lines after the attachment is saved to automatically open the attachment, resize the date columns, close and save the changes . . .

    Dim wb As Workbook
    Set wb = Workbooks.Open(strFilePath)
 
    wb.Worksheets(1).Columns("A:B").AutoFit
 
    wb.Close SaveChanges:=True

Note that the first sheet is referenced, along with Columns A and B for the start and end dates. Change these references accordingly.

Also, I would suggest that you set Application.ScreenUpdating to False at the beginning of your code so that you won't see the workbooks being opened and closed, and to make it more efficient. Then set it back to True and the end of your code.

Hope this helps!

 

[josros60]

Hi,



I did, see below where I put it per your instructions, (one thing though the file come as csv format and save it as pdf)



here part of the code, as I am not that good in VBA no sure if put it in wrong place:

[B][COLOR=rgb(226, 80, 65)]Dim wb As Workbook[/COLOR][/B]
 
    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)
             
               
   [B][COLOR=rgb(226, 80, 65)] Set wb = Workbooks.Open(strFilePath)[/COLOR][/B]
[COLOR=rgb(226, 80, 65)][B] 
    wb.Worksheets(1).Columns("A:B").AutoFit
 
    wb.Close SaveChanges:=True[/B][/COLOR]
[B][COLOR=rgb(226, 80, 65)] [/COLOR][/B]
               '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

thank you

 

[Domenic]

It looks fine, have you tried it?

 

[josros60]

Yes,

But just save to pdf but won't open the file again.

Thank you

 

[Domenic]

When I tested your code, I was able to open the PDF file using my Chrome browser without any problems. And, while I wasn't able to test it using Adobe Reader or Acrobat, it should be fine as well.

Are you using some other PDF viewer?

 

[josros60]

this won't work for because:

1. the file being open and save is in csv format what I want to know if possible once file open to pause to allow me to modify the file and save it after.



thank you

 

[Domenic]

What did you use to open the PDF file?

Also, I'm a little bit confused. Have you changed your requirement?

 

[josros60]

Chrome browser.

thank you

 

[Domenic]

In that case, I wonder if it's a timing issue. Try pausing the macro for a few seconds each time after printing. So, for example, you can call the following function in order to pause the macro for the desired number of seconds . . .

Sub PauseMacro(ByVal secs As Long)

    Dim endTime As Single
    endTime = Timer + secs
    
    Do
        DoEvents
    Loop Until Timer > endTime
    
End Sub

So you can call the PauseMacro function by adding the following line after the line that prints to PDF . . .

PauseMacro 3 'seconds

Does this help?