asked on

Attach a report as pdf to an email

I have the below mail that opening up an outlook email to be sent out. I would like to it to send a report as well as a pdf attachment. The report is titled CustomerInvoicesIndividual.

What coding do I add so it sends the report as an attachment?

Private Sub EmailInvoice_Click()
Dim sSubj As String, sBody As String
sSubj = "We are all set for swim lessons!"

sBody = "<HTML> " & XXXXXX & ", <BR>" _
    & "<P> We are excited to start swim lessons with you this summer! Swim lessons brings excitement, anxiety, fun, fear, and nerves all wrapped up in one. We are thrilled you choose us to guide you through the adventure. We will strive to exceed your expectations in every way. Attached you will see a confirmation of the first package of lessons as we discussed on the phone.</P>" _
    & "<P> XXXX is your account manager who will guide you through your swim lessons experience. XXX is excited to help you with any schedule issues, questions, concerns or even if you just want to chat. The direct line to meet XXXX is XXX-XXX-XXXX. XXX will be giving you a call for an introduction in the near future. </P>" _
    & "<P> Visit us on social media! Great place to get to know us and post some of your own. Interested in an easy way to get two free days of swim lessons? Earn an extra swim lesson for each of the below:<P>" _
    & "<UL><LI>6 Posts to Facebook or Instagram: Make one post for everyday of swim lessons and you will receive one free lesson! Make sure to use #XXXXXX and tag us @XXXXXX.</LI>" _
    & "<LI>Refer 5 Friends:  Simply refer 5 friends for swim lessons and receive one free lesson. Fast and easy! Click this link for referral sheet: XXXXXXXXXXXXXXXXXX</LI></UL></P>" _
    & "<P>We would like to thank you again for choosing Big Arms Swimmers to guide you through your swim lesson adventure. We have been successfully helping families just like yours since the summer of 2000. You are in good hands! <P>" _
   & "Have a great summer day,<BR><BR> <BR>Big Arms Swimmers Team<BR>" _
   & "XXX-XXX-XXXX<BR> <BR>" _
    & "<a href=http://www.XXXXXXX.com>www.XXXXXX.com</a><BR>" _
     & "<a href=http://www.Facebook.com>www.Facebook.com/XXXXXXXXX</a>. </HTML>"
    
Set objOutlook = CreateObject("Outlook.Application")
        Set objEmailMessage = objOutlook.CreateItem(olMailItem)
        objEmailMessage.To = Me.Parent.Parent.[Email]
        objEmailMessage.Subject = sSubj
        objEmailMessage.htmlBody = sBody & vbCrLf & vbCrLf

        objEmailMessage.Display


End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

<No points wanted>
If you are using Access 2003 or older, ...you can use the info in this link to create the PDF.
http://www.lebans.com/reporttopdf.htm
Then use code like Scott posted to send the PDF as an email.

And here is another popular link on sending email attachments
https://support.microsoft.com/en-us/kb/161088
It also shows how to set other email options such as BCC (Blind Carbon Copy) and setting the email priority level

JeffCoachman

cansevin

ASKER

Thanks Scott... any chance you can show me where that goes in the current code I posted? If I have more guidance as to where it goes, I can definitely work with that.

Thanks for your help, much appreciated!

Chris

Scott McDaniel (EE MVE )

You can put it at the top of your code - doesn't really matter, you just need to create it before you create the email message.

Helen Feddema

Here is some code for saving a filtered report and mailing it as a PDF:

Private Function SendPDFReport() 
'Created by Helen Feddema 17-Jan-2010 
'Last modified 17-Jan-2010 
 
On Error GoTo ErrorHandler 
 
   Dim appOutlook As New Outlook.Application 
   Dim itm As Outlook.MailItem 
   Dim strFileName As String 
   Dim rpt As Access.Report 
   Dim strReport As String 
   Dim strCurrentPath As String 
   Dim strFileNameAndPath As String 
    
   strCurrentPath = Application.CurrentProject.Path & "\" 
   strReport = "rptProductPrices" 
   strFileName = "Product Prices.pdf" 
   strFileNameAndPath = strCurrentPath & strFileName 
    
   'Output report to PDF in current path 
   DoCmd.OutputTo objecttype:=acOutputReport, _ 
      objectname:=strReport, _ 
      outputformat:=acformatpdf, _ 
      outputfile:=strFileNameAndPath, _ 
      autostart:=False 
    
   'Create new mail message and attach text file to it 
   Set itm = appOutlook.CreateItem(olMailItem) 
   With itm 
      .To = "someone@xyz.com" 
      .Subject = "Daily report" 
      .Body = "Your message" 
      .Attachments.Add strFileNameAndPath 
      'To edit before sending 
      .Display 
      'To send automatically 
      '.Send 
   End With 
    
