Attach all files in a folder to email in Word VBA

  • Hi All


    I have this code in a word doc:



    It was partly written for excel but I have moved to word as it is required.


    The next question I have is how can I have the VBA code attach all the files that are present in the "fold" folder in the code (which is inactive at the moment). There could be multiple files of different types and names in this folder.


    Also the inactive parts reguarding the ans = MsgBox etc this in excel gave the user the option to select multiple extra files to attach, however the "Application.GetOpenFilename" does not work in Word, would someone be able to rewrite this for word?


    Thanks in advance.

  • Re: Attach all files in a folder to email in Word VBA


    Example of a File Open dialogue in Word...


    OK, so it needs a slight rewrite to fit into your code...

  • Re: Attach all files in a folder to email in Word VBA


    Thanks cytop




    I have tried working the word open dialog box code into my code but I haven't managed to get a working version, I think its cause I dont know the order/type of arguments to mimic this: ("Files (*.**)," & _ ' "*.**", 1, "Select File", "Open", True)


    In word vba?


    Any ideas?


     

    Any ideas on attaching all files within a folder? This is the main question since the adding extra attachments probably wont be used much.


    Cheers

  • Re: Attach all files in a folder to email in Word VBA


    Code
    [COLOR=#006400]'fold = "C:\Users\tbaker\Documents\Jobs\" & job[/COLOR]

    Its inactive at the moment cause I don't know how to do it. Basically some previous code puts a bunch of files into this created "job folder", all those files in that folder needs to be attached to the email also? Hope this helps sorry if I wasn't clear before.

  • Re: Attach all files in a folder to email in Word VBA


    So the folder will always exist when this procedure runs and it can be determined ...?


    Hard coding a folder name is not a good thing, so it needs to be something that can be determined in code. For example, it is always the JOBS folder in the users 'My documents' directory?

  • Re: Attach all files in a folder to email in Word VBA


    The previous code makes a new "job folder" using the users imputed value in the My Docs\Jobs\ directory every time the procedure starts. The files are then all saved to this new folder which path will be this code: "C:\Users\tbaker\Documents\Jobs\" & job (job being the variable the user inputs). So yes as long as the code looks in the folder using that path code then it will be fine. The main reason why there is the "job = InputBox("Enter Job Number")" code in this code, is because the first procedure is run in Outlook which saves all of an emails attachments into a new folder created as the above, but I don't think I can keep the "job" variable set from Outlook for use in Word, so the user has to put it in twice.

  • Re: Attach all files in a folder to email in Word VBA


    Hi All


    Just so you know I have figured out the answer to my main question of attaching all files in a folding by putting this code in:


    Code
    Dim MyObj As Object, MySource As Object, file As Variant
    file = Dir("C:\Users\tbaker\Documents\Jobs\" & job & "\")
    
    
    While (file <> "")
        objMailItem.Attachments.Add "C:\Users\tbaker\Documents\Jobs\" & job & "\" & file
        file = Dir
    Wend


    This works perfectly, goes through the folder and attaches all the files.



    The only thing left to do now is fix these parts to work in Word:


    Code
    ans = MsgBox("Do you need to add any more attachments?", vbYesNo)
         If ans = vbYes Then
             AttachFileName = Application.GetOpenFilename("Files (*.**)," & _
              "*.**", 1, "Select File", "Open", True)


    Code
    For a = LBound(AttachFileName) To UBound(AttachFileName)
             objMailItem.Attachments.Add AttachFileName(a)
          Next


    Thanks

  • Re: Attach all files in a folder to email in Word VBA


    Slightly different approach as regards the dialogue used...

  • Re: Attach all files in a folder to email in Word VBA


    If I read that right, the above code will make "dlgOpen" equal to an array of file paths selected by the dialog box?

  • Re: Attach all files in a folder to email in Word VBA


    No. dlgOpen is defined as a dialog...

    Code
    Dim dlgOpen As FileDialog


    It has a SELECTEDITEMS property which contains the list of files selected. The SelectedItems property also has a Count sub-property, as used above...

  • Re: Attach all files in a folder to email in Word VBA


    Ah so I just replace it to this code:


    Code
    objMailItem.Attachments.Add .SelectedItems(iTemp)


    And it should work perfectly in my current code.


    Thank will give that a try now.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!