+ Reply to Thread
Results 1 to 8 of 8

Macro to email files from folder automatically

  1. #1
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Prefer Excel 2003 but 2007 is great!
    Posts
    980

    Macro to email files from folder automatically

    Hi all

    I am wondering if its possible to have a macro setup that will go into a specified folder and then email the file (pdf) to the respective individuals? I have the following task
    1) Outlook macro goes into the following folder:
    C:\Users\TORHXH\Desktop\Timekeeper performance overview\PDF

    2) The folder has PDF files with the following naming convention:
    Ellis, Mark - May.pdf
    --> I would like the macro to email e.g. the above named file to "mark.ellis@xyz.com" (the file shows last name, first name)

    3) In the body of the email have the following
    Subject line
    [Employee name i.e. Mark Ellis] TK Performance overview [Month, year e.g. May, 2017] (month of taken from file name?)

    Body of email:
    Hello [Employee name e.g. Mark Ellis]

    Attached is you performance dashboard that shows you Key Performance Indicators (KPIs) as at FY [month, year], which shows the following KPIs
    1. ABC
    2. ABC
    3. ABC
    4. ABC

    The purpose of this dashboard is to keep you apprised of your own progress so that you can plan accordingly for the remainder of the year.

    If you have any questions or feedback related to your report of the KPIs, please do not hesitate to reach out to me.

    4) Send out the email.

    There will be a total of +/-110 PDF files, so I would like if this process can be automated as we will be sending these files each month?

    Pls let me know...thank you in advance!

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,266

    Re: Macro to email files from folder automatically

    Hi again

    I assume this is part of the file we worked on earlier for creating pdfs? It might make sense to add a column next to the employee names in Excel, and just have excel email them as it creates them (invoking outlook in the process)
    Or the code could build the email name based on your post criteria, a list is handy but not necessary.
    Let me know if that idea works for you, and I can work on some code.
    Last edited by Arkadi; 05-18-2017 at 04:17 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Prefer Excel 2003 but 2007 is great!
    Posts
    980

    Re: Macro to email files from folder automatically

    Quote Originally Posted by Arkadi View Post
    Hi again

    I assume this is part of the file we worked on earlier for creating pdfs? It might make sense to add a column next to the employee names in Excel, and just have excel email them as it creates them (invoking outlook in the process)
    Or the code could build the email name based on your post criteria, a list is handy but not necessary.
    Let me know if that idea works for you, and I can work on some code.
    Hi Arkadi

    Yes, it is infact part of the earlier help with the PDFs.

    I am open to suggestions, so if it easier or less redundant to have Excel email the copies that works for me as well? I can add a column to the employee name that contains their email address - i assume that is what you meant?

    Pls see attached file - it has the emails next to it. WOuld it be possible to have the subject and body of email contain the info., I have listed in my post earlier?

    kindly let me know. thanks a million for your awesome support

    FYI: the email address is to a fake url in the file
    Attached Files Attached Files

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,266

    Re: Macro to email files from folder automatically

    I made some small changes to the first macro I had made, so you will need to replace the code in that module with the code below:

    Please Login or Register  to view this content.

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,266

    Re: Macro to email files from folder automatically

    Did this resolve the question? If so please mark the thread as solved? Thanks in advance

  6. #6
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Prefer Excel 2003 but 2007 is great!
    Posts
    980

    Re: Macro to email files from folder automatically

    Quote Originally Posted by Arkadi View Post
    Did this resolve the question? If so please mark the thread as solved? Thanks in advance
    Yes, thank you ....I tried to do it the other day but gives me an error message and continues today - which is strange.

    I will try to do it again shortly

  7. #7
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Prefer Excel 2003 but 2007 is great!
    Posts
    980

    Re: Macro to email files from folder automatically

    Hi Arkadi

    Need to bug you one last time.

    I seem to be having some issues with creating spaces <br>. This is my current code that "emails" the report that you developed

    Please Login or Register  to view this content.
    I would like the text to remain the same, except after this line
    "Please find attached your performance dashboard detailing Key Performance Indicators (KPIs)" _
    & " FY " & Format(myDate, "mmmm, yyyy") & "." & "<br>" & _

    I would like to show this:
    Top Section:

    1. Billable Hours: Summarizes your billable hours by month for the fiscal year (Jul-Jun) as well as your year to date collections, AR (accounts receivable) and WIP (unbilled time)

    2. Non-Billable Hours: Non-billable hours broken down by category

    3. Collections by month with a comparison to prior year (variance in $ and %)

    Graph Section:

    1. Monthly Billable Hours Breakdown - displays timekeeper hours (red line) , office average hours (black line) and highest hours (grey) over the last 2 fiscal years by month

    2. Collections, AR and WIP - summary amounts of year to date collections, AR (accounts receivable - billed but not collected) as of report run date and WIP (unbilled time) as of report run date

    3. Realization on collections - this graphs details the total slippage (amounts lost) from the value of docketed time to collected time.
    a) Fees Worked at Adjusted Rates** - this is your standard billing rate adjusted for any special billing arrangements, flat/fixed fees or other multiplied by hours
    b) Rate Discount - additional discounts given due to client arrangements
    c) Fees Worked at Matter Rates - matter rate calculated prior to billing
    d) Fees Written Up/Down - additional adjustments made
    e) Additional Discounts
    f) Fees Billed - rate calculated when billing
    g) Credits - additional discounts after billing
    h) Fees Collected - cash received and posted in the accounting system
    i) Total Slippage - total amount of discounts from the value of docketed time to collected time

    4. Avg Days to Close Time - displays the average days you close your time versus the firm average (3 days) and your practice group.


    Fiscal year: July 1 - June 30
    FYTD: Fiscal year to date
    PYFYTD: Prior year fiscal year to date

    ^ this needs to be the body of the email. Can you pls help?

  8. #8
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,266

    Re: Macro to email files from folder automatically

    See if this works....

    Please Login or Register  to view this content.
    Last edited by Arkadi; 06-27-2017 at 04:09 PM. Reason: Forgot the text "Top Section:" which I added now

+ 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