ErrorHandlerExit: 
   Exit Sub 
 
ErrorHandler: 
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description 
   Resume ErrorHandlerExit 
 
End Function

Open in new window

cansevin

ASKER

Thanks All... I think I am being vague with the questions. I'll try to phrase it a different way.

I want to add more text to the body of the email of this code. This code works perfectly attaching as a PDF:

Private Sub EmailInvoice_Click()
  If Dirty Then DoCmd.RunCommand acCmdSaveRecord

On Error GoTo Err_cmdEmailInvoice_Click
 
    Dim stDocName As String
 
    'your report name goes here'
    stDocName = "CustomerInvoicesIndividual"
    'the acFormatRTF can be set to several options including SNP etc'
    DoCmd.SendObject acSendReport, stDocName, acFormatPDF, Me.Parent.Parent.[Email], , , "Swim Lesson Confirmation", "Thank you for choosing XXXXXXX as your swim lesson provider this summer. We are looking forward to a successful summer in the water. Attached you will find a confirmation of your lessons as we discussed. If you have any questions, let us know. Thanks and have a great day!"
    
    'any of the fields in the email above can be substituted with data from your form using Me![your_field]'
 
Exit_cmdEmailInvoice_Click:
    Exit Sub
 
Err_cmdEmailInvoice_Click:
    MsgBox Err.Description
    Resume Exit_cmdEmailInvoice_Click
    
End Sub

Open in new window

I am just not happy with the body of the email. I want to use this as my body of the email. How can I add this to the body?

sBody = "<HTML> " & XXXXXX & ", <BR>" _
    & "<P> We are excited to start swim lessons with you this summer! Swim lessons brings excitement, anxiety, fun, fear, and nerves all wrapped up in one. We are thrilled you choose us to guide you through the adventure. We will strive to exceed your expectations in every way. Attached you will see a confirmation of the first package of lessons as we discussed on the phone.</P>" _
    & "<P> XXXX is your account manager who will guide you through your swim lessons experience. XXX is excited to help you with any schedule issues, questions, concerns or even if you just want to chat. The direct line to meet XXXX is XXX-XXX-XXXX. XXX will be giving you a call for an introduction in the near future. </P>" _
    & "<P> Visit us on social media! Great place to get to know us and post some of your own. Interested in an easy way to get two free days of swim lessons? Earn an extra swim lesson for each of the below:<P>" _
    & "<UL><LI>6 Posts to Facebook or Instagram: Make one post for everyday of swim lessons and you will receive one free lesson! Make sure to use #XXXXXX and tag us @XXXXXX.</LI>" _
    & "<LI>Refer 5 Friends:  Simply refer 5 friends for swim lessons and receive one free lesson. Fast and easy! Click this link for referral sheet: XXXXXXXXXXXXXXXXXX</LI></UL></P>" _
    & "<P>We would like to thank you again for choosing Big Arms Swimmers to guide you through your swim lesson adventure. We have been successfully helping families just like yours since the summer of 2000. You are in good hands! <P>" _
   & "Have a great summer day,<BR><BR> <BR>Big Arms Swimmers Team<BR>" _
   & "XXX-XXX-XXXX<BR> <BR>" _
    & "<a href=http://www.XXXXXXX.com>www.XXXXXX.com</a><BR>" _
     & "<a href=http://www.Facebook.com>www.Facebook.com/XXXXXXXXX</a>. </HTML>"

Open in new window

Scott McDaniel (EE MVE )

The SendObject code does all that stuff for you (i.e. the creating report, etc). As you've found, however, you can't use HTML with SendObject.

So if you use Outlook Automation, you must create the report, output it as a PDF, and then attach that to the email.

The code sample I provided does just that. I've included my mods below:

