UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sending Report As Pdf Via Email, Access 2013    
 
   
Psycoperl
post Nov 28 2017, 09:25 AM
Post#1



Posts: 39
Joined: 11-March 15



Good Morning,

Currently I have a report which via VBA we generate PDFs for each client to our local storage for our archival process.

What I would like to do is actually send the report to the client as a pdf via email.

How can I do this?

Thanks
Go to the top of the page
 
doctor9
post Nov 28 2017, 10:24 AM
Post#2


UtterAccess Editor
Posts: 17,902
Joined: 29-March 05
From: Wisconsin


Psycoperl,

You didn't post any details about the code you're using to create the files, so it's hard to give you a specific solution, but here's a general one I've thrown together based on code I found in other discussions here at UtterAccess.

Create a new code module and add this subroutine to it:
CODE
Public Sub SendAtt(strPathAndFile As String, strTo As String, strSubject As String, strBody As String)

    Dim olApp As Object
    Dim olMsg As Object
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMsg = olApp.CreateItem(0)
    
    With olMsg
        .Attachments.Add (strPathAndFile)
        .Importance = 2     '0=Low, 1=Normal, 2=High
        .To = strTo
        .Subject = strSubject
        .Body = strBody
        .Send
    End With
    
    Set olMsg = Nothing
    Set olApp = Nothing

End Sub

This code assumes you are using Outlook for your E-Mail. Inside your code is likely a loop that creates one PDF file for each iteration of the loop. Right after creating the file, add a line of code that calls this new subroutine along these lines:

CODE
Call SendAtt("C:\Users\Psycoperl\OneDrive - Corp\Documents\" & strFilename, strRecipientEMailAddress, "Monthly Report", "The attached file is your monthly report.")

In this example, strFilename is the name of the file you just created. You'll want to change the path as well, of course. strRecipientEMailAddress is a variable where you store the E-Mail address of the client within your loop. If you're working with a Recordset, you can use the field from the recordset here. I've hard-coded the subject line and E-Mail body, and you can, of course, customize these as well.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
theDBguy
post Nov 28 2017, 10:57 AM
Post#3


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Just as another option, you might also look into the SendObject method.

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Psycoperl
post Dec 4 2017, 04:50 PM
Post#4



Posts: 39
Joined: 11-March 15



Thank you for the feed back

This is how I am creating the individual reports:

CODE
    RS.Open sqlReportsList, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
    Debug.Print RS.RecordCount
    
    If RS.RecordCount <= 0 Then
        MsgBox "No records found for this session.", vbInformation, ATBAS_AppName
        RS.Close
        Set RS = Nothing
        Exit Sub
    End If
    
    RS.MoveFirst
    
    intTotalRecords = RS.RecordCount
      
    Do Until RS.EOF
        
        vFileName = ""
        vFileName = ATBAS_SAB_Report_Letter_Folder_Path
        vFileName = vFileName & Replace(Trim(RS![SLastName]), " ", "_") & "_"
        vFileName = vFileName & Replace(Trim(RS![SFirstName]), " ", "_") & "_"
        If Len(Trim(Nz(RS![SMiddleName], ""))) > 0 Then vFileName = vFileName & Replace(Trim(RS![SMiddleName]), " ", "_") & " "
        vFileName = UCase(vFileName) & Replace(Trim(RS![IDNUM]), " ", "_") & "_"
        vFileName = vFileName & Format(RS![TDate], "YYYY-MMDD")
        vFileName = vFileName & ".pdf"
        
        Debug.Print vFileName
        
        DoCmd.OpenReport rReport, acViewPreview, , "[AppointmentID]=" & RS![AppointmentID] & " AND [IDNUM]='" & RS![IDNUM] & "' AND [SessionID]=" & RS![SessionID] & " AND [RecordID]=" & RS![RecordID] ', acHidden
        DoCmd.OutputTo acOutputReport, rReport, acFormatPDF, vFileName, , , , acExportQualityPrint
        
        DoCmd.Close acReport, rReport
        
        
        RS.MoveNext
        DoEvents
    Loop
    RS.Close
    Set RS = Nothing
    status ""
    
    MsgBox "Process Complete", vbOKOnly, ATBAS_AppName



Is there a way to format the body of the email message so that it would be like:

Dear XXXXX
Please find the requested report attached.
If you have any questions, please contact ###-###-####
Thank You,
John Smith
Go to the top of the page
 
theDBguy
post Dec 4 2017, 04:58 PM
Post#5


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


Hi John,

The code you posted doe not send the report out as email. Whether you use the code Doctor9 posted or the SendObject method, you can format the "Body" property or argument to say whatever message you want in the body of the email.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Psycoperl
post Dec 4 2017, 05:02 PM
Post#6



Posts: 39
Joined: 11-March 15



I was posting how we are creating the PDF as Doctor9 appeared to have asked.

I had not moved to sending email reports yet as that is a process where I will need some time to work on, I am currently in planning stages and was trying to see how to do things so that I can progress from there.

Thanks
Go to the top of the page
 
doctor9
post Dec 4 2017, 05:20 PM
Post#7


UtterAccess Editor
Posts: 17,902
Joined: 29-March 05
From: Wisconsin


Psycoperl,

To be clear, my suggestion has nothing to do with creating the PDF - I assumed you already had that part working. My post was only addressing the "how do I send a PDF as an attachment to an E-Mail" aspect of your process.

Do you need help creating the files or not? If you already have code that creates the files, feel free to post it if you need help adding the line of code that attaches the file you've created. If you are NOT able to create the files at this time, let us know, and we can get you started on that part first.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
theDBguy
post Dec 4 2017, 05:52 PM
Post#8


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


Hi John,

Thanks for the clarification. The SendObject method can auto-create the PDF for you, so you won't need to procedure anymore, if you use the SendObject method. However, since you're filtering the report before creating a PDF, you'll have to modify your report, so it auto-filters itself, if you decide to use the SendObject method.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 04:05 AM