Macro to email files from folder automatically

[jw01]

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 "[email protected]" (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!

[Arkadi]

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.

[jw01]

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

[Arkadi]

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:

Sub jw01pdf()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lr As Long, i As Long
Dim myPath As String
Dim myFileName As String

Set ws2 = Worksheets("Summary")
Set ws1 = Worksheets("Employees")
lr = ws1.Range("A" & Rows.Count).End(xlUp).Row
myPath = "C:\Users\TORHXH\Desktop\Timekeeper performance overview\PDF\May\"
For i = 2 To lr
    ws2.Range("E7").Value = ws1.Range("A" & i).Value
    Worksheets(Array("Summary", "WIP Table", "AR Table")).Select
    myFileName = myPath & ws1.Range("A" & i).Value & " - " & MonthName(Month(Date))
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFileName, quality:=xlQualityStandard _
    , includedocproperties:=True, ignoreprintareas:=False, openafterpublish:=True 'change openafterpublish to false if you don't want the pdf to open
    ws1.Activate 'this line just selects the first sheet to un-select the group of 3... keeping those selected was causing other code to write to "Summary" when it should be writing to other sheets.
    Call jw01mailer(myFileName & ".pdf", ws1.Range("B" & i).Value, ws1.Range("A" & i).Value, Date)
Next i

End Sub

Function jw01mailer(myFile As String, toMail As String, recipient As String, myDate As Date)

Dim Oapp As Object
Dim Omail As Object
Dim myMsg As String

'string below is the body of the email, using html tags for formatting, so each <br> is a line break
myMsg = "<p style=""font-size:11pt"">Hello " & recipient & "," & "<br><br>" & "Attached is you performance dashboard that shows you Key " _
& "Performance Indicators (KPIs) as at FY " & Format(myDate, "mmmm, yyyy") & ", which shows the following KPIs:" & "<br>" & _
"1. ABC" & "<br>" & " 2. ABC" & "<br>" & "3. ABC" & "<br>" & "4. ABC" & "<br><br>" & _
"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." & _
"<br><br>" & "If you have any questions or feedback related to your report of the KPIs, please do not hesitate to reach out to me.</p>"

Set Oapp = CreateObject("Outlook.Application")
Set Omail = Oapp.createitem(0)
With Omail
    .display 'code works without this, but if you take it out outlook won't have the signature in the email
    .to = toMail
    .Subject = recipient & " TK Performance overview – " & Format(myDate, "mmmm, yyyy")
    .htmlbody = myMsg & .htmlbody
    .attachments.Add myFile
     '.send 'enable this line once you are satisfied the email is building properly
End With
Set Omail = Nothing
Set Oapp = Nothing

End Function

[Arkadi]

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

[jw01]

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

[jw01]

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

Function jw01mailer(myFile As String, toMail As String, recipient As String, myDate As Date)

Dim Oapp As Object
Dim Omail As Object
Dim myMsg As String

'string below is the body of the email, using html tags for formatting, so each <br> is a line break
myMsg = "<p style=""font-size:11pt"">Hello " & recipient & "," & "<br><br>" & "Please find attached your performance dashboard detailing Key Performance Indicators (KPIs)" _
& " FY " & Format(myDate, "mmmm, yyyy") & "." & "<br>" & _
"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)" & "<br>" & " 2. Non-Billable Hours:  Non-billable hours broken down by category" & "<br>" & _
"3. Collections by month with a comparison to prior year (variance in $ and %)" & "<br>" & "4. Average days to close time (Summary tab)" & "<br><br>" & _
"<br>" & "If you have any questions/comments please contact me. Thank you.</p>"

Set Oapp = CreateObject("Outlook.Application")
Set Omail = Oapp.createitem(0)
With Omail
    .display 'code works without this, but if you take it out outlook won't have the signature in the email
    .To = toMail
    .Subject = recipient & " - TK Performance overview – " & Format(myDate, "mmmm, yyyy")
    .htmlbody = myMsg & .htmlbody
    .attachments.Add myFile
    .send 'enable this line once you are satisfied the email is building properly
End With
Set Omail = Nothing
Set Oapp = Nothing

End Function

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?

[Arkadi]

See if this works....

Function jw01mailer(myFile As String, toMail As String, recipient As String, myDate As Date)

Dim Oapp As Object
Dim Omail As Object
Dim myMsg As String

'string below is the body of the email, using html tags for formatting, so each <br> is a line break
myMsg = "<p style=""font-size:11pt"">Hello " & recipient & "," & "<br><br>" & "Please find attached your performance dashboard detailing Key Performance Indicators (KPIs)" _
& " FY " & Format(myDate, "mmmm, yyyy") & "." & "<br><br>" & _
"Top Section:" & "<br><br>" & _
"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)" & "<br><br>" & " 2. Non-Billable Hours:  Non-billable hours broken down by category" & "<br><br>" & _
"3. Collections by month with a comparison to prior year (variance in $ and %)" & "<br><br>" & "4. Average days to close time (Summary tab)" & "<br><br>" & _
"Graph Section:" & "<br><br>" & "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" & "<br><br>" & _
"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" & "<br><br>" & _
"3. Realization on collections - this graphs details the total slippage (amounts lost) from the value of docketed time to collected time." & "<br>" & _
"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" & "<br>" & _
"b)  Rate Discount - additional discounts given due to client arrangements" & "<br>" & "c)  Fees Worked at Matter Rates - matter rate calculated prior to billing" & "<br>" & _
"d)  Fees Written Up/Down - additional adjustments made" & "<br>" & "e)  Additional Discounts" & "<br>" & "f)  Fees Billed - rate calculated when billing" & "<br>" & _
"g)  Credits - additional discounts after billing" & "<br>" & "h)  Fees Collected - cash received and posted in the accounting system" & "<br>" & _
"i)  Total Slippage - total amount of discounts from the value of docketed time to collected time" & "<br><br><br>" & _
"4. Avg Days to Close Time - displays the average days you close your time versus the firm average (3 days) and your practice group." & "<br><br>" & _
"Fiscal year: July 1 - June 30" & "<br>" & "FYTD: Fiscal year to date" & "<br>" & "PYFYTD: Prior year fiscal year to date" & "<br><br>" & _
"If you have any questions/comments please contact me. Thank you.</p>"

Set Oapp = CreateObject("Outlook.Application")
Set Omail = Oapp.createitem(0)
With Omail
    .display 'code works without this, but if you take it out outlook won't have the signature in the email
    .to = toMail
    .Subject = recipient & " - TK Performance overview – " & Format(myDate, "mmmm, yyyy")
    .htmlbody = myMsg & .htmlbody
    .attachments.Add myFile
    '.send 'enable this line once you are satisfied the email is building properly
End With
Set Omail = Nothing
Set Oapp = Nothing

End Function