Loop through a folder and e-mail PDF's

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Loop through a folder and e-mail PDF's

Post by JimmyC »

The office uses Office 2010; but I am not sure if the tasks below could be done via VBA using one of the office products (i.e. Word, Excel or Outlook—plus I am terrible at VBA) or whether someone knows of a 3rd party application that will accomplish the objective or a 3rd party add-in for an office application.

In a Windows folder named “Process” there are numerous PDF files that are created by a billing application (i.e. each time I need to complete the e-mail task—explained next—there will be a random number of PDF’s in the “Process” folder. I need a program that the end user can activate or invoke, that will attach a single PDF to a separate e-mail. Each e-mail is going to the same person and the e-mail subject line is “Invoice”. Once the e-mail is automatically sent, the program moves the PDF that was just sent on the e-mail to a “Completed” folder. The program continues this iteration process until there are no PDF’s left in the “Process” folder and then the program / application then stops or terminates. I need for the e-mails to display in the end user's sent folder--a couple of a possible 3rd party applications can send e-mail but use their own smtp---and thus the "sent" mail does not end-up in the end user's Outlook sent folder.

Can this be done within an office application or this better suited for a 3rd party standalone application or is there a 3rd party add-in for an office application? Please keep in mind that my VBA skills are poor.

Thank you for any advice.
JimC

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Loop through a folder and e-mail PDF's

Post by HansV »

You want all the files to be sent to the same recipient, but each in a separate e-mail, instead of all files attached to a single e-mail?
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Loop through a folder and e-mail PDF's

Post by JimmyC »

Hans, yes that is correct. There person receiving the e-mail has mandated a "one to one" relationship between the e-mail and a single PDF. Each PDF is an invoice. My guess is that this person has some sort of Outlook rule, etc. to process /route these e-mails. Thank you for helping me. JimC

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Loop through a folder and e-mail PDF's

Post by HansV »

Since you want to send the files from Outlook, I'd use a macro in Outlook.
The end user can do the following:
- Start Outlook.
- Press Alt+F11 to start the Visual Basic Editor.
- Select Insert > Module.
- Copy the following code into the module window:

Code: Select all

Sub SendPDF()
    ' Paths should end in backslash \
    ' Folder containing the PDF files
    Const strSourceFolder = "C:\Process\"
    ' Folder to which the files should be moved
    Const strTargetFolder = "C:\Completed\"
    ' E-mail address of recipient
    Const strRecipient = "john.doe@acme.com"
    ' Subject of the e-mails
    Const strSubject = "Invoice"
    ' Message text of the e-mails
    Const strMessage = "Please see the attached file." & vbCrLf & _
        "Yours sincerely, Jim"
    Dim strFile As String
    Dim objMsg As MailItem
    ' Get the name of the first PDF file in the source folder
    strFile = Dir(strSourceFolder & "*.pdf")
    ' Loop through all PDF files
    Do While strFile <> ""
        ' Create a new e-mail
        Set objMsg = CreateItem(olMailItem)
        ' Set the recipient
        objMsg.To = strRecipient
        ' Set the subject
        objMsg.Subject = strSubject
        ' Set the message
        objMsg.Body = strMessage
        ' Add the PDF file as attachment
        objMsg.Attachments.Add strSourceFolder & strFile
        ' Send the message
        objMsg.Send ' or objMsg.Display
        ' Move the PDF file to the target folder
        Name strSourceFolder & strFile As strTargetFolder & strFile
        ' Get the next file name
        strFile = Dir
    Loop
End Sub
Change the constants at the beginning of the code to match the actual situation.
Switch back to Outlook.
Create a Quick Access Toolbar button or ribbon button for the macro (File > Options, then Customize Ribbon or Quick Access Toolbar).
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Loop through a folder and e-mail PDF's

Post by JimmyC »

Hans---thank you. I will be implementing and testing late tomorrow (Friday afternoon) or Monday. I truly appreciate your assistance and am grateful for your willingness to share your talents with others.....You are truly a blessing for those, like me, that you help out....I can never say "thank you" enough times to you. JimC

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Loop through a folder and e-mail PDF's

Post by JimmyC »

Hans,
The end user asked me if there is a way to capture & display the number of e-mails sent each time the macro is run. I found code on the intranet for a message box, wow, its simple. But I can't figure out how to, or if its possible, to "count" the times the "loop" processes and then display that number in the message box. I have found conflicting information on the internet and am confused whether a message box can display only static text or whether I would need an input box--even though I do not want to input any value---only have a calculated value displayed--and then the user clicks "OK" I placed the code below between the Loop and End Sub lines at the bottom of the macro---it may need moved somewhere else should a calculation be done.

Thanks for your help and patience...and I do try to "learn" and not thoughtlessly just paste code given to me and forget about the "learning" opportunity. Jim

MsgBox "The total e-mails sent is:"

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Loop through a folder and e-mail PDF's

Post by HansV »

You can use MsgBox for this - the message can combine fixed and variable parts. I have added a few lines to the code, with comments:

Code: Select all

Sub SendPDF()
    ' Paths should end in backslash \
    ' Folder containing the PDF files
    Const strSourceFolder = "C:\Process\"
    ' Folder to which the files should be moved
    Const strTargetFolder = "C:\Completed\"
    ' E-mail address of recipient
    Const strRecipient = "john.doe@acme.com"
    ' Subject of the e-mails
    Const strSubject = "Invoice"
    ' Message text of the e-mails
    Const strMessage = "Please see the attached file." & vbCrLf & _
        "Yours sincerely, Jim"
    Dim strFile As String
    Dim objMsg As MailItem
    ' Variable to keep track of the number of messages
    Dim lngCount As Long
    ' Get the name of the first PDF file in the source folder
    strFile = Dir(strSourceFolder & "*.pdf")
    ' Loop through all PDF files
    Do While strFile <> ""
        ' Create a new e-mail
        Set objMsg = CreateItem(olMailItem)
        ' Set the recipient
        objMsg.To = strRecipient
        ' Set the subject
        objMsg.Subject = strSubject
        ' Set the message
        objMsg.Body = strMessage
        ' Add the PDF file as attachment
        objMsg.Attachments.Add strSourceFolder & strFile
        ' Send the message
        objMsg.Send ' or objMsg.Display
        ' Increase the counter
        lngCount = lngCount + 1
        ' Move the PDF file to the target folder
        Name strSourceFolder & strFile As strTargetFolder & strFile
        ' Get the next file name
        strFile = Dir
    Loop
    ' Display a message
    MsgBox lngCount & " messages have been sent.", vbInformation
End Sub
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Loop through a folder and e-mail PDF's

Post by JimmyC »

Hans---thank you again for taking your valuable time to help those like me---that are very grateful. You are the best...thank you. Jim

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Loop through a folder and e-mail PDF's

Post by HansV »

There was a typo in the code that I posted - I have edited my previous reply. Sorry about that.
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Loop through a folder and e-mail PDF's

Post by JimmyC »

Hans..thanks. I was off working on the code, and the code as previously posted does give me a message box, but it states "messages have been sent"...but there is no #. I will now work on the revised code. I guess I am still trying to figure out how the variable objMsg gets into the message box. In the revised code, you now have "lngCount"--but I don't see that anywhere. I also notice the objMsg is used twice, once as MailItem and once as a createitem....can you explain this? Sorry I am not very good with this stuff. Jim

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Loop through a folder and e-mail PDF's

Post by HansV »

The variable lngCount is declared in the line

Code: Select all

    Dim lngCount As Long
Since it is declared as a variable of type Long, its initial value is 0.

Inside the loop

Code: Select all

    Do While strFile <> ""
        ...
    Loop
the value of lngCount is increased by 1 after each e-mail is sent:

Code: Select all

        lngCount = lngCount + 1
So if the code sends 37 e-mails, the value of lngCount will be 37 at the end of the loop.

Finally, the message box uses the value of lngCount:

Code: Select all

    MsgBox lngCount & " messages have been sent.", vbInformation
If lngCount is 37, the message box will display "37 messages have been sent". (You'll need to use the corrected code)
Best wishes,
Hans

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Loop through a folder and e-mail PDF's

Post by JimmyC »

Hans---thank you for the explanation. I am off to adjust the code and experiment. I also had to enable all macros for Outlook which is not recommended. Is there any way just to permit this macro to run but not others? Or is there little risk in enabling all macros so long as the end user is careful not download Outlook macros?

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Loop through a folder and e-mail PDF's

Post by StuartR »

JimmyC wrote:Hans---thank you for the explanation. I am off to adjust the code and experiment. I also had to enable all macros for Outlook which is not recommended. Is there any way just to permit this macro to run but not others? Or is there little risk in enabling all macros so long as the end user is careful not download Outlook macros?
The best way to permit a macro like this is to sign it with a code-signing certificate, that you can buy from an organization like Thawte, Symantecor DigiCert.
StuartR


User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Loop through a folder and e-mail PDF's

Post by HansV »

While using a certificate is certainly the safest and most convenient solution, I don't really think you run a large risk if you enable all macros.
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: Loop through a folder and e-mail PDF's

Post by stuck »

If this macro is for internal use only then you can always self sign the macro:
http://www.howto-outlook.com/howto/selfcert.htm

Ken

JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Loop through a folder and e-mail PDF's

Post by JimmyC »

Hans, Stuart and Ken---thank you all for the advice. The macro is only for internal use. I will look into the self sign alternative--if nothing else, to understand the process better. Again, thank you all for the replies. Jim

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Loop through a folder and e-mail PDF's

Post by StuartR »

I don't know where you work Jimmy, or what infrastructure they have. When I used to work for a large corporation they created a signing certificate for me to use, based on their internal infrastructure. This certificate would only work for people using computers built by their IT department, but that was perfect for what I needed.
StuartR


JimmyC
3StarLounger
Posts: 382
Joined: 08 Feb 2010, 16:08

Re: Loop through a folder and e-mail PDF's

Post by JimmyC »

Stuart--thanks for the advice. I will inquire to our system folks whether this is possible. Thanks again. Jim