Loop through a folder and e-mail PDF's
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Loop through a folder and e-mail PDF's
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
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
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Loop through a folder and e-mail PDF's
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
Hans
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Re: Loop through a folder and e-mail PDF's
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
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Loop through a folder and e-mail PDF's
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:
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).
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
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
Hans
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Re: Loop through a folder and e-mail PDF's
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
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Re: Loop through a folder and e-mail PDF's
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:"
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:"
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Loop through a folder and e-mail PDF's
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
Hans
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Re: Loop through a folder and e-mail PDF's
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
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Loop through a folder and e-mail PDF's
There was a typo in the code that I posted - I have edited my previous reply. Sorry about that.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Re: Loop through a folder and e-mail PDF's
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
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Loop through a folder and e-mail PDF's
The variable lngCount is declared in the line
Since it is declared as a variable of type Long, its initial value is 0.
Inside the loop
the value of lngCount is increased by 1 after each e-mail is sent:
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:
If lngCount is 37, the message box will display "37 messages have been sent". (You'll need to use the corrected code)
Code: Select all
Dim lngCount As Long
Inside the loop
Code: Select all
Do While strFile <> ""
...
Loop
Code: Select all
lngCount = lngCount + 1
Finally, the message box uses the value of lngCount:
Code: Select all
MsgBox lngCount & " messages have been sent.", vbInformation
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Re: Loop through a folder and e-mail PDF's
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?
-
- Administrator
- Posts: 12623
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Loop through a folder and e-mail PDF's
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.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?
StuartR
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Loop through a folder and e-mail PDF's
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
Hans
-
- Panoramic Lounger
- Posts: 8187
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: Loop through a folder and e-mail PDF's
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
http://www.howto-outlook.com/howto/selfcert.htm
Ken
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Re: Loop through a folder and e-mail PDF's
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
-
- Administrator
- Posts: 12623
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Loop through a folder and e-mail PDF's
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
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Re: Loop through a folder and e-mail PDF's
Stuart--thanks for the advice. I will inquire to our system folks whether this is possible. Thanks again. Jim