+ Reply to Thread
Results 1 to 13 of 13

automatic reply with parametric Excel file attached

  1. #1
    Registered User
    Join Date
    08-05-2016
    Location
    athens
    MS-Off Ver
    2007
    Posts
    7

    automatic reply with parametric Excel file attached

    i have an excel file in "c:/desktop/files" where, by inputting a unique customer code, an one-page report is produced with all customer's data.

    What i need, is:

    1. my colleagues to be able to send an empty e-mail, via outlook 2007, with only the customer code in the subject line
    2. the excel file to run (either firstly the excel file to be automatically opened or, if not possible, i should have it always running) for this customer code
    3. a pdf to be produced and saved in "c:/desktop/pdf files"
    4. an automatic reply to be sent to the colleague with the pdf attached.


    I know this can be done as i have seen it happening in other organisations but i do not know how to do it...

    any ideas for that???

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016
    Posts
    4,390

    Re: automatic reply with parametric Excel file attached

    This is an issue that is bigger than Excel alone.

    The first issue I see is deciding which emails should be processed. It would be ideal if you could write a filter rule in Outlook to direct these emails to a specific folder.

    Then I would write code that reads the messages in the folder, parses out the customer code, runs the report, saves it as PDF and then moves the processed mail to another folder so it doesn't get processed again and finally mails out the report to the recipient. I have code that does some of these bits and pieces already.

    The way I would execute it is to use the Windows Task Scheduler to start the program on log in and run it every x minutes thereafter. This way the Excel program isn't constantly running in the background.

    Once you have a customer code, how do you generate the report for it?
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    08-05-2016
    Location
    athens
    MS-Off Ver
    2007
    Posts
    7

    Re: automatic reply with parametric Excel file attached

    Thank you for the fast response.

    once i have the customer code, it is inputted in the B2 cell of the "report" spreadsheet.

    I could easily create the new rule for the outlook folder transfer of all relevant e-mails received, however i would really need help for the rest part that has to do with the code needed...

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016
    Posts
    4,390

    Re: automatic reply with parametric Excel file attached

    I am modifying some code that I already have.

    On the control panel sheet fill in the following information.

    Cell B1: Mailbox Name

    Cell B3: Folder to which you sent the request for the reports

    Cell B5: The name of the subfolder to the folder in B3 to which the reports will be moved after being processed.

    Cell B7: The Path Name to the windows folder that contains the report file

    Cell B9: The name of the report file

    Once you fill this information in, you are good to go.

    When you click on the Read Mail Button, it reads the mail in the folder listed in cell B3. It gets the subject and the sender.

    Then it opens the report file and puts the subject in Cell B2 on Sheet 1. I am assuming that there is only one sheet in this spreadsheet.

    It then saves that sheet as a PDF file in the same folder as the report file. The name is the subject with “.pdf” added to the end.

    It then mails the PDF file to the sender, marks the email as sent and moves it to the sub folder.

    I tested this code with the values shown for my system and it worked. It will fail if you have an illegal character for a file name in the subject such as a colon or slash. If your customer codes don't have these characters that should not be an issue for you.

    The next thing you will have to do is set up the windows task scheduler to run the ProcessReport macro in this spreadsheet when you log in, and every X minutes after that. I can also give you a hand with that if you need it. But do tell me what version of windows you have.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-05-2016
    Location
    athens
    MS-Off Ver
    2007
    Posts
    7

    Re: automatic reply with parametric Excel file attached

    thank you for the tremendous help, it seems to be incredibly easy to use...

    ...however, after having filled in(correctly, i hope) all the required info in the cells, i get the message in the attached image.


    message.jpg

    What i inputted, is:

    Mailbox: my name, as it appears at the top of outlook 2007, right? By the way, is this really needed as information for the loop to be executed?

    In Folder: Inbox (i will not be moving the messages to subfolders)

    Sub Folder: _sbd (this is the folder that the response with the attachment will be moved to)

    Report Folder: C:\Users\\Desktop\auto email (this is the folder that the report file is located)

    Report File Name: latest pseudo-position.xlsm (the report filename)


    any ideas for finetuning?

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016
    Posts
    4,390

    Re: automatic reply with parametric Excel file attached

    You may have to go to Tools->References in the VBA browser, scroll down and click on Microsoft Outlook 15.0 Object Library - yours might have a different number because you have a different version of Outlook.
    Attached Images Attached Images

  7. #7
    Registered User
    Join Date
    08-05-2016
    Location
    athens
    MS-Off Ver
    2007
    Posts
    7

    Re: automatic reply with parametric Excel file attached

    thank you!!! it works perfectly!!! it is unbelievable!!!

    may i ask for two additional favors...???

    1. Is it possible to create a "safe senders list" to whom, upon my granted authorisation, the automatic reply could be sent? In that way, i will prevent sending reports to unauthorised users who will have learnt this module...

    2. i created a task in the windows task scheduler (Windows 7 Enterprise is Windows version) in order for the read mail 2.1.xlsb to open every time i log in. How do i setup the "read mail" button to autoexecute e.g. every 1 minute?
    Last edited by tas75; 12-12-2017 at 04:52 AM.

  8. #8
    Registered User
    Join Date
    08-05-2016
    Location
    athens
    MS-Off Ver
    2007
    Posts
    7

    Re: automatic reply with parametric Excel file attached

    don't bother for the first request, i dealt with it by incorporating the senders in the outlook rule.

    for the second issue, i added a macro in the existing ProcessReport as follows:

    Application.OnTime Now + TimeValue("00:01:00"), "ProcessReport"


    It works fine as long as there are e-mails to process, however the loop stops and the automatic repeat fails when there is no e-mail in the outlook folder to process...

    moreover, is there a way when the "Read mail" button is clicked/activated, the excel report file not to be closed?
    Last edited by tas75; 12-12-2017 at 04:52 AM.

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016
    Posts
    4,390

    Re: automatic reply with parametric Excel file attached

    Our postings crossed in the middle of the night.

    Here the new version. You will have to set your reference again.

    On the Control Panel, you now have a table to add a list of safe senders.

    I also added another feature: a log. It records when a message has been processed, who sent it, what the subject was, and a status: this should be either Unauthorized User (for people not on the safe senders list) or Report Sent. You can use this table for pivot table analysis.

    I like your idea of controlling the safe senders with a filter rule. It's better than the safe senders list. But I have a better idea on controlling when the report runs. Set up the trigger on the scheduled task to reflect what is shown in the attached picture.

    So here is what I recommend: I remove the safe senders list from the control page and let you manage it with the Outlook Filter Rule. Let me know if you would like to do this and also if you would like to keep the log.

    It won't take much to make these changes.
    Attached Images Attached Images
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-05-2016
    Location
    athens
    MS-Off Ver
    2007
    Posts
    7

    Re: automatic reply with parametric Excel file attached

    ok - so, i kept the outlook rule in order to control the senders' list.

    I used the 2.2 version with the settings needed for my pc however i got again an error message "Error in loading DLL". So i think i will get back to 2.1 version that still works fine (log is nice but i can live without it if the rest works smoothly).

    I modified the task scheduler so that the readmail.xlsb runs every 5 minutes. However, the file opens but the "readmail" button is not executed...was that provided in 2.2 version?

    current update: the file, once triggered and ran, seems to work perfectly! Thank you once more, there is no vba macro to calculate its value!!! - i will let you know if any problem occurs...
    Last edited by tas75; 12-13-2017 at 05:38 AM.

  11. #11
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016
    Posts
    4,390

    Re: automatic reply with parametric Excel file attached

    I will assume that you have the task scheduler set up to open the file and run the macro. If not, I can help with that. Also you need the file to be a trusted file (Excel should ask the first time it opens) or put the file in a trusted location.

  12. #12
    Registered User
    Join Date
    08-05-2016
    Location
    athens
    MS-Off Ver
    2007
    Posts
    7
    I have set up the scheduler in the way that you showed to me. I trigger the readmail when the excel opens and after that,i suppose it runs periodically,right?


    One last question...is there a solution in case i am absent and my pc is turned off? I could place the report file in a common server but how could the operation through outlook be triggered?

  13. #13
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016
    Posts
    4,390

    Re: automatic reply with parametric Excel file attached

    When you log on, the task scheduler should run the program in the background and then re-run it every x minutes as you specify.

    The only way I've ever been able to get VBA to work with Outlook is if I am logged in. Outlook must be installed and configured on the machine that is running it, but the application does not have to be launched. I have a laptop that runs reports that read outlook. I reboot it periodically, but I log in and then lock my screen. It's the best I can do.

    Having it trigger automatically depends on the task scheduler that must be set up on the machine that is running it. The issue is that you really want only one machine running it at a time. If you could set this up on the machine that has the common drive and whoever logs into that machine has Outlook configured, it will run with that person's credentials.

    I do scheduled reports on an "industrial" scale. I have two directories: one for scripts and one for the reports. The reports are regular Excel files. I can open them and work with them any time.

    The script is a special file. I have a template for it. I make a copy of the template, give it a name, and then fill in three lines of data: the directory containing the report file, the name of the report file, the name of the macro in the report file that I want to run. I have a formula that generates the line to put in the task scheduler.

    So the task scheduler opens the script file. The script file has an on open macro that reads the data you filled in on where to find the file and runs the specified macro in that file. It also records error information. Both the script and report directories are trusted locations.

    The reason I bring up all this is that I might be able to modify the script program. You tell the script who is supposed to be running it by login name. The script will look at the login of the person calling it, and if it matches, execute the code to run the program otherwise, it exits immediately. So you can have multiple people set up with their task schedulers to run the same script, but only the person that is identified in a cell in the script will actually run the code. The rest will open and close immediately.

    So when you are out of office, you will need to open the script and fill in the login ID of the person taking over. It sounds very complicated, but it isn't. The only issue I have is that I won't be able to test it.

    Here is my script program: http://www.utteraccess.com/wiki/Schedule_a_Macro_to_Run

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1