Macro to save a report as PDF and Email to Microsoft outlook

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.

Macro to save a report as PDF and Email to Microsoft outlook Fig-1.1

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.

Macro to save a report as PDF and email to Microsoft outlook Fig-1.2

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.

Macro to save a report as PDF and email to Microsoft outlook Fig-1.3

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.

Macro to save a report as PDF and email to Microsoft outlook Fig-1.4

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.

Macro to save a report as PDF and email to Microsoft outlook Fig-1.5

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.

Macro to save a report as PDF and email to Microsoft outlook Fig-1.6

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 = "ARKMicrosystems@ark.com"
item.Subject = "EMail PDF"
item.Body = " "
item.Attachments.Add namePath
item.Display
End With
End Function


DISCLAIMER

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. Access Guru 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.