我已经基本解决了这个问题,只是在尝试获取同一封电子邮件的多个不同报告时遇到了问题。
我有一个表单,用户可以在其中选择买家将出现在哪个列表中。有 7 个可能的列表,并且它们可以在任意数量的列表中,因此我更愿意不必写出所有单独的案例。任何帮助将不胜感激!我在测试中将每个复选框命名为各自的列表名称 - 它们是 Active、Cabinet、Distribution、MH、OEM、RV 和 Salvage。
Private Sub Btn_Generate_Email_Click()
Dim OLApp As Outlook.Application
Dim OLMsg As Outlook.MailItem
Dim TodayDate As String
Dim Path As String
Dim ActiveReportName As String
Dim CabinetReportName As String
Dim DistributionReportName As String
Dim MHReportName As String
Dim OEMReportName As String
Dim RVReportName As String
Dim SalvageReportName As String
Dim ReportName As String
Dim Control As Control
Dim ControlName As String
Set OLApp = CreateObject("Outlook.Application")
Set OLMsg = OLApp.CreateItem(olMailItem)
TodayDate = Format(Date, "MM") & "-" & Format(Date, "DD") & "-" & Format(Date, "YYYY") 'Formats Today's date in MM-DD-YYYY
Path = CurrentProject.Path & "\" & "Access PDFs" 'Finds current AccessDB path and grabs the folder Access PDFs
ActiveReportName = Path & "\" & "Active List - " & TodayDate & ".pdf"
CabinetReportName = Path & "\" & "Cabinet List - " & TodayDate & ".pdf"
DistributionReportName = Path & "\" & "Distribution List - " & TodayDate & ".pdf"
MHReportName = Path & "\" & "MH List - " & TodayDate & ".pdf"
OEMReportName = Path & "\" & "OEM List - " & TodayDate & ".pdf"
RVReportName = Path & "\" & "RV List - " & TodayDate & ".pdf"
SalvageReportName = Path & "\" & "Salvage List - " & TodayDate & ".pdf"
ReportName = "ReportName"
'Create all necessary reports and outputs them to folder Access PDFs inside AccessDB Path
DoCmd.OutputTo acOutputReport, "Rpt_ActiveOpenQuantity", acFormatPDF, ActiveReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_CabinetOpenQuantity", acFormatPDF, CabinetReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_DistributionOpenQuantity", acFormatPDF, DistributionReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_MHOpenQuantity", acFormatPDF, MHReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_OEMOpenQuantity", acFormatPDF, OEMReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_RVOpenQuantity", acFormatPDF, RVReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_SalvageOpenQuantity", acFormatPDF, SalvageReportName, False
With OLMsg
.Display
.To = Forms!Frm_BuyerList!Buyer_Email
.Subject = "This is the subject of the email."
.Body = "This is the body of the email."
For Each Control In Me.Form.Controls
If Control.ControlType = acCheckBox Then
If Control = -1 Then
.Attachments.Add Control.Name & ReportName
End If
End If
Next Control
End With
Set OLMsg = Nothing
Set OLApp = Nothing
For Each 控件的底部是我不断出错的地方。我曾尝试动态引用我自己的对象哈哈,然后意识到control.name和reportname作为字符串返回,所以这种方式肯定行不通。我过去曾遇到过这样的情况,它添加了相同的报告 7 次,但我认为我陷入了第一个 If 语句返回 true 的困境,然后它额外触发了相同的行 6 次。
编辑 这是更改一些内容后更新的代码库。如果将来其他人遇到同样的问题,下面将是更新的代码块。
Dim OLApp As Outlook.Application
Dim OLMsg As Outlook.MailItem
Dim TodayDate As String
Dim Path As String
Dim ActiveReportName As String
Dim CabinetReportName As String
Dim DistributionReportName As String
Dim MHReportName As String
Dim OEMReportName As String
Dim RVReportName As String
Dim SalvageReportName As String
Dim ReportPath As String
Dim Ctrl As Control
Set OLApp = CreateObject("Outlook.Application")
Set OLMsg = OLApp.CreateItem(olMailItem)
TodayDate = Format(Date, "MM-DD-YYYY") 'Formats Today's date in MM-DD-YYYY
Path = CurrentProject.Path & "\Access PDFs" 'Finds current AccessDB path and grabs the folder Access PDFs - later add logical if to create folder if it doesnt exist
ActiveReportName = Path & "\Active List - " & TodayDate & ".pdf"
CabinetReportName = Path & "\Cabinet List - " & TodayDate & ".pdf"
DistributionReportName = Path & "\Distribution List - " & TodayDate & ".pdf"
MHReportName = Path & "\MH List - " & TodayDate & ".pdf"
OEMReportName = Path & "\OEM List - " & TodayDate & ".pdf"
RVReportName = Path & "\RV List - " & TodayDate & ".pdf"
SalvageReportName = Path & "\Salvage List - " & TodayDate & ".pdf"
'Create all necessary reports and outputs them to folder Access PDFs inside AccessDB Path
DoCmd.OutputTo acOutputReport, "Rpt_ActiveOpenQuantity", acFormatPDF, ActiveReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_CabinetOpenQuantity", acFormatPDF, CabinetReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_DistributionOpenQuantity", acFormatPDF, DistributionReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_MHOpenQuantity", acFormatPDF, MHReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_OEMOpenQuantity", acFormatPDF, OEMReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_RVOpenQuantity", acFormatPDF, RVReportName, False
DoCmd.OutputTo acOutputReport, "Rpt_SalvageOpenQuantity", acFormatPDF, SalvageReportName, False
With OLMsg
.Display
.To = Forms!Frm_BuyerList!Buyer_Email
.Subject = "This is the subject of the email."
.Body = "This is the body of the email."
For Each Ctrl In Me.Form.Controls
If Ctrl.ControlType = acCheckBox Then
If Ctrl = -1 Then
ReportPath = CurrentProject.Path & "\Access PDFs\" & _
Ctrl.Name & " List - " & Format(Date, "MM-DD-YYYY") & ".pdf"
.Attachments.Add ReportPath
End If
End If
Next Ctrl
End With
Set OLMsg = Nothing
Set OLApp = Nothing