Automating Email and PDF Creation, then Attaching files

[timotei]

OK - I've searched around and found MANY helpful hints on this board that I'm going to play around with. Wondering if anybody can help me put this all together...

Follow me on this one - it's really a very basic program I'm putting together to notify employees and managers of their phone bills every month...

I have a TABLE of employees, their managers and their cellphone number. (employee, employee_email, phone_number, manager, manager_email)

I have a linked TABLE (tied by phone number), to their monthly cellphone bill (phone_number, provider, bill_date, monthly_charge, air_charge, other_charge, tax_charge, total).

I pull it all together with a query (for example - [provider] = "Verizon" AND [bill_date] = #02/02/2002#

I want to do THREE things with that query data:

1) Dump their billing data to an Excel file, with a filename I define ([phone_number]&"_"&[employee]&"_"&[provider]&"_"&[bill_date]).xls (example: 4085551212_johnsmith_verizon_02_02_2002.xls)

2) Same as above, but run an Access report on the same query and print it to a PDF file, using the same filename format: 4085551212_johnsmith_verizon_02_02_2002.pdf

3) Email the information, looping through the query to pull employee email adress and manager email address. Email TO: Employee CC: Manager SUBJ: Your monthly Cellphone Bill BODY: Here's your latest bill.... (Verizon, 2/02/2002, Total: $xxx.xx) --- AND attach BOTH the Excel file AND the PDF file, based on the employee information for that row in the query.

Any ideas?

Thanks!
Tim.

 

[chassle]

hi tim,
Might have a solution for your 1st issue. Try it out by putting this code in the click event behind a button - for example.

DoCmd.OutputTo acOutputQuery, "Name of your query", acFormatXLS, "C:\temp\" & [phone_number] & "_" & [employee] & "_" & [provider] & "_" & [bill_date] & ".xls"

Let me know if it works
Chassle

 

[eljakim]

To solve your last problem: Just add Outlook to your
module references and use the following code:

Public Sub SendItemsByMail(email As String _
                         , employee As String _
                         , phone As String _
                         , provider As String _
                         , billdate As Date _
                         , grandtotal As Currency)
' By Eljakim ([URL unfurl="true"]www.eljakim.nl)[/URL]
' This subroutine sends an email message with
' two attachments to the provided emailaddress.
Dim strFilename As String
Dim appOutlook As Object
Dim msg As MailItem
strFilename = phone & "_" & employee & "_" & provider & _
              "_" & Format(billdate, "yyyymmdd")
Set appOutlook = CreateObject("Outlook.Application")
Set msg = appOutlook.CreateItem(olMailItem)
msg.To = email
msg.Subject = "Your Monthly Cellphone Bill"
msg.Body = "Hi " & employee & Chr(13) & Chr(10) & _
           "This message contains your lates bill for " & _
           provider & "." & Chr(13) & Chr(10) & _
           "The grand total this month comes to: " & _
           Format(grandtotal, "Currency")
msg.Attachments.Add strFilename & ".xls", olByValue
msg.Attachments.Add strFilename & ".pdf", olByValue
msg.Send
End Sub

Pretty neat, don't you think

Kim

 

[eljakim]

The last missing answer is de PDF creation part...

Either buy Adobe Distiller to generate PDF files, or
use a free utility (

http://www.webxd.com/zipguy/freepdf.htm)

to generate them.

You should now have enough information to finish
the project

If you have any questions, just let me know.

Kim