[kmxs]

Trying to figure out how create a 1 click button to generate a PDF and auto-email it to a customer based on an email stored in a field on the customers table.

I can generate PDF's to save them but I am not sure how to have it auto-email. Can anyone point me in the right direction on this or provide a snippet of VBA that you've used to do something similar? Thanks.

[kmxs]

Trying to use this code

DoCmd.SendObject acSendReport, "rptPDFWorkOrder", acFormatPDF, "someemail@gmail.com", "", "", "test invoice email", "test invoice message text", True, ""

but it's saying variable not defined for acFormatPDF.

This post has been edited by kmxs: 30 June 2011 - 09:51 AM

[kmxs]

Hmm after some more research I've found that acFormatPDF is not available in access 2003. Any other solutions I am seeing seem to require like 50 lines of code. Anyone know an easy way to do this, seems like there should be an easy solution.

[kmxs]

Problem solved. Took quite a bit to get through it given Access 2003 doesn't make it easy when it comes to PDF. I had originally used Outlook to send the mail but I converted over to SMTP because Outlook through up to many security check boxes and I figured that was inconvenient to have to click through every time. I'll post the code here in case anyone else wants to do something like this. Works like a charm, feel free to ask if you have any questions.

Private Sub cmdEmail_Click()
    Dim strEmail As String
    strEmail = "target@email.com"
    Call generatePDF
    Call SendMessage(strEmail, "C:\tempPDFInvoice.pdf")
    Call DeleteFile("C:\tempPDFInvoice.pdf") ' Doesn't go to Recycle Bin!
    CurrentDb.Execute ("UPDATE Orders SET InvoiceSent=Yes WHERE OrderID=" & txtOrderID.Value)
End Sub
Private Sub generatePDF()
    ' Creates SNP, converts and Converts it to PDF.
    DoCmd.OutputTo acOutputReport, "rptPDFInvoice", acFormatSNP, "C:\tempPDFInvoice.snp", False
    Call ConvertReportToPDF(vbNullString, "C:\tempPDFInvoice.snp", "C:\tempPDFInvoice.pdf", False, False)
    Call DeleteFile("C:\tempPDFInvoice.snp") ' Doesn't go to Recycle Bin!
End Sub

Public Sub SendMessage(strTo As String, strAttachment2 As String)
    'Send using the Port on a SMTP server
    Dim attList() As String
    Dim item As Integer
    Dim iMsg
    Dim iConf
    Dim Flds
    Dim strHTML
    Dim stBody1 As String, stBody2 As String, stSubject As String
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.configuration")
    Set Flds = iConf.Fields
    'Set lfsFile1 = CreateObject("Scripting.FileSystemObject")
    With Flds
        .item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'cdoSendUsingPort
        .item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.yourServer.com" ' make sure to use your server
        'Use SSL to connect to the SMTP server:
        .item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
        .Update
    End With

    If InStr(strAttachment2, ";") Then attList = Split(strAttachment2, ";")

    With iMsg
        Set .Configuration = iConf
        .To = strTo
        '.CC = "target@whateverCC.com"
        .From = "whoever@email.com"
        '.BCC = strBCC
        .Subject = "subject goes here"
        '.HTMLBody = strHTML
        .TextBody = "body goes here"
        '.Nocoverpage: True
        '.confirmsend: true
        If InStr(strAttachment2, ";") Then
            For item = 1 To UBound(attList)
                .addAttachment (attList(item))
            Next
        Else
            If Not IsNull(strAttachment2) And Len(strAttachment2) > 0 Then .addAttachment strAttachment2
        End If
        .Send
    End With
    
    ' cleanup of variables
    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing
End Sub

[pmiroslav]

Hi,
I'm trying to use your code but something is missing.
In your Private Sub generatePDF you Call ConvertReportToPDF and DeleteFile but this is not present in your example.

[kmxs]

pmiroslav, on 21 July 2011 - 05:43 AM, said:

Hi,
I'm trying to use your code but something is missing.
In your Private Sub generatePDF you Call ConvertReportToPDF and DeleteFile but this is not present in your example.

Yeah I was just trying to answer the question I asked before I left the thread. When I head in to work today I can dig out those functions and paste them in here for you.

[kmxs]

I used Lebans for the convertor, you can google it and download it.

'DEVELOPED AND TESTED UNDER MICROSOFT ACCESS 97 through A2003
'
'Copyright: Stephen Lebans - Lebans Holdings 1999 Ltd.


'Distribution:

' Plain and simple you are free to use this source within your own
' applications. whether private or commercial, without cost or obligation, other that keeping
' the copyright notices intact. No public notice of copyright is required.
' You may not resell this source code by itself or as part of a collection.
' You may not post this code or any portion of this code in electronic format.
' The source may only be downloaded from:
' www.lebans.com
'
'Name: ConvertReportToPDF
'
'Version: 7.51

delete file function

Private Declare Function DeleteFile Lib "kernel32.dll" Alias _
    "DeleteFileA" (ByVal lpFileName As String) As Long ' Declaration

[SerialComplainer]

I have a similar but related problem.

I'm using MS Access to generate a report that I want to send out to customers. For each customer, I will have multiple invoices in the databases to be included in the report.

I've seen the 'sendobject' function, and how this can be used to send a PDF. My problem is not with the mechanics of sending an email. It's a bit more conceptual than that.

I have an MS Access report that generates one page for each customer, but I don't want to send this full report to each customer. I just want to send the one page for that customer by email to the customer. What do I need to do to my Access report to make it 'one page with data for this customer only', instead of all customers appearing in the report.

Just for context, I'm a 'point and click' access guy, rather than a code. I can just about follow the VB code example above, but I wouldn't consider myself competent to write VB code - so ideally responses that tell me how I need to restructure my MS Access report would be most welcome.

[June7]

Open the report filtered to the one customer, send email, close report, open report filtered to next customer, etc.

The filter critera can be provided by user selection on form. However, if you have A LOT of customers to email, this is not practical so you want to automate. This requires more CODE. I use only VBA and doubt macro could accomplish.

Open a recordset of the desired customer IDs filtered on appropriate criteria. Loop through the recordset. Within the loop open report filtered to the current customer ID of recordset, send email, close report, move to next record, etc.

Review http://www.accessfor...look-21903.html

P.S. You should have started your own thread on this topic.

This post has been edited by June7: 30 October 2012 - 12:39 PM