Code Error

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Code Error

Post by Leesha »

Hi,
I'm modifying code for auto sending emails that I rec'd from Hans years ago. It appears to be working with the exception of the pdf not being saved for the email to attach it. I'm at a loss.

I stripped down the database to try to upload but keep getting a size error. The code I'm using is:

Code: Select all

Private Sub cmdEmailPrint_Click()
    Dim strWhere As String
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strFilename As String
    Dim outApp As Object
    Dim outMsg As Object
    Dim blnStart As Boolean
    Dim arrNames As Variant
    Dim i As Long

    On Error Resume Next
    ' Try to get running instance of Outlook
    Set outApp = GetObject(Class:="Outlook.Application")
    If outApp Is Nothing Then
        ' If Outlook wasn't running, start it
        Set outApp = CreateObject(Class:="Outlook.Application")
        If outApp Is Nothing Then
            ' We failed to start Outlook, so get out
            MsgBox "We can't start Outlook, sorry!", vbCritical
            Exit Sub
        End If
        ' Set a flag that we started Outlook
        blnStart = True
    End If
    On Error GoTo ErrHandler

     ' Create the where-condition
    strWhere = " WHERE EmailInvoiceSent = False"
    
    strSQL = "SELECT ALL * FROM [tblInvoice AutoTemp]" & strWhere

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    Do While Not rst.EOF
        glngInvoiceID = rst!InvoiceID
        strFilename = gstrPath
        ' Export report to PDF
'        ConvertReportToPDF "rptInvoiceAuto", , strFilename, , False
         'Stops code until invoice is looked at
         DoCmd.OpenReport ReportName:="rptInvoiceAuto", View:=acViewPreview, WindowMode:=acDialog
        ' Ask user for permission to send
        If MsgBox("Do you want to email this invoice?", vbQuestion + vbYesNo) = vbYes Then
            ' Convert report to PDF
         DoCmd.OutputTo acOutputReport, "rptInvoiceAuto", acFormatPDF, strFilename
        
        ' Create a new e-mail message
        Set outMsg = outApp.CreateItem(0) ' olMailItem
        With outMsg
            ' Use the e-mail address field
            'Main Email
            arrNames = Split(rst![Email], ",")
            For i = 0 To UBound(arrNames)
                .Recipients.Add arrNames(i)
            Next i

        
            ' Change the subject as needed
            .Subject = rst![Event Name] & " Invoice "

            ' Change the body text as needed& vbCrLf & vbCrLf & Me.comment3
            .Body = "Please remit payment for the attached invoice" & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & vbCrLf & "Rebecca Turner"
            ' Attach the PDF file
            .Attachments.Add strFilename
            ' Use ONE of the two following lines, not both
            .Send ' to edit the message before sending
        End With

        ' Set EmailFaxSent field to True, and EmailDateTimeSent to Now
        rst.Edit
        rst!EmailInvoiceSent = True
        rst!EmailInvoiceDateSent = Now
        rst.Update
    
  
        ' Optional: delete the PDF file after creating the e-mail
        ' Delete or comment out the next line if you don't want to delete the file
        Kill strFilename
       End If
       
        rst.MoveNext
        Me.lbxInvoice.Requery
    Loop

    ' Optional: requery the list box
      
      'Updates Invoice sent that got missed above
   ' DoCmd.OpenQuery "qryUpdateInvoiceEmailSent"
        'Updates Invoice sent that were skipped to place on hold
   ' DoCmd.OpenQuery "qryUpdateInvoiceEmailSentnot"
        
    
    Me.lbxInvoice.Requery
    

'___________________________________________________________________________________
ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    If blnStart Then
        outApp.Quit
    End If
    Set outMsg = Nothing
    Set outApp = Nothing
    Exit Sub

ErrHandler:
    If Err = 2501 Then
        Resume Next
    Else
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End If


End Sub
The basGlobal module code with the path for the file to be saved to is:

Code: Select all

Option Compare Database
Option Explicit

Public Const gstrPath = "C:\DyslexiaACT\Invoices\"

Public glngInvoiceID As Long
Thanks,
Leesha
Last edited by HansV on 23 Oct 2017, 17:19, edited 1 time in total.
Reason: to add [code]...[/code] tags around the code

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code Error

Post by HansV »

Did you zip the database? The zip file should be 250 KB or less in size.
If it's too large, you can upload it to a cloud service such as Google Drive, OneDrive, DropBox, FileDropper etc., then share the uploaded file, and post a link to the shared file in a reply.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Code Error

Post by Leesha »

Hi Hans!
Yes I did and I even compacted it first but kept getting an error. Here is the link to dropbox.
Thanks!
Leesha

https://www.dropbox.com/s/iesj737u7kzps ... l.zip?dl=0" onclick="window.open(this.href);return false;

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code Error

Post by HansV »

Thanks, that is a big file indeed!

You set the file name for the PDF file in the line

Code: Select all

        strFilename = gstrPath
but gstrPath is - as the name indicates - only a path, it doesn't include a file name. DoCmd.OutputTo fails because of that. You could change it to

Code: Select all

        strFilename = gstrPath & "Invoice" & rst!InvoiceID & ".pdf"
This will produce a file name such as Invoice721.pdf if the InvoiceID is 721. You can change this to suit your preference, of course.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code Error

Post by HansV »

By the way, are you aware that the object in the report footer contributes more than 43 megabytes to the size of the report and of the database?
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Code Error

Post by Leesha »

Thanks Hans! It works perfect and no I was wasn't aware that the object in footer was adding that much in size!! It was beyond me why I couldn't get it smaller!

Leesha

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Code Error

Post by Leesha »

Hi Hans,
The user who will be sending the auto emails out doesn't have a company email address, and they do not want the send email to be their personal email. Is there a way to insert an email in the code for the sender, vs it being automatically put in from the default address in Outlook?
Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code Error

Post by HansV »

It is possible to set the SentOnBehalfOfName property of the email message in the code, but the recipient will still be able to see the real email address of the user.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Code Error

Post by Leesha »

Hi Hans,
I knew if there was a work around you'd know it! I think that will tide this group over till they get their own computer with outlook. It's a small NFP. How would the code I sent earlier be changed to set that up?
Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code Error

Post by HansV »

Below the line

Code: Select all

        With outMsg
insert the following new line

Code: Select all

            .SentOnBehalfOfName = "Jane Roe"
substituting the "on behalf of" name or email address.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Code Error

Post by Leesha »

Hi Hans,
This is what I tried:

' Create a new e-mail message
Set outMsg = outApp.CreateItem(0) ' olMailItem
With outMsg
.SentOnBehalfOfName = "The Dyslexia Society of CT"

This is the error that I get
"The operation failed. The messaging interfaces have returned an unknown error. If the problem persists, restart Outlook. The operation failed. An object cannot be found."

Thanks!
Leesha

JohnH
3StarLounger
Posts: 287
Joined: 09 Mar 2010, 23:16
Location: Canberra Australia

Re: Code Error

Post by JohnH »

Do you have an email addresses associated with the name "The Dyslexia Society of CT" ?

What happens if you replace "The Dyslexia Society of CT" with that email address?
Regards

John

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Code Error

Post by Leesha »

Hi John,
They don't have an email address. The individual volunteers have their own personal ones. I tried putting in a dummy email and it does work with that, so I'm guessing the error was due to it not being an email address.
Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code Error

Post by HansV »

Entering a name probably works only if Outlook recognizes it as the display name of an email address in the Contacts list.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Code Error

Post by Leesha »

Hi Hans and John,
The emails are showing in the sent box of Outlook, however they are never rec'd by the test email address. I have checked the spam folder but they aren't there either. Is there a setting in Outlook that might be preventing them from actually being sent? When I take out the "on behalf of" code they arrive at the test email address.
Thanks!
Alicia

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code Error

Post by HansV »

If the emails hadn't been sent, they would be in the Outbox folder. Since they are in the Sent Items folder, they have actually been sent.

I suspect that the recipient's mail server blocks the email as spam since the sender name doesn't correspond to the actual email address the message was sent from.

So I fear that you can't set the SentOnBehalfOfName...
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Code Error

Post by Leesha »

That is what I suspected too. Oh well, it was worth a shot.
Thanks!
Leesha