Send Report and Attachments in Email

[Pimped]

Hey guys

Scenario: User is looking at a job details. Job has pictures and PDF's as external files saved in the filesystem of the computer. User clicks Email Invoice. System attaches report as a PDF and opens Outlook Email message with email address, subject and body filled and the report PDF as an attachment

I can currently send a report as an attachment in an Outlook Email by using the "DoCmd.SendObject acSendReport" code.

That works nicely for me because it attaches the report I specify but has the limitation that I cannot attach any other objects.

I therefore came across the following code which allows me to add multiple attachments to an Outlook email message:

Sub CreateEmail()

'write the default Outlook contact name list to the active worksheet

Dim OlApp As Object
Dim OlMail As Object
Dim ToRecipient As Variant
Dim CcRecipient As Variant

Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.createitem(olmailitem)

For Each ToRecipient In Array("User 1", "User 2", "User 3")
OlMail.Recipients.Add ToRecipient
Next ToRecipient

For Each CcRecipient In Array("User 4", "User 5", "User 6")
With OlMail.Recipients.Add(CcRecipient)
.Type = olCC
End With
Next CcRecipient

'fill in Subject field
OlMail.Subject = "Test of Outlook email"

'Add the active workbook as an attachment
OlMail.Attachments.Add "C:\Users\User\Pictures\Unspecified\01.jpg"
OlMail.Attachments.Add "C:\Users\User\Pictures\Unspecified\02.jpg"

'Display the message
OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it

End Sub

The problem with the above is that I cannot figure out how to attach a report of my choice. I don't know if there is a way to tell access to open a report and attach it as a PDF.

Worst comes to worst, I'll try to figure out a way to save the report as a PDF and then attach that as one of the attachments.

 

[darbid]

Worst comes to worst, I'll try to figure out a way to save the report as a PDF and then attach that as one of the attachments.

If you use Access only then attaching reports is done for you. If you use Outlook then you will have to do everything manually. This means get your report, make it into a format that you want, save it someone, open your new email, attach your saved document.

As far as I know there is no easy way.

 

[Pimped]

If you use Access only then attaching reports is done for you. If you use Outlook then you will have to do everything manually. This means get your report, make it into a format that you want, save it someone, open your new email, attach your saved document.

As far as I know there is no easy way.

Thanks for the reply mate. At the moment, I am using access to convert the report to PDF and attach it to a new outlook message.

I have seen ways where you can use "OLMail" to attach multiple files to a new outlook message. What I want to do is merge the two techniques so that if the user clicks a button, the report is converted to PDF and then attached along with pictured associated to the "job". The only way I can think of is that if the report is created as a PDF and saved to disk first. But I was hoping to have a PDF created and attached "on the fly" rather than save to disk.

 

[darbid]

What I want to do is merge the two techniques so that if the user clicks a button, the report is converted to PDF and then attached along with pictured associated to the "job". The only way I can think of is that if the report is created as a PDF and saved to disk first. But I was hoping to have a PDF created and attached "on the fly" rather than save to disk.

Words like "along with pictured associated to the "job"" or "But I was hoping to have a PDF created and attached "on the fly" rather than save to disk" confuse me.

In my opinion "on the fly" has nothing to do with saving to disk. A program that does something on the fly must "save" it work somewhere.

If you have a report on your screen and a button to email it as a pdf the only steps I know of are as follows;

1. Some 3rd Party program creates a .pdf file of this report
2. On creation it must be saved somewhere
3. MSAccess "connects" to Outlook and tells outlook to create a mail object
4. MSAccess tells the mail object it wants to add attachments which are save .....
5. Attachements are added and mail is sent.
6. Clean up where attachements were saved by deleting them.

To me "on the fly" means that instead of a button "make pdf" and a second button "send email" with a choice to add files. You have one button which the user clicks and all is done for the user. But there is no magic, no iCloud magic disk in thin air, the file has to be saved somewhere inorder to attach it.

 

[Pimped]

Words like "along with pictured associated to the "job"" or "But I was hoping to have a PDF created and attached "on the fly" rather than save to disk" confuse me.

In my opinion "on the fly" has nothing to do with saving to disk. A program that does something on the fly must "save" it work somewhere.

If you have a report on your screen and a button to email it as a pdf the only steps I know of are as follows;

1. Some 3rd Party program creates a .pdf file of this report
2. On creation it must be saved somewhere
3. MSAccess "connects" to Outlook and tells outlook to create a mail object
4. MSAccess tells the mail object it wants to add attachments which are save .....
5. Attachements are added and mail is sent.
6. Clean up where attachements were saved by deleting them.

To me "on the fly" means that instead of a button "make pdf" and a second button "send email" with a choice to add files. You have one button which the user clicks and all is done for the user. But there is no magic, no iCloud magic disk in thin air, the file has to be saved somewhere inorder to attach it.

You're right. When I say on the fly, I mean that it creates a pdf temporarily to attach to an email and then discard it later.

This is the scenario:

The user is in a form which is used to build up the elements of an invoice (job form). They can then click "print invoice" or "email invoice". If they click print, access opens up the report for them to view and then print using access's menu system. At the moment, if they click "email invoice", access will run the "DoCmd.SendObject acSendReport" code. This allows me to specify the recipient, the subject and which report I want to attach to the email as a PDF.

Hope it makes sense what I'm doing currently so far.

In the Job form, the user can attach pictures, pdf's and any file that is related to the job. When the user adds an attachment, that file is copied to a specific place on the filesystem so that all attachments are in one folder and are named according to the job.

What I want is that when the user clicks "email invoice", access should check the attachments table to see if there are any attachments for the current job and then convert a given report to pdf and attach it to a new email message along with any attachments (by way of using a loop) to the same email message.

At the moment, it seems like that I will have to convert to pdf and save to a specific location, then attach all the files and then either leave the PDF once the email has been sent or delete the file (but that may endanger the attachment because if the email has not yet been sent and the attachment is deleted, then the email will suffer).

 

[darbid]

What I want is that when the user clicks "email invoice", access should check the attachments table to see if there are any attachments for the current job and then convert a given report to pdf and attach it to a new email message along with any attachments (by way of using a loop) to the same email message.

At the moment, it seems like that I will have to convert to pdf and save to a specific location, then attach all the files and then either leave the PDF once the email has been sent or delete the file

You have got your work cut out for you but you are on the right track.

You can add as manny attachements as you like to an email as long as the email is not too big to be sent. So yes you will have to check for files attached to the inoivce record and if they exists attach each one.

You could create your pdf in the temp directory of the operating system (make sure you clear it for the name you will use) - or you can create your own temp directory.

(but that may endanger the attachment because if the email has not yet been sent and the attachment is deleted, then the email will suffer).

Try it but I dont think so. Once you have attached it to the email, Outlook has created its own copy. But test this.

 

[Pimped]

You have got your work cut out for you but you are on the right track.

You can add as manny attachements as you like to an email as long as the email is not too big to be sent. So yes you will have to check for files attached to the inoivce record and if they exists attach each one.

You could create your pdf in the temp directory of the operating system (make sure you clear it for the name you will use) - or you can create your own temp directory.


Try it but I dont think so. Once you have attached it to the email, Outlook has created its own copy. But test this.

very good point about the email size. Potentially, the email could be 100s of megabytes if the user decides to go on an attachment spree! Might be a good idea to allow the user to check which files they want to send along with the email before they click "email invoice".

Thanks for the tips

 

[Pimped]

I've hit a problem already

When I try to run the following code, I get the error: Runtime error 2051, The OutputTo action was canceled.

DoCmd.OutputTo acOutputReport, "Remittance", acFormatPDF, "C:\filename.pdf", True

I've tried False at the end. I've also tried set the filename as follows and place it in the code:

filename = "C:\" & "Remittance" & ".pdf"

What could be the problem? The report opens properly and if I use the following code, it works properly, makes a pdf and attaches it to the email:

DoCmd.SendObject acSendReport, "Report name", acFormatPDF, "To", , , "Subject", "Message text"

I have tried the outputto code using different reports and still no luck. Where could I be going wrong?

It's Office 2010