By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,620 Members | 1,512 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,620 IT Pros & Developers. It's quick & easy.

Outlook vba connected to Access Error-help

P: 27
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:

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Sub test()
  4. Dim rsAccountNumber As DAO.Recordset
  5. Dim olApp As Outlook.Application, olEmail As Outlook.MailItem
  6. Dim fileName As String, todayDate As String, strEmail As String
  7.  
  8. todayDate = Format(Date, "YYYY-MM-DD")
  9.  
  10. Set rsAccountNumber = CurrentDb.OpenRecordset("SELECT DISTINCT EmployeeID, [email] FROM [queAutoUpdate]", dbOpenSnapshot)
  11. Set olApp = New Outlook.Application
  12.  
  13. With rsAccountNumber
  14.      Do Until .EOF
  15.          ' SETTING FILE NAME TO SAME PATH AS DATABASE (ADJUST AS NEEDED)
  16.          fileName = Application.CurrentProject.Path & "\Desktop\trial.accdb" & !EmployeeID & "_" & todayDate & ".pdf"
  17.  
  18.          ' OPEN AND EXPORT PDF TO FILE
  19.          DoCmd.OpenReport "test", acViewPreview, "EmployeeID = '" & !EmployeeID & "'"
  20.          ' INTENTIONALLY LEAVE REPORT NAME BLANK FOR ABOVE FILTERED REPORT
  21.          DoCmd.OutputTo acReport, , acFormatPDF, fileName, False
  22.          DoCmd.Close acReport, "test"
  23.  
  24.          ' CREATE EMAIL OBJECT
  25.          strEmail = ![email]
  26.          Set olEmail = olApp.CreateItem(olMailItem)
  27.          With olEmail
  28.              .Recipients.Add strEmail
  29.              .Subject = "Updated Balance"
  30.              .Body = "Text Here"
  31.              .Attachments.Add fileName        ' ATTACH PDF REPORT
  32.              .Send                               ' SEND WITHOUT DISPLAY TO SCREEN
  33.          End With
  34.  
  35.          Set olEmail = Nothing
  36.          .MoveNext
  37.      Loop
  38.      .Close
  39. End With
  40. End Sub
  41.  
however, I keep receiving "Compile error: Variable not defined" at different locations. This time I have it at
Expand|Select|Wrap|Line Numbers
  1. Set rsAccountNumber = CurrentDb.OpenRecordset
Feb 20 '19 #1
Share this Question
Share on Google+
4 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
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.
Feb 21 '19 #2

P: 27
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?
Feb 21 '19 #3

twinnyfo
Expert Mod 2.5K+
P: 3,284
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!
Feb 21 '19 #4

P: 27
Thanks twinnyfo!
I have looked into two issues of downloading ClickYes app or just doing a mailmerge :)
Feb 22 '19 #5

Post your reply

Sign in to post your reply or Sign up for a free account.