Private Sub EmailInvoice_Click()
    Dim sSubj As String, sBody As String
    sSubj = "We are all set for swim lessons!"

    sBody = "<HTML> " & XXXXXX & ", <BR>" _
            & "<P> We are excited to start swim lessons with you this summer! Swim lessons brings excitement, anxiety, fun, fear, and nerves all wrapped up in one. We are thrilled you choose us to guide you through the adventure. We will strive to exceed your expectations in every way. Attached you will see a confirmation of the first package of lessons as we discussed on the phone.</P>" _
            & "<P> XXXX is your account manager who will guide you through your swim lessons experience. XXX is excited to help you with any schedule issues, questions, concerns or even if you just want to chat. The direct line to meet XXXX is XXX-XXX-XXXX. XXX will be giving you a call for an introduction in the near future. </P>" _
            & "<P> Visit us on social media! Great place to get to know us and post some of your own. Interested in an easy way to get two free days of swim lessons? Earn an extra swim lesson for each of the below:<P>" _
            & "<UL><LI>6 Posts to Facebook or Instagram: Make one post for everyday of swim lessons and you will receive one free lesson! Make sure to use #XXXXXX and tag us @XXXXXX.</LI>" _
            & "<LI>Refer 5 Friends:  Simply refer 5 friends for swim lessons and receive one free lesson. Fast and easy! Click this link for referral sheet: XXXXXXXXXXXXXXXXXX</LI></UL></P>" _
            & "<P>We would like to thank you again for choosing Big Arms Swimmers to guide you through your swim lesson adventure. We have been successfully helping families just like yours since the summer of 2000. You are in good hands! <P>" _
            & "Have a great summer day,<BR><BR> <BR>Big Arms Swimmers Team<BR>" _
            & "XXX-XXX-XXXX<BR> <BR>" _
            & "<a href=http://www.XXXXXXX.com>www.XXXXXX.com</a><BR>" _
            & "<a href=http://www.Facebook.com>www.Facebook.com/XXXXXXXXX</a>. </HTML>"

    Dim sPath  As String
    sPath = "C:\PDFFiles\Invoice.pdf"

    If Dir(sPath) <> "" Then
        Kill sPath
    End If

    DoCmd.OpenReport "CustomerInvoicesIndividual", acViewPreview, , , acHidden
    DoCmd.OutputTo acOutputReport, "CustomerInvoicesIndividual", acFormatPDF, sPath, True
    DoCmd.Close acReport, "CustomerInvoicesIndividual"

    Set objOutlook = CreateObject("Outlook.Application")
    Set objEmailMessage = objOutlook.CreateItem(olMailItem)
    objEmailMessage.To = Me.Parent.Parent.[Email]
    objEmailMessage.Subject = sSubj
    objEmailMessage.htmlBody = sBody & vbCrLf & vbCrLf
    objEmail.Attachments.Add sPath
    objEmailMessage.Display
End Sub

Open in new window

If you need to filter that report, and send an invoice for ONLY the person in the subject line, then you'll have to do things a bit differently. If you need to do that, you'd need to create a filtered report, or use the WHERE clause of the OpenReport method:

DoCmd.OpenReport "CustomerInvoicesIndividual", acViewPreview, , "YourIDField=" & YourIDValue, acHidden

Where "YourIDField" is the field in the report that "points" to the correct value, and "YourIDVAlue" is the value that would identify that specific Customer Invoice.

cansevin

ASKER

Thanks! Got it to work.

Helen Feddema

Here is some code for creating an HTML email:

Public Sub CreateEmails()
'Created by Helen Feddema 31-Jan-2010
'Last modified by Helen Feddema 7-Feb-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim dtePickup As Date
   Dim dteSold As Date
   Dim msg As Outlook.MailItem
   Dim rstAll As DAO.Recordset
   Dim rstSingle As DAO.Recordset
   Dim strBody As String
   Dim strCompany As String
   Dim strEMail As String
   Dim strFCFNumber As String
   Dim strGrade1 As String
   Dim strGrade2 As String
   Dim strHeader As String
   Dim strHTMLBody As String
   Dim strLine1 As String
   Dim strLine2 As String
   Dim strNotes As String
   Dim strQueryAll As String
   Dim strQuerySingle As String
   Dim strSignature As String
   Dim strSubject As String
   
   Set dbs = CurrentDb
   strQueryAll = "qrySendEmails"
   Set rstAll = dbs.OpenRecordset(strQueryAll)
   strQuerySingle = "qryTempEmail"
   strSignature = "</table><br><br><font face='Book Antiqua', size=5>" _
      & "<align='left'><u>John Doe</u></font><br>" _
      & "<font face='Arial', size=3><br>" _
      & "VP of Sales and Marketing<br>" _
      & "JD Inc.<br>" _
      & "Ph: 555-112-9601<br>" _
      & "Fx: 555-112-9422<br>"
   Debug.Print "Signature: " & strSignature
   
   Do While Not rstAll.EOF
      'Create filtered recordset for this customer
      lngSupplierID = Nz(rstAll![CustomerID])
      
      If lngSupplierID <> 0 Then
         strSQL = "SELECT * FROM " & strQueryAll & " WHERE " _
            & "[CustomerID] = " & lngSupplierID & ";"
      End If
   
      Debug.Print "SQL for " & strQuerySingle & ": " & strSQL
      lngCount = CreateAndTestQuery(strQuerySingle, strSQL)
      Debug.Print "No. of items found: " & lngCount
      If lngCount = 0 Then
         strPrompt = "No records found; canceling"
         strTitle = "Canceling"
         MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
         GoTo ErrorHandlerExit
      Else
         'Create starter HTML text for this customer
         strHTMLBody = ""
         strHeader = "<p><img border='0' src='Image.jpg' " _
	    	& "width='559' height='120'></p> & "<font face='Arial', size=3>" _
            & "Please schedule the following: <br><br>" _
            & "<table width='791' border='1'>" _
            & "   <tr>" _
            & "      <td width='283'height='26' nowrap valign='bottom'" _
            & "align='left'><font face='Arial', size=3><strong>Grade</strong></font></td>" _
            & "      <td width='127' height='26'nowrap valign='bottom'" _
            & "align='left'><font face='Arial', size=3><strong>FCF Pickup #</strong></font>" _
            & "      <td width='144'height='26' nowrap valign='bottom'" _
            & "align='left'><font face='Arial', size=3><strong>Pickup Date</strong></font></td>" _
            & "      <td width='237'height='26' nowrap valign='bottom'" _
            & "align='left'><font face='Arial', size=3><strong>Notes</strong></font></td>" _
            & "   </tr>"

         Set rstSingle = dbs.OpenRecordset(strQuerySingle)
         strEMail = Nz(rstSingle![EmailAddress])
         strCompany = Nz(rstSingle![strCompany])
         strSubject = "Loads for " & strCompany
         
         'Create email for this customer
         Set msg = appOutlook.CreateItem(olMailItem)
         msg.To = strEMail
         msg.Subject = strSubject
         msg.BodyFormat = olFormatHTML
         
         'Process loads per customer
         Debug.Print "Processing load(s) for " & strCompany
         strBody = ""
         
         Do While Not rstSingle.EOF
            strLine1 = ""
            strLine2 = ""
            strGrade1 = ""
            strGrade2 = ""
            strSlipNo = Nz(rstSingle![SlipNo])
            dteSold = Nz(rstSingle![DateSold])
            dtePickup = Nz(rstSingle![ScheduledPickup])
            strFCFNumber = Nz(rstSingle![FCFNumber])
            strGrade1 = Nz(rstSingle![Grade1])
            Debug.Print "Grade 1: " & strGrade1
            strGrade2 = Nz(rstSingle![Grade2])
            Debug.Print "Grade 2: " & strGrade2
            strNotes = Nz(rstSingle![Notes])
            
            'Create line of body text
            strLine1 = "<font face='Arial', size=3" _
               & "   <tr>" _
               & "      <td width='283'valign='bottom' align='left'>" & strGrade1 & "</td>" _
               & "      <td width='127' valign='bottom' align='left'>" & strFCFNumber _
               & "      <td width='144'valign='bottom' align='left'>" _
               & Format(dtePickup, "m/d/yyyy") & "</td>" _
               & "      <td width='237'valign='bottom' align='left'>" & strNotes & "</td>" _
               & "   </tr></font>"
            Debug.Print "Line 1: " & strLine1
            
            If strGrade2 <> "" Then
               strLine2 = "<font face='Arial', size=3>" _
                  & "   <tr>" _
                  & "      <td width='283'valign='bottom' align='left'>" & strGrade2 & "</td>" _
                  & "      <td width='127' valign='bottom' align='left'>" & strFJDNumber _
                  & "      <td width='144'valign='bottom' align='left'>" _
                  & Format(dtePickup, "m/d/yyyy") & "</td>" _
                  & "      <td width='237'valign='bottom' align='left'>" _
                  & strNotes & "</font></td>" _
                  & "   </tr>"
            End If
            Debug.Print "Line 2: " & strLine2
         
            strHTMLBody = strHTMLBody & strLine1 & strLine2
            rstSingle.MoveNext
         Loop
         
         strHTMLBody = strHeader & strHTMLBody & strSignature
         Debug.Print "Message HTML body: " & strHTMLBody
         
         msg.HTMLBody = strHTMLBody
         msg.Display
         
      End If
      rstAll.MoveNext
   Loop
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in CreateEmails procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Also, my recent Access Archon article has information on creating both Plain Text and HTML emails containing data from Access:

http://www.helenfeddema.com/Files/accarch240.zip