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

​Adding link for Cross-posting LINK​

---
Have a try with your revised macro:

Code:

---

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

---

Cross posted at https://www.msofficeforums.com/outlo...ook-email.html :banghead:

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

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

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

This is an old thread. It would have made more sense to start a new one.
However - see attached.

Quote:

---

Originally Posted by gmayor

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 :o:

Firstly - thank you again, Graham. This is almost excaly what I needed :yes
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 :-)

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.

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