Results 1 to 5 of 5

Access 2010 Send Outlook Email from Button Click - Failing to send email unless Outlook is open.

  1. #1
    Stephen is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    7

    Access 2010 Send Outlook Email from Button Click - Failing to send email unless Outlook is open.

    Hi all,


    I have developed an Access 2010 (32 bit) database to send automatic emails (in the background) once a button is clicked. The code below works perfectly when using the database on Windows 7 (32 bit). However, it does not work on Windows Server 2008 R2 Standard Service Pack 1 (64 bit)? The point of failure is at .Recipients.Add "My.Email@Somewhere" in the code below.


    1) I thought that it had something to do with Outlook opening too quickly so I put a temporary delay within the code, unfortunately that did not resolve the issue.
    2) I have temporary selected "Enable all macros (not recommended, potentially dangerous code can run)" within Outlook, unfortunately that did not resolve the issue.
    3) I have temporary selected "Never warn me about suspicious activity (not recommended)" within Outlook, unfortunately that did not resolve the issue.


    Option Compare Database
    Private Sub Submit_Click()


    Dim time1, time2


    time1 = Now
    time2 = Now + TimeValue("00:00:10")

    Dim fileName As String
    fileName = Application.CurrentProject.Path & "\Safeguarding_Referral" & ".pdf"
    DoCmd.OutputTo acOutputReport, "reportReferrals_Norfolk_New", acFormatPDF, fileName, False, "", , acExportQualityPrint

    Dim oApp As Object
    Set oApp = CreateObject("Outlook.Application")

    Dim oEmail As Object
    Set oEmail = oApp.CreateItem(olMailItem)

    With oEmail

    Do Until time1 >= time2
    time1 = Now()
    Loop

    .Recipients.Add "My.Email@Somewhere"
    .Subject = "My Subject"
    .Body = "My Body"
    .Importance = 2
    .Sensitivity = 3
    .Attachments.Add fileName
    .Send
    End With

    Set oApp = Nothing
    Set oMail = Nothing

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "02c - Referral_Norfolk_New_Remove_Update_Flag", acViewNormal, acEdit
    DoCmd.SetWarnings True
    DoCmd.Close acReport, "reportReferrals_Norfolk_New"
    DoCmd.Close acForm, "Referrals_Norfolk_New"
    DoCmd.Close acForm, "Referrals_Norfolk"
    MsgBox "A secure email has beeen sent to the appropriate teams", vbInformation, "Email Status"
    DoCmd.OpenForm "Opening Screen", acNormal, "", "", , acNormal

    If Len(Dir(fileName)) > 0 Then
    Kill fileName

    End If


    End Sub


    If you require any other information please let me know.


    Your help, time and guidance will be appreciated.


    Thanks Steve

  2. #2
    Minty is offline Hacker
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    869
    I have found most server software doesn't like Outlook being opened "quietly" do to spamming protection.

    Have a read here to see if it assists you: https://support.microsoft.com/en-gb/...tion-of-office
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,424
    First, try .Recipients.Add("My.Email@Somewhere")
    Second, code tags would make your code much easier to read (# on menu bar), as would grouping your declaration blocks. The way you have it, I have to continually scan the entire procedure to see if the parent of a child object (such as a mail item) has been properly declared/defined - and it could be anywhere. Your posted code might end up looking like
    Code:
    Option Compare Database
    OPTION EXPLICIT 'should be turned on. VB editor, Tools>Options>Require Variable Declaration
    
    Private Sub Submit_Click()
    On Error GoTo errHandler 'you should have this when creating objects as code can terminate after error.
    'leaving you with unrecovered pc memory allocation as well as database bloat
    
    'Dim time1, time2 ** should not be needed as you point out
    
    Dim fileName As String
    Dim oApp As Object
    Dim oEmail As Object
    
    Set oApp = CreateObject("Outlook.Application")
    Set oEmail = oApp.CreateItem(olMailItem)
    
    fileName = Application.CurrentProject.Path & "\Safeguarding_Referral" & ".pdf"
    DoCmd.OutputTo acOutputReport, "reportReferrals_Norfolk_New", acFormatPDF, fileName, False, "", , acExportQualityPrint
    'so if the file cannot be found, error occurs, code stops, Nothings don't execute as previously mentioned
    
    With oEmail
      .Recipients.Add("My.Email@Somewhere")
      .Subject = "My Subject"
      .Body = "My Body"
      .Importance = 2
      .Sensitivity = 3
      .Attachments.Add(fileName)
      .Send
    End With
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "02c - Referral_Norfolk_New_Remove_Update_Flag", acViewNormal, acEdit
    'DoCmd.SetWarnings True 'move this and see note **
    
    DoCmd.Close acReport, "reportReferrals_Norfolk_New"
    DoCmd.Close acForm, "Referrals_Norfolk_New"
    DoCmd.Close acForm, "Referrals_Norfolk"
    MsgBox "A secure email has beeen sent to the appropriate teams", vbInformation, "Email Status"
    DoCmd.OpenForm "Opening Screen", acNormal, "", "", , acNormal
    
    If Len(Dir(fileName)) > 0 Then Kill fileName 'simple, needs only 1 line thus no End If
    
    exitHere:
    Set oApp = Nothing
    Set oMail = Nothing
    DoCmd SetWarnings True '**if turned off & query fails, warnings can remain off. 
    'Would put it here only, but suggest you research .Execute method of DoCmd object instead
    
    Exit Sub
    
    errHandler:
    
    Select Case err.Number
      Case 31655 'Outlook cannot be started' 'Note the explanation for the error number, which you would leave in.
       Msgbox "Outlook failed to start. Please contact the database adminstrator."
    
      Case Else
       Msgbox "Error " & err.Number & ": " & err.Description
    
    End Select
    Resume exitHere
    
    End Sub
    Easy to make mistakes when copying/pasting around so no guarantees. Plus much of the comments I entered would not be required in the final product. There is only one possible error (31655) shown for example but there are several possibilities related to Outlook, plus the failure of any query to run, path to not be found, etc., otherwise could be simpler as
    errHandler:
    Msgbox "Error " & err.Number & ": " & err.Description
    Resume exitHere

    EDIT: forgot to mention that it's customary to create and SET a recipients object, but I don't see why your way wouldn't work if you had the parentheses unless the reason is security constraints. Given that you state the error occurs on the recipients line, I don't see that being the case, but watch out for the second set that I added.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start a sentence with, like, "so"?

  4. #4
    Stephen is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    7
    Thank you Minty for your direction to server side automation (read up last night) and comments about spamming.

    Thank you Micron for your detailed explanation of the code I posted and suggestions, it will be valuable in my rather steep learning curve of VBA coding.

    I have been able to open and close Outlook "quietly" although it now fails with a run-time error, not too sure what this means? Click image for larger version. 

Name:	Capture.JPG 
Views:	7 
Size:	16.2 KB 
ID:	34708

    I intend on doing some more investigation and apply the suggestions above, error handling, declaration blocks and code tags. I obviously need to learn a lot more and am very grateful for both of your time .

    Thanks again.
    Steve

  5. #5
    Stephen is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    7
    All issues resolved, special thanks to Minty and Micron .

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

Similar Threads

  1. Send Email via Outlook from an Access form
    By ramirezx@ddmfg.com in forum Programming
    Replies: 3
    Last Post: 07-11-2018, 10:22 AM
  2. Replies: 4
    Last Post: 02-19-2016, 02:13 PM
  3. Replies: 5
    Last Post: 09-14-2015, 06:24 AM
  4. Command Button to Send Outlook Email
    By AJM229 in forum Forms
    Replies: 19
    Last Post: 05-07-2014, 08:05 AM
  5. Send email from Access thru Outlook
    By ZMAN in forum Forms
    Replies: 2
    Last Post: 11-27-2010, 05:10 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
  •  
Tech Forums: Microsoft Office Forums