Results 1 to 11 of 11
  1. #1
    Kipandrews06 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    54

    Sending email from form with report attachment

    I am having a hard time figuring out how to get a report attachment in an email to only attach the record submitted through a form I have. For instance I have a form called "Contractor Orders". On the form I have a command button that sends an email to the appropriate person. In the email, I want it to attach a report called "Contractors_ER" of the current record I'm on. Currently, it attaches the report but it's for all the records, not for a single record I want to send. I'm pretty new to access and very new to VBA, I've basically been watching videos online and researching what I can and self teaching myself, but I'm just still clueless how to do alot of stuff. So right now I have a macro on the command button that uses EmailDatabaseObject. I haven't figured out or understand yet how to attach stuff in emails using VBA. That would have been my first preference so I could customize the email body message, but the file path you have to have is incredibly confusing to me. I have no clue where the report files for each record get saved to in access. So using the macro was easier for me because it just let me select the report to attach and I didn't have to specify any file path or anything. But when it attaches the report it's always for every record and I can't figure out how to get it to sort to that specific record I'm on in the Contractor Orders form. Any help would be greatly appreciated!

    Thanks,


    Kip M.

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Here is some example vba that demonstrates how to save a *filtered* report as a pdf: https://codekabinett.com/rdumps.php?...docmd-outputto

    Here is an example from user Sorceri of how to send an email using outlook from vba: https://stackoverflow.com/questions/...hrough-outlook

    Here is another example from user Parfait of how to send an email but also add an attachment: https://stackoverflow.com/questions/...-as-attachment

  3. #3
    Kipandrews06 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    54
    Thank you very much! That is definitely helpful! I will check out all those examples and play around with it. I appreciate the help!

  4. #4
    Kipandrews06 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    54
    I checked the links out and I was able to get it to generate an email with the report attachment with vba (instead of the macro I was using), but I still can't figure out how to get the report to only attach a certain record and not all of them. I have a form called Contractor Orders and on the form I have an "email order" control button. I'd like to have the report that's attached only show whatever record I'm on in the form that I submitted. For instance, if I'm on contractor order #8, I'd only like it to send an attachment of the contractor order #8 record in the report. Is this possible? I followed the links in the above thread and it only shows how to filter certain data from fields in a report but it doesn't show you how to filter the record source ID for the report from the form. Any help would be greatly appreciated!

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If you do not want to save the report as PDF first you can make a copy of the report (called Contractors_ER_Current) and modify the report's record source by adding Forms![Contractor Orders]![Order #] to the criteria row for the [Order #] field (note that by using spaces and special characters in the field and object names you need to enclose them in square brackets). Now simply use Docmd.SendObject methid in VBA using the new repot instead of the original one with all records.

    Otherwise you need to open the report filtered (using the WhereCondition parameter of the Docmd.OpenReport method) for the current order number, then use Docmd.OutputTo method to export the newly opened report to PDF and finally attach it to the email message.

    Please post your code if you get stuck!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Kipandrews06 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    54
    @Gici, I do want to send it as a pdf. Does the report have to be opened to filter it to the record source? My code now attaches the report without having to open it, I'd like to not have it opened if possible because other users will be using this and they don't need to see it since they are the ones who are submitting the report from the form that they are sending. My current code is below.

    Code:
    Private Sub Command46_Click()Dim oApp As New Outlook.Application
    Dim oEmail As Outlook.MailItem
    Dim fileName As String, todayDate As String
    
    
    'Export report in same folder as db with date stamp
    todayDate = Format(Date, "MMDDYYYY")
    fileName = Application.CurrentProject.Path & "\Contractor_ER_" & todayDate & ".pdf"
    DoCmd.OutputTo acReport, "Contractor_ER", acFormatPDF, fileName, False
    
    
    'Email the results of the report generated
    Set oEmail = oApp.CreateItem(olMailItem)
    With oEmail
        .Subject = "Contractor Expense Report"
    'Adding HTMLbody text to email
        .HTMLBody = "<BODY style=font-size:12pt;font-family:Calibri>A new contractor order has been submitted and is ready for you to review.</BODY>" & _
                    "<BODY style=font-size:12pt;font-family:Calibri>Please print attachment off for your records and login into WORCS to review the order.</BODY>" & _
                    "" & "<br>" & "<br>" & _
                    "<BODY style=font-size:12pt;font-family:Calibri>Sincerely,<br></BODY>" & "<br>" & _
                    "<BODY style=font-size:11pt;font-family:Bahnschrift><b>WORCS_Admin<br></BODY>" & _
                    "<BODY style=font-size:11pt;font-family:Cavolini><i>CompanyName</BODY>"
    
    
        .Attachments.Add fileName
        .Display
        
    End With
    
    
    End Sub

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Docmd.SendObject allows you to send it as PDF as well. What I meant is if you intend to save those PDF files on your network for future use, audits, etc. If you don't need them then having a new report that includes just the current order is easiest. But if you do want to save them as implied in your existing code please try this updated one; the report gets opened hidden (to apply the filter), exported then closed:
    Code:
    Private Sub Command46_Click()
    Dim oApp As New Outlook.Application
    Dim oEmail As Outlook.MailItem
    Dim fileName As String, todayDate As String
    
    
    
    
    'Export report in same folder as db with date stamp
    todayDate = Format(Date, "MMDDYYYY")
    fileName = Application.CurrentProject.Path & "\Contractor_ER_" & todayDate & ".pdf"
    
    
    DoCmd.OpenReport "Contractor_ER", acViewPreview, , "[Order #] = " & Me.[Order #],acHidden 'adjust this line accordingly to match your field and control names 
    DoCmd.OutputTo acReport, "Contractor_ER", acFormatPDF, fileName, False
    DoCmd.Close acReport, "Contractor_ER"  'close the hidden report
    
    
    'Email the results of the report generated
    Set oEmail = oApp.CreateItem(olMailItem)
    With oEmail
        .Subject = "Contractor Expense Report"
    'Adding HTMLbody text to email
        .HTMLBody = "<BODY style=font-size:12pt;font-family:Calibri>A new contractor order has been submitted and is ready for you to review.</BODY>" & _
                    "<BODY style=font-size:12pt;font-family:Calibri>Please print attachment off for your records and login into WORCS to review the order.</BODY>" & _
                    "" & "<br>" & "<br>" & _
                    "<BODY style=font-size:12pt;font-family:Calibri>Sincerely,<br></BODY>" & "<br>" & _
                    "<BODY style=font-size:11pt;font-family:Bahnschrift><b>WORCS_Admin<br></BODY>" & _
                    "<BODY style=font-size:11pt;font-family:Cavolini><i>CompanyName</BODY>"
    
    
    
    
        .Attachments.Add fileName
        .Display
        
    End With
    
    
    
    
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Kipandrews06 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    54
    Ok, where I'm getting stuck is how to reference it to filter to the record I'm on. On the line....."[Order #] = " & Me.[Order #], do I need to list the form name? Because I will be emailing the report from a form and want the record I'm on in the form to correspond to the record generated in the attached report in the email. my form name is called "Contractor Orders", would I need to do something like "[Contractor Orders] = " .....then something about the record source or current ID? I'm just not sure how to specify that. I don't know how to adjust the [order #] like you listed because I want it to correspond with the form record. Does that make sense or am I explaining it badly? I'm really sorry, I'm still pretty new to access and just trying to learn on the fly and figure this out.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    That is what Me. does, it replaces the long reference to the form (Forms![Contractor Orders].).
    So for the where condition you need to look at the field that contains the unique ID you want to filter on (I assumed it was called Order #) and make it equal to the value in the form's current record ( again I assumed you have a text box on the form called Order # that is bound to the Order # field) - you need to adjust thus to fit your names.
    Cheers,

  10. #10
    Kipandrews06 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    54
    @Gicu, that worked perfectly! It is filtering just how I want it now, attaching to the email and everything. Thank you very much for your help! I've been stuck on that for days haha.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Great job, good luck with your project!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-23-2015, 02:13 PM
  2. sending email with attachment of certain file.
    By joshynaresh in forum Access
    Replies: 1
    Last Post: 02-13-2014, 05:16 AM
  3. Sending email with an attachment
    By jle0003 in forum Access
    Replies: 3
    Last Post: 12-19-2012, 12:43 PM
  4. Sending Report from Access as attachment in Email
    By taimysho0 in forum Programming
    Replies: 16
    Last Post: 02-09-2012, 12:07 PM
  5. Replies: 0
    Last Post: 07-06-2010, 08:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums