none
sequence of events in VBA, Access 2013, WIN10 vs WIN7 RRS feed

  • Question

  • In VBA for Access, I have programmed to produce a "PDF" file and then attach this file to a generated Outlook mail message.
    So the sequence of events is generate PDF and generate Mail
    In the next occasion, the old "PDF" should be replaced and then attached to a new generated outlook mail message.

    In WIN7, this procedure works OK.
    However, in WIN10, the outlook mail is not waiting for the completion of the generation of the "PDF" and will then attach either no file or the "old-wrong" one.

    I have  build a timer. The generation of the mail has to wait for 20 seconds. This is not a nice solution.

    Does anyone have a suggestion for a better solution?

    Friday, January 20, 2017 10:15 AM

All replies

  • We'd need to see your code to be in any position to offer any sort of help.

    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Friday, January 20, 2017 1:39 PM
  • I agree with Daniel. There is too much going on here to troubleshoot without seeing the sequence of events, from generation of the PDF to sending it via e-mail using Outlook automation.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, January 20, 2017 2:04 PM
  • Hello, Thanks for the response. I thought that a general explanation of the case would be sufficient.

    Hereafter, You will see the program lines. I have translated the Dutch comments into English. However some code are still Dutch

    If MsgBox("Do you wish to sent confirmation by Mail?" & vbCrLf _ 'Ask for confirmation of requested action
         & "Save or write-over PDF in directory Atydelyk", vbYesNo, _
        "Orderbevestiging") = vbYes Then

    'print the confirmation to PDF in directory Atydelyk (temporary directory)
        strDefaultPrinter = Application.Printer.DeviceName 'define default printerdevice.
        Set Application.Printer = Application.Printers("gDoc Creator") 'new printerdevice
        StDocPDF = "C:\Atydelyk\" & stDocName & " " & Me.OrderId & ".pdf" 'define name outputfile.
        DoCmd.OpenReport stDocName, , , stLinkCriteria 'print
        Set Application.Printer = Application.Printers(strDefaultPrinter) 'back to default printerdevice.

    'Wait for execution of mail generation. This part has been added for WIN10. Is not needed in Win7
        iWachttijd = 20
        iBegintijd = Time()
        iEindtijd = DateAdd("s", iWachttijd, iBegintijd)
            Do Until Time() > iEindtijd
        Loop

    'Prepare / produce mail
        email_ontvanger = Me.OrderMail 'adressee
        MedeNaam = Me.MedNaam 'sender
        MedeMail = Me.MedMail 'sender mailaddress
        
        Onderwerp = "Bevestiging levertermijn Uw referentie " & Me.OrderRef & ", onze referentie " & Me.OrderId 'Subject line

        If Me!Sex Then 'retrieve name and gender adressee
            Naam = "mevrouw " & Me.OrderANaam
        Else
            Naam = "heer " & Me.OrderANaam
        End If
        html_body = Get_template(CurrentDBDir & "bevestiging termijn.htm")
        html_body = Get_converted_content(html_body, "", Naam, email_ontvanger, MedeNaam, MedeMail)
        Bijlage = "C:\Atydelyk\bevestiging levertermijn.pdf" 'attachment name
        If fs.FileExists(Bijlage) Then
            outlook_mail email_ontvanger, Onderwerp, html_body, Bijlage 'in case there is an attachment
        Else
            outlook_mail email_ontvanger, Onderwerp, html_body 'in case there is no attachment
        End If
    end if

    Under Win7, all works to expectation without the waiting.
    Under Win10 (and now also under Win7), one has to wait.

    Sunday, January 22, 2017 9:05 PM
  • Out of curiosity, what version of Access are you running?

    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Sunday, January 22, 2017 9:16 PM
  • Since you are using a PDF printer to generate the PDF, I think I'd probably create a looping mechanism to check for the existence of the PDF file itself and keep looping until the file is created, then move on to the e-mail part of your code.

    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Sunday, January 22, 2017 9:19 PM
  • Since you are creating the PDF by printing to a file, which can depend upon events running outside of your app, this would appear to be a timing issue. The code to check whether the PDF file exists should be in a loop. You can add a Timer to your loop to provide a graceful exit should the operation fail for some reason. You should be able to use the Timer code you currently have, with some modification.

    You should also add a flag in your code indicating whether the PDF needed to be created. That way you can simply bypass the attachment operation if it was not.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Mike_Lan Monday, January 30, 2017 9:32 PM
    • Unmarked as answer by Mike_Lan Monday, January 30, 2017 9:32 PM
    • Proposed as answer by Chenchen LiModerator Tuesday, January 31, 2017 3:25 AM
    Monday, January 23, 2017 1:47 PM
  • Dear Sirs,

    Thank you very much for your support.
    However, each solution had its disadvantages; checking the filename on a docmd.openreport results in a "non-detectable" filename.

    I have choosen the easiest way for a dummy like me: I reinstalled WIN7. The VBA runs to full expectations.
    Thanks again for your support

    Monday, January 30, 2017 9:37 PM
  • Hi,

    We are glad that you have resolved your issue, I would suggest you mark helpful post or your solution as answer, thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 31, 2017 3:25 AM
    Moderator