Results 1 to 3 of 3
  1. #1
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067

    Automated Email Dispersion

    I have a database that is run by Task Scheduler at 6:15 every Friday morning. The autoexec macro in Access 2010 runs 2 queries and exports the data to 2 Excel files. Then the following code is run to email those 2 files to 4 users. I've used this method in several other databases without an issue. Now I get a prompt for each user to allow the email to be sent. I didn't get this in the other databases I used this method in.


    Anybody know why I'm getting those prompts now? They're the same ones you get if you use the docmd.sendmail



    Code:
    Public Sub Masteremail()
        Dim objOutlook As Outlook.Application
        Dim objOutlookMsg As Outlook.MailItem
        Dim objOoutlookRecip As Outlook.Recipient
        Dim objOutlookAttach As Outlook.Attachment
        Dim sfile As String
        Dim filetocopy As String
        Dim fs As Object
        Set fs = CreateObject("scripting.filesystemobject")
        Set objOutlook = CreateObject("Outlook.Application")
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        'Add list of recipients here
        objOutlookMsg.Recipients.Add ("someone1@somewhere.net")
        objOutlookMsg.Recipients.Add ("someone2@somewhere.net")
        objOutlookMsg.Recipients.Add ("someone3@somewhere.net")
        objOutlookMsg.Recipients.Add ("someone4@somewhere.net")
        With objOutlookMsg
            .Subject = "Claim Audits Weekly Report"
            .Body = ""
        End With
        
        sfile = "\\192.168.114.17\HVVHomeFolders\rmilhon\Development\Claims Audit Report\Claim_Audits_Facility.xlsx"
        objOutlookMsg.Attachments.Add (sfile)
        
        'sfile = "\\192.168.114.17\HVVHomeFolders\rmilhon\Development\Claims Audit Report\Claim_Audits_Facility" & "_" & Format(date(),"mmddyyyy") & ".xlsx"
        Name sfile As "\\192.168.114.17\HVVHomeFolders\rmilhon\Development\Claims Audit Report\Claim_Audits_Facility" & "_" & Format(Date, "mmddyyyy") & ".xlsx"
        sfile = "\\192.168.114.17\HVVHomeFolders\rmilhon\Development\Claims Audit Report\Claim_Audits_Professional.xlsx"
        objOutlookMsg.Attachments.Add (sfile)
        'sfile = "\\192.168.114.17\HVVHomeFolders\rmilhon\Development\Claims Audit Report\Claim_Audits_Professional" & "_" & Format(date(),"mmddyyyy") & ".xlsx"
        Name sfile As "\\192.168.114.17\HVVHomeFolders\rmilhon\Development\Claims Audit Report\Claim_Audits_Professional" & "_" & Format(Date, "mmddyyyy") & ".xlsx"
        'Repeat the above 2 lines for each file to add
        'Send the email
        objOutlookMsg.Send
        'Clear out the objects
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing
    
    
    End Sub

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think you get the security prompt because you are attempting to manipulate the recipients collection which triggers the warning. Have you tried to concatenate your 4 users (emails) using ";" as a separator and add it to the BCC line (and remove the 4 recipients.add lines):

    dim strRecipients as string

    strRecipients=strUser1 &";" & strUser2 & ";" & strUser3 & ";" & strUser4

    objOutlookMsg.BCC=strRecipients

    Cheers,
    Vlad

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can also just end 1 email at a time. The code doesnt care how many repetitions it has to do.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Automated email for reports
    By caniread in forum Reports
    Replies: 4
    Last Post: 08-02-2016, 09:39 PM
  2. Automated Email Question
    By siggybaby_18 in forum Access
    Replies: 16
    Last Post: 12-02-2013, 08:48 AM
  3. Automated Email using Outlook
    By imran688 in forum Programming
    Replies: 25
    Last Post: 11-12-2012, 03:02 AM
  4. Automated Email Notification
    By sai_rlaf in forum Access
    Replies: 5
    Last Post: 12-07-2011, 06:33 PM
  5. Automated email from access
    By jfuller in forum Access
    Replies: 3
    Last Post: 04-11-2011, 02:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums