Posts: 27
Threads: 9
Joined: Apr 2013
Hello,
I am working on shortening the process of attached specific files to emails that I send on a regular basis. In my job I run quotes and then email those quotes to the person requesting them. I already automate quite a bit of the process of creating these emails but one thing that I haven't been able to automate yet is attaching the unique quotes I just generated to the email that I am sending to the person who requested them. I found the code below as I have been searching the forum for some example of searching for a specific file and I was hoping I could adjust it to do what I am looking for.
'Cc
str s.getclip
str myFolder.expandpath("J:\Quotes\2014")
str myFile.from(myFolder F"{s}")
ARRAY(str) a; int i
GetFilesInFolder a myFolder
for i 0 a.len
,;out a[i]
,if(a[i]~myFile)
,,del- myFile; err
I know this code was generated to delete a file as you can see I want to change to attached the quote(s) that I generate to my email. The copy I have at the top of the code is the term I would like to search in the folder "2014". All of the quotes that are run follow the following naming convention between the brackets [MM-DD CLIENT LAST NAME - ADVISOR LAST NAME -] i.e. [08-05 Smith - Johnson -]. All of the quotes are also PDF files it that helps as well. Lastly, there are generally at least 2 PDFs that need to be attached that follow the naming convention and sometime as many 8 PDFs that need to be attached. I really appreciate any help you can give me on this.
Thank You,
Paul
Posts: 12,072
Threads: 140
Joined: Dec 2002
Do you want to find all these files assuming clipboard text is "TextInClipboard"?
J:\Quotes\2014\08-05 Smith - Johnson - TextInClipboard.pdf
J:\Quotes\2014\08-06 Smith - Johnson - TextInClipboard.pdf
J:\Quotes\2014\08-07 Somebody - Else - TextInClipboard.pdf
Posts: 27
Threads: 9
Joined: Apr 2013
Hello Gintaras,
Gintaras Wrote:Do you want to find all these files assuming clipboard text is "TextInClipboard"?
J:\Quotes\2014\08-05 Smith - Johnson - TextInClipboard.pdf
J:\Quotes\2014\08-06 Smith - Johnson - TextInClipboard.pdf
J:\Quotes\2014\08-07 Somebody - Else - TextInClipboard.pdf
The text that will be in the clipboard is the "08-05 Smith - Johnson -". The rest of the text after the last dash will change based on the quote that is being run. For example the full name of the PDF would look similar to this.
J:\Quotes\2014\08-05 Smith - Johnson - Gen $4500 Monthly 6yrs Shared 3% Comp.pdf
J:\Quotes\2014\08-05 Smith - Johnson - Comparison.pdf
J:\Quotes\2014\08-05 Smith - Johnson - Thrivent $4500 Monthly 6yrs Shared 3% Comp.pdf
J:\Quotes\2014\08-05 Somebody - Else - Gen $5000 Monthly 8yrs Shared 3% Comp.pdf
J:\Quotes\2014\08-05 Somebody - Else - Comparison.pdf
The goal is to attach all of the files in the example above that have the text "08-05 Smith - Johnson -". Also, because I am not the only one entering the names of the PDFs sometime the spaces between the dashes aren't there. However I can say with certainty all of the people running quotes are using "MM-DD Client LName - Advisor LName -" even if all of the spaces are not there.
Thank you for your help on this.
Paul
Posts: 12,072
Threads: 140
Joined: Dec 2002
Macro Macro2351
;This code until ------ creates files for testing. Delete it.
;Select the following text inside "" and run this macro.
;"08-05 Smith - Johnson -"
;It should display 3 files.
out
str files=
;C:\Quotes\2014\08-05 Smith - Johnson - Gen $4500 Monthly 6yrs Shared 3% Comp.pdf
;C:\Quotes\2014\08-05 Smith - Johnson - Comparison.pdf
;C:\Quotes\2014\08-05 Smith-Johnson-Thrivent $4500 Monthly 6yrs Shared 3% Comp.pdf
;C:\Quotes\2014\08-05 Somebody - Else - Gen $5000 Monthly 8yrs Shared 3% Comp.pdf
;C:\Quotes\2014\08-05 Somebody - Else - Comparison.pdf
mkdir "C:\Quotes\2014"
str _s1 _s2="test"
foreach(_s1 files) _s2.setfile(_s1)
;--------------
str s.getsel
str attachmentsList
str rx=" *- *"
s.replacerx(rx "-")
;out s
ARRAY(str) a; int i
GetFilesInFolder a "C:\Quotes\2014" "*.pdf"
for i 0 a.len
,;out "----"; out a[i]
,str s2
,if(findrx(a[i] "\\Quotes\\20\d\d\\(\d\d-\d\d [^-]+-[^-]+-).+\.pdf" 0 1 s2 1)<0) end F"incorrect filename format: {a[i]}"
,s2.replacerx(rx "-")
,;out s2
,if(!(s2~s)) continue
,;out a[i]
,attachmentsList.addline(a[i])
out attachmentsList
Posts: 27
Threads: 9
Joined: Apr 2013
Hello Gintaras,
I tried out your code and it works really well. Although I am a novice with some of the programming. I have been working on editing the macro so it searches the already created directory on my system. Trouble is I can't seem to figure out why it isn't working. Here are the changes I made to the code and I am getting the following error message.
Error (RT) in Attached Quote: cannot open or create file: Access is denied. (5).
out
str files.searchpath("J:\Quotes\2014")
/;C:\Quotes\2014\08-05 Smith - Johnson - Gen $4500 Monthly 6yrs Shared 3% Comp.pdf
/;C:\Quotes\2014\08-05 Smith - Johnson - Comparison.pdf
/;C:\Quotes\2014\08-05 Smith-Johnson-Thrivent $4500 Monthly 6yrs Shared 3% Comp.pdf
/;C:\Quotes\2014\08-05 Somebody - Else - Gen $5000 Monthly 8yrs Shared 3% Comp.pdf
/;C:\Quotes\2014\08-05 Somebody - Else - Comparison.pdf
/mkdir "C:\Quotes\2014"
str _s1 _s2="test"
foreach(_s1 files) _s2.setfile(_s1)
/08-05 Zepper - King -
;--------------
str s.getsel
str attachmentsList
str rx=" *- *"
s.replacerx(rx "-")
;out s
ARRAY(str) a; int i
GetFilesInFolder a "J:\Quotes\2014" "*.pdf"
for i 0 a.len
,;out "----"; out a[i]
,str s2
,if(findrx(a[i] "\\Quotes\\20\d\d\\(\d\d-\d\d [^-]+-[^-]+-).+\.pdf" 0 1 s2 1)<0) end F"incorrect filename format: {a[i]}"
,s2.replacerx(rx "-")
,;out s2
,if(!(s2~s)) continue
,;out a[i]
,attachmentsList.addline(a[i])
out attachmentsList
As you can see I haven't made much for changes other than to point to the actual folder on my system. Any help you can give me with the error code would be appreciated.
Best Regards,
Paul
Posts: 12,072
Threads: 140
Joined: Dec 2002
In my code delete everything at the beginning until the ---- line.
Then replace C: to J:
Posts: 27
Threads: 9
Joined: Apr 2013
Boy I need to read the commented out instructions better. Sorry about that.
Anyway, I just realized that there are PDFs that are formatted differently than the actual quotes that are done in this folder that we are searching. However all of those PDFs are not the quotes that I want to attach to the email with this macro. How would exclude any other formatted title from the search?
Thanks,
Paul
Posts: 12,072
Threads: 140
Joined: Dec 2002
Differently formatted filenames probably produce error. Replace
end F"incorrect filename format: {a[i]}"
to
continue
Posts: 27
Threads: 9
Joined: Apr 2013
Last question, how use the to attach the files to my email?
Thanks,
Paul
Posts: 12,072
Threads: 140
Joined: Dec 2002
If you use SendMail, pass it as 5-th argument.
Macro Macro2354
Posts: 27
Threads: 9
Joined: Apr 2013
Are they any other options to attaching to the email that you can think of? As it seems like the microsoft exchange server my company uses does not use SMTP. Instead they use RPC protocol and I haven't been able to get quick macros synced with my Microsoft Office account.
Thank you for all your help on this it has given me a much better understanding of how to program in QM!
Thank You,
Paul
Posts: 12,072
Threads: 140
Joined: Dec 2002
Another option - automate your program that sends email. Let the macro do what you would do manually.
For example, Thunderbird can be easily automated to add a list of attachments:
Macro Macro2355
str attachmentsList=
;C:\Quotes\2014\08-05 Smith - Johnson - Comparison.pdf
;C:\Quotes\2014\08-05 Smith - Johnson - Gen $4500 Monthly 6yrs Shared 3% Comp.pdf
;C:\Quotes\2014\08-05 Smith-Johnson-Thrivent $4500 Monthly 6yrs Shared 3% Comp.pdf
int w=wait(30 WA win("Write: " "Mozilla*WindowClass"))
str sa
foreach sa attachmentsList
,key Aftf
,int w2=wait(30 WA win("Attach File(s)" "#32770"))
,;key An
,;key (sa) Y
,int c=child("" "ComboBox" w2 0x0 "id=1148") ;;combo box 'File name:'
,sa.setwintext(child("" "Edit" c))
,key Y
Posts: 27
Threads: 9
Joined: Apr 2013
Hello Gintaras,
I really appreciate the ideas, I will see if there is the ability to pull my email into thunderbird but I think this is going to be a battle with my IT department. In the meantime, I learned a ton from your code you put together for me so I really appreciate all the help you have given me. I will be a QM supporter for life!
Thanks,
Paul
|
