Consulting

Results 1 to 11 of 11

Thread: Reload this Page Attaching all PDFs in folder to Outlook email using Word VBA

  1. #1

    Reload this Page Attaching all PDFs in folder to Outlook email using Word VBA

    Hi


    I want to attach all PDFs in a specific desktop folder to an Outlook email using VBA in Word


    I'm using below code to generate an Outlook email but cant figure out how to write the attachment part.



    Sub Email_Template1()


    Dim oMailItem As Object, oOLapp As Object

    Dim Word As Object, doc As Object, MsgTxt$

    Set oOLapp = CreateObject("Outlook.Application")

    Set oMailItem = oOLapp.CreateItem(0)

    Set Word = CreateObject("word.application")


    With oMailItem

    .To = "Email"

    .cc = "Email"

    .subject = "Needed Confirmation"

    ' .attachment.Add

    .HTMLBody = "test"

    .Display

    End With


    Set oOLapp = Nothing

    Set oMailItem = Nothing


    End Sub

  2. #2
    VBAX Contributor rollis13's Avatar
    Joined
    Jun 2013
    Location
    Cordenons
    Posts
    146
    Location
    ​Adding link for Cross-posting LINK​

    ---
    Have a try with your revised macro:
    Option Explicit
    
    Sub Email_Template1()
    
    
        Dim oMailItem As Object, oOLapp As Object
        Dim Word   As Object, doc As Object, MsgTxt$
        Dim filePath As String                        '<=
        Dim fileName As String                        '<=
    
    
        Set oOLapp = CreateObject("Outlook.Application")
        Set oMailItem = oOLapp.CreateItem(0)
        Set Word = CreateObject("word.application")
        With oMailItem
            .To = "Email"
            .Cc = "Email"
            .Subject = "Needed Confirmation"
            '---------------------------------------
            filePath = "F:\Test\Pdf\"               '<= change as needed
            fileName = Dir(filePath & "*.pdf")        '<=
            Do While Len(fileName) > 0                '<=
                .Attachments.Add filePath & fileName  '<=
                fileName = Dir                        '<=
            Loop                                      '<=
            '---------------------------------------
            .HTMLBody = "test"
            .Display
        End With
        Set oOLapp = Nothing
        Set oMailItem = Nothing
        
    End Sub
    Last edited by rollis13; 01-24-2021 at 05:02 PM.

  3. #3
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    handclips: Kindly read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq...._new_faq_item3
    I note this is not the first time you've ignored the cross-posting rules you agreed to abide by when joining a forum...
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Apologies for the trouble, i did not notice this until now. Will take note going forward.

  6. #6
    Hello there

    It will be highly appreciated if someone could help me modify this code a bit.

    I have an excel file where one sheet contains a list of emails and then there are sheets with letters for each of these emails.
    I already have a code that creates PDFs from all the sheets (in a dedicated folder).
    Now I need excel to loop through the email list (which is listed in the first sheet) and programmatically send an an outlook email to each of the emails, and attach the corresponding PDF file.

    I need excel to do this automatically, because I have about 100 files and I need to to this every month (so far this was done manually, and it is a real pain).

    Al the best and thanks in advance
    Attached Files Attached Files

  7. #7
    This is an old thread. It would have made more sense to start a new one.
    However - see attached.
    Attached Files Attached Files
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  8. #8
    Quote Originally Posted by gmayor View Post
    This is an old thread. It would have made more sense to start a new one.
    However - see attached.
    My applogies. It made sense to keep my query in context with THAT threat.. Next time I will open a new one.
    Reagrdless, thank you very much for your time and trouble. I will study your solution, hoping I will be able to understand it and apply to my real data

  9. #9
    Firstly - thank you again, Graham. This is almost excaly what I needed
    However, after giving this code a test run, there are 2 things which I don't know how to fix:
    1. When saving as PDF, it seems that the "Sales sum" is not captured in the PDF that the code creates.
    2. The code creates "open drafts" of all the emails, and outlook is now wating for me to hit the send button for each and every one of them.
    I can appreciate the logic of verifying each eamil prior to sending, but this is something I really wanted to avoid.
    I hoped the code will "fire" the "send" command, and the whole things wil happen automatically behind the scenes :-)

  10. #10
    1. The macro creates PDF copies of the named worksheets. I did not investigate the content of those worksheets before they were saved. However it seems pretty obvious that the links to the first sheet are missing from cell F21 of Sheets 004 and 005. Fix the sheets and the PDFs will reflect the content.
    2. The Send command is commented out for testing. When you have tested and are happy with the results, remove the apostrophe from the beginning of the Send line as indicated in the note in the code and the messages will go to the Outbox. Do not delete the Display line.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  11. #11
    Thank you VERY much ! I will give it a try as you wrote
    All the best and god's bless

Posting Permissions

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