I have a report that is based upon a query. The query draws data from several tables.
The report has a button that:
- Creates and saves the report in pdf. The name of the pdf file is generated by a combination of fields from the report and a time stamp.
- Creates an email with text and attaches the saved report (in pdf).
- The sub routine also attaches documents to the email depending upon the value of one of the fields (ProductID).
The issue that I am having is that the If The Else line needs to “search” only the data that is from the current report. Right now it seems that it searches an entire table.
The VBA sub is as follows (I have highlighted in RED the line that is problematic) :
Private Sub Command84_Click()
Dim Msg As String
Msg = "Dear " & FirstName & ",<P> Please find attached the quote you requested." & _
"<p> The price total is your net, freight allowed." & _
"<p> The lead time for the quoted units is " & ProductionLeadTime & ". This quote might qualify for our Quick Turn Around program. " & _
"Call us for additional details." & _
"<p> For your convenience, I have also attached the brochures for the required units. " & _
"<p> Please note that at the time of order, " & _
"tracking information will automatically be emailed to this address: " & EmailName & ". " & _
"For more information concerning our order entry process, please review the attached Order Entry Procedure." & _
"<p> This quote is valid for 30 days." & _
"<p> Please do not hesitate to contact me if you should require additional information."
Dim O As Outlook.Application
Dim M As Outlook.MailItem
If ProductID = 3 Then Brochure = "C:\Users\micha\Documents\Acme\Acme Brochure Template\UniSet\UniSet 190122_1334.pdf" Else Brochure = Nul
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 & " "
.Attachments.Add FileName
.Attachments.Add OrderEntryProcedure
.Attachments.Add Brochure
.Display
End With
Any help would be greatly appreciated.
