Outlook vba connected to Access Error-help

[beginneraccess]

In my last question (https://bytes.com/topic/access/answers/972172-split-ms-access-reports-into-separate-emails) I wanted to make separated reports from one big report and have them sent matching the EmployeeIDs and Emails... However after every email I get a message from Microsoft Outlook:
"A program is trying to send an e-mail message on your behalf. If this is unexpected, click Deny and verify your antivirus is up-to-date...."
so someone suggested to try coding VBA through Outlook and suggested:

Option Explicit
 
Sub test()
Dim rsAccountNumber As DAO.Recordset
Dim olApp As Outlook.Application, olEmail As Outlook.MailItem
Dim fileName As String, todayDate As String, strEmail As String
 
todayDate = Format(Date, "YYYY-MM-DD")
 
Set rsAccountNumber = CurrentDb.OpenRecordset("SELECT DISTINCT EmployeeID, [email] FROM [queAutoUpdate]", dbOpenSnapshot)
Set olApp = New Outlook.Application
 
With rsAccountNumber
     Do Until .EOF
         ' SETTING FILE NAME TO SAME PATH AS DATABASE (ADJUST AS NEEDED)
         fileName = Application.CurrentProject.Path & "\Desktop\trial.accdb" & !EmployeeID & "_" & todayDate & ".pdf"
 
         ' OPEN AND EXPORT PDF TO FILE
         DoCmd.OpenReport "test", acViewPreview, "EmployeeID = '" & !EmployeeID & "'"
         ' INTENTIONALLY LEAVE REPORT NAME BLANK FOR ABOVE FILTERED REPORT
         DoCmd.OutputTo acReport, , acFormatPDF, fileName, False
         DoCmd.Close acReport, "test"
 
         ' CREATE EMAIL OBJECT
         strEmail = ![email]
         Set olEmail = olApp.CreateItem(olMailItem)
         With olEmail
             .Recipients.Add strEmail
             .Subject = "Updated Balance"
             .Body = "Text Here"
             .Attachments.Add fileName        ' ATTACH PDF REPORT
             .Send                               ' SEND WITHOUT DISPLAY TO SCREEN
         End With
 
         Set olEmail = Nothing
         .MoveNext
     Loop
     .Close
End With
End Sub
 

however, I keep receiving "Compile error: Variable not defined" at different locations. This time I have it at

Set rsAccountNumber = CurrentDb.OpenRecordset

[twinnyfo]

The warning you are receiving (at the beginning of your post) is not an error but is based upon the network settings of your IT department. The only way you can send e-mails without this warning is to have your IT department change the security settings of your network. I can almost guarantee that this will NOT happen. The only way to avoid the warning is to have the e-mail pop up for editing and then click send on each individual e-mail. This may be a pain, but the simplicity of simply clicking send (as many times as necessary) is a much better solution than an unsecure network.

[beginneraccess]

Thanks twinnyfo!
I was hoping the code in Outlook would be a work around... Do you think the "Compile error: Variable not defined" in Outlook is due to IT as well?

[twinnyfo]

The second part is not an IT issue, but I did not research your code in depth. It "appears" to be correct.

However, you may want to check out this article on Sending e-mails via Outlook, which can simplify this process for you over the long haul. This eliminates many of the repetitive redundancies that may occur when you have a DB that sends a lot of e-mails (ours sends about a thousand every month).

Hope this hepps!

[beginneraccess]

Thanks twinnyfo!
I have looked into two issues of downloading ClickYes app or just doing a mailmerge :)