I have a report that is based upon a query. The query draws its data from several tables.
A command button on the report generates an email which has X quantity of attachments.
The attachments are a function of the value of one field. The code I used successfully is:
Dim O As Outlook.Application
Dim M As Outlook.MailItem
Dim Brochure As String
Select Case Group
Case "UniSet"
Brochure = "C:\Users\micha\Acmeprod\classeur_detecteurs-gaz - Documents\Sales\Acme Brochure Template\UniSet\UniSet 190122-1334.pdf"
Case "TwinSet"
Brochure = "C:\Users\micha\Acmeprod\classeur_detecteurs-gaz - Documents\Sales\Acme Brochure Template\TwinSet\TwinSet 040423-0915.pdf"
Case "CEL-4"
Brochure = "C:\Users\micha\Acmeprod\classeur_detecteurs-gaz - Documents\Sales\Acme Brochure Template\CEL QuadSet\CEL-4 V30801.pdf"
Case "CEL-32"
Brochure = "C:\Users\micha\Acmeprod\classeur_detecteurs-gaz - Documents\Sales\Acme Brochure Template\CEL MultiSet\CEL-CEW MultiSet 271023-1014.pdf"
Case "Strobe/Horn"
Brochure = "C:\Users\micha\Acmeprod\classeur_detecteurs-gaz - Documents\Sales\Acme Brochure Template\Strobe Horn\S-H Series Strobe Horn 300623-0626.pdf"
Case "Pioneer Solo"
Brochure = "C:\Users\micha\Acmeprod\classeur_detecteurs-gaz - Documents\Sales\Acme Brochure Template\Pioneer\Solo\Pioneer Solo v230831-0931.pdf"
Case "Pioneer Duo"
Brochure = "C:\Users\micha\Acmeprod\classeur_detecteurs-gaz - Documents\Sales\Acme Brochure Template\Pioneer\Duo\Pioneer Duo V230831-0925.pdf"
Case "CEL-LS"
Brochure = "C:\Users\micha\Acmeprod\classeur_detecteurs-gaz - Documents\Sales\Acme Brochure Template\CEL-LS MegaSet\CEL-LS MegaSet V1.23.07.05.pdf"
Case Else
Brochure = "C:\Users\micha\Acmeprod\classeur_detecteurs-gaz - Documents\Sales\Price Book\2024 Acme Price list Ver 1.pdf"
End Select
Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)
todayDate = Format(Date, "DDmmYY")
todaytime = Format(Time, "HHMM")
If EmployeeID = 1 Then Extension = "MMK" Else Extension = "GT"
OrderEntryProcedure = "C:\Users\micha\Acmeprod\classeur_detecteurs-gaz - Documents\Sales\Quotes\Order Entry Procedure.pdf"
FileName = Application.CurrentProject.Path & "\Quotes" & "\Q" & OrderID & Extension & " " & todayDate & "-" & todaytime & " " & ProjectName & " " & CompanyName & " (" & LastName & ")" & ".pdf"
DoCmd.OutputTo acReport, "Quotation", acFormatPDF, FileName, False
With M
.BodyFormat = olFormatHTML
.HTMLBody = Msg
.To = EmailName
.Subject = "Quotation " & OrderID & " for " & ProjectName & " Product ID: " & ProductID & " " & " Model " & Group & "" & concatenatedValues & ""
.Attachments.Add FileName
.Attachments.Add OrderEntryProcedure
.Attachments.Add Brochure
.Display
End With
Set M = Nothing
Set O = Nothing
End Sub
This code works great for the first record of the field Group, but it does not show the values for subsequent records.
My questions are:
How do I get the values of not only the first record but all subsequent records for field Group
How do I then run the Select Case command on all of the values of Group? Does it involve creating a string that counts the records and then having a loop run the Case Select?
Dim attachments() As String
attachments = Split("C:\File1.pdf,C:\File2.docx,C:\File3.xlsx", ",")
For i = LBound(attachments) To UBound(attachments)
If attachments(i) <> "" Then
On Error Resume Next
.Attachments.Add Trim(attachments(i)), 0
If Err.Number <> 0 Then
End If
On Error GoTo 0
End If
Next i
