I need to change an email process we use within Access. We currently have internal customers fill out forms on Access databases sitting on a central server. When the user has completed their data entry, they notify us by a button with an embedded macro, for EMailDatabaseObject. A pre-populated email opens up for them with a PDF of a Report attached. The internal customer clicks the Send button, and we receive the email and PDF.

I need to switch this process from using the standard embedded macro to a VBA event procedure, utilizing CDO. We are not receiving emails because of individuals’ personal configurations, so we want them to use our smtp server instead.

I have set up the code below to send the email successfully. I am struggling to attach the PDF Report. I understand that with the CDO method, I can use .AddAttachment and attach a file from a specific location. I’ve tried to use DoCmd to create the report as PDF and then point to the Documents folder the report gets saved in, but this isn’t working. I’m guessing it may be because of security or the structure of the documents location URL.

Is there any way to use DoCmd to run the report, use DoCmd to select the report, and then have the CDO process attach the item that is selected?

Here is code I have now, using .AddAttachment with the URL below structured similarly to our actual location. When I run this code, an HTML attachment comes through on the email and opens to https://MYORG.sharepoint.com (not the rest of the URL).

Private Sub Command3_Click()
Dim Mail As CDO.message
Dim Config As CDO.Configuration
Set Mail = CreateObject("CDO.Message")
Set Config = CreateObject("CDO.Configuration")

Config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort
Config.Fields(cdoSMTPServer).Value = "smtp.MYORG.org"
Config.Fields(cdoSMTPServerPort).Value = 25
Config.Fields.Update
Const ForReading = 1

DoCmd.OutputTo acOutputReport, "rptMYREPORT", acFormatPDF, "REPORTNAME" & ".pdf"
DoCmd.SelectObject acReport, "rptMYREPORT", True

Set Mail.Configuration = Config

With Mail
.Subject = "Ready to Review"
.To = "[email protected]"
.From = Format(DLookup("Email", "tblSubmit"))
.CC = Format(DLookup("Email", "tblSubmit"))
.TextBody = "Entries are complete." & vbNewLine & vbNewLine & Format(DLookup("SubmissionNotes", "tblSubmit"))
Call .AddAttachment("https://MYORG.sharepoint.com/:f/r/personal/FIRSTNAME_LASTNAME_MYORG_org/Documents/Documents/REPORTNAME.pdf")
.Send
End With

Set Config = Nothing
Set Mail = Nothing

MsgBox ("Your entries have been submitted.")
End Sub