Outlook Automation (1 Viewer)

VictorAtWork · Jun 27, 2017

Hello,
We have tried several of the forum replies to no avail with outlook.
Here is our scenario:
We provide certificates of completion to attendees of a classroom course.
We print these certificates using a loop based on the state they live in AND if they have additional certifications.
This all works fine.
We now have our sub sending the reports out to a folder (c:\exportPDF) in PDF format.
This is working fine.
What we want to do now is take the associated PDFs for the attendee and attach them to a single email.
The reports are assigned the studentid as the identifier + what type of certificate it is.
Example: 10101_ak.pdf for a state certificate, 10101_cfp.pdf for an advanced certificate.
Some attendees may have 1 certificate, some may have more - depending on how many certifications they hold.

We have been able to send each certificate in an individual email. Not a good solution as the attendee may receive 5 emails - each with one pdf.
We are at a point now that the loop attachs the first pdf, then gets the first and second pdf, then gets the first, second and third pdf. The attendee would receive the number of emails that there are certificates, just like sending one email for each certificate, but the last email would have all the pdfs.

Since the uniqueness of the file is the studentid we are hoping someone can point us in the direction of being able to search the exportPDF folder and attach all pdfs for the attendee to one email. We are using the studentid as a variable and declaring it.

Again, understand that we have read the forums, tried many of the solutions suggested but have not been able to get ALL the pdfs for the attendee to attach to ONE email.

Thanks for you help in advance.

MrHans · Jun 27, 2017

Hello I don't have a complete code ready, but as a start you can take a look at this function from Daniel Pinault from the DevHut:
https://www.devhut.net/2012/07/13/vba-list-of-files-in-a-specified-folder-or-directory/

It allows you to loop through your PDF dir.
You then add a line that searches your student ID.

Something like:
If left(strfilename, 5) = intStudentID then
'add your outlook attachment add statement here
End If

Hope it helps to get you going.

Uncle Gizmo · Jun 27, 2017

I think you need some oversight of the process.

There are various reasons why a PDF might not get generated or might get accidentally deleted and you wouldn't know.

Therefore when you create the PDF and save it to the folder it would be a good idea to store the UNC along with the PDF name in a table. This would then give you total control over the process.

You could use the UNC and filename as the instructions for your VBA code to attach the files to the email.

You would know if any were missing, you would also be able to record how many times a PDF has been emailed.

Sent from my SM-G925F using Tapatalk

Cronk · Jun 27, 2017

It would be more productive if you posted what you have and then we can offer suggestions/solutions.

What I do in such a case, is to initialize Outlook automation and in a loop moving through the recordset of email recipients, generate the particular pdf, create the email, attach the pdf, send and move to the next recipient.

sxschech · Jun 28, 2017

Here is a snippet of code I use for adding multiple attachments to an email. This is only the part that is doing the attachment, assumes that you already have the code set up to create the email.

This particular code is based on looping through a listbox of a form as we are sending out emails based on dates and what stage the information is at in the work flow. This also allows user to select specific items or all the items. It then finds all the pdf files associated with the selection and attaches them into one email. (Actually, it does this by looping through the items in the list box, so it is not gathering all the pdfs at once. Each item in the list box is used to locate the corresponding file) Afterward, it updates a status table indicating the email was sent and the workflow is completed for that selection so that additional emails of the same data are not sent to the same person or previously sent pdfs are not resent. That way if the person has new items in the future, it will send out the new items rather than all the items both sent and unsent.

Since I am using the same form for saving the pdfs and sending emails, I use the textbox txtPrinted for status updates to indicate how many items are completed, so in the context of this code, where you see txtPrinted it is not indicating that something has printed, rather that it is indicating that the email was created and items have been attached. If you are pulling data from a table/query, then you would change the listbox code to recordset syntax.

Code:

'Code to create email with multiple attachment
'https://social.msdn.microsoft.com/Forums/en-US/d3b0c997-fae8-4fc8-adce-47a5e7b2a667/sending-email-and-attachment-in-access-2010-to-outlook?forum=isvvba
'20160818

For Each varItem In lstEvents.ItemsSelected
                If lstEvents.Column(4, varItem) = rs!PrimaryEmail Then
                    stTable = lstEvents.Column(1, varItem)
                    stExportPath = Me.txtFileLocation
                    stExportFileName = Replace(stExportPath & "\" & lstEvents.Column(1, varItem) & "_" & Format(lstEvents.Column(3, varItem), "yyyymmdd") & ".pdf"
                    If Dir(stExportFileName) <> "" Then
                        .Attachments.Add (stExportFileName)
                        Me.txtPrinted = Me.txtPrinted + 1
                    Else
                        stAlreadyCreated = stAlreadyCreated & ", " & lstEvents.Column(1, varItem)
                        If Me.txtPrinted > 0 Then
                            Me.txtPrinted = Me.txtPrinted - 1
                        End If
                    End If
                    'Mark event as sent using system date
                    stsql = "UPDATE tblSurveyExtract_CourseDetail " & _
                            "SET SentInstructor = Now() " & _
                            "WHERE EventCode = '" & stTable & "' " & _
                            "AND SentInstructor Is Null"
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL stsql
                    DoCmd.SetWarnings True
                    'Change status from P-PDF Created to C-Completed
                    'in tblSurveyStatus
                    stsql = "UPDATE tblSurveyStatus " & _
                            "SET Status = 'C', " & _
                            "ProcessedDate=Now() " & _
                            "WHERE EventCode = '" & stTable & "' " & _
                            "AND Status = 'P'"
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL stsql
                    DoCmd.SetWarnings True

                End If
          Next varItem

VictorAtWork · Jun 29, 2017

Thank you for the snippet.
We will try implementing it next week and will let everyone know the results.

Outlook Automation (1 Viewer)

VictorAtWork

Registered User.

MrHans

Registered User

Uncle Gizmo

Nifty Access Guy

Cronk

Registered User.

sxschech

Registered User.

VictorAtWork

Registered User.

Similar threads

Users who are viewing this thread