Export a Microsoft Access report to PDF using Macro and email using Microsoft Outlook
This article is about to export the report to PDF using Macro and email this report using Microsoft Outlook. This approach is extremely useful to convert report into PDF format whenever we call the Macro.
For this purpose we have to make table and save data in table as shown in Fig 1.1.
And we have to select create Report from the Ribbon for current table. This will change data into report. Now we have to select the design view of the report and add two buttons, one is for save into report and second is for send report to Microsoft Outlook as shown in Fig 1.2.
There is another method also that is directly changing the report to PDF and email directly. We have just select the Email button from Ribbon and select the PDF format or any other format according to requirement. Then after pressing Ok button we are able to send email with PDF file as shown in Fig 1.3.
But we are interested with the help of macro, which is more interested technique. Using button on the report we are embedding the macro on event. After the selection of macro builder on “Save AS PDF” button the macro action window is opened .We has to select “Output To” action with object type report, and output format PDF format as shown in Fig 1.4.
After this we have to test to test 'save as PDF' button. Automatically this Report will change into PDF format as shown in Fig 1.5.
Next task is to send this PDF file to Microsoft Outlook as attachment .So on “send PDF” button we have to write VBA code. That will enable this to send to Outlook as shown in Fig 1.6.
VBA code for Send to Microsoft Outlook:
Option Compare Database Private Sub sendemail_Click() mailPDFReport End Sub Private Function mailPDFReport() Dim MyOutlook As New Outlook.Application Dim item As Outlook.MailItem Dim sReport As String Dim namePath As String Dim fileName As String Dim rpt As Access.Report 'Subjectline$ = InputBox$("Please enter the subject line for this mailing.", "We Need A Subject Line!") sPath = Application.CurrentProject.Path & "\" sReport = "Table1" fileName = "Table1.pdf" 'BodyFile$ = InputBox$("Please enter the filename of the body of the message.", "We Need A Body!") namePath = sPath & fileName DoCmd.OutputTo objecttype:=acOutputReport, objectname:=sReport, outputformat:=acFormatPDF, outputfile:=namePath, autostart:=False Set item = MyOutlook.CreateItem(mItem) With item item.To = "email@example.com" item.Subject = "EMail PDF" item.Body = " " item.Attachments.Add namePath item.Display End With End Function
It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. ERP Makers will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.