Solved

Emails won't send via MS Access VBA Code - Getting Error . . . Don't have permissions to perform this task

Posted on 2014-01-15
12
1,389 Views
Last Modified: 2014-01-22
I have Windows 7 and Office 2010, and have a MS Access Database that utilizes VBA Code that I've written that automatically creates a PDF file and attaches to email and sends; since I've upgraded from Windows XP and Office 2007, this does not work now.  It does not create an Email, there is nothing in the Sent, Outbox or DRAFT folders;

I was doing some troubleshooting and commented out the code that creates the attachment and attaches to email, and it then created the email and sent; so it has something to do with the attachment;  I've went into the Options/Trust Center/Automatic Download and deselected the option about "downloading pictures, etc.", but it still doesn't work.

I've also checked the files in the outlook folder on my hard drive to see if they are marked as "Read Only" and they're not.

Here is the code I'm using:
Public Function SendEmailEmpCompletedRptNoErrorsNoAction(ByVal varEmpEmail, varAddlEmail As String)
On Error GoTo Err_Handler
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim AttachmentPath As String
Dim DisplayMsg As String
Dim strAttach1 As String
Dim strAttachTemp As String
Dim strOutputToTemp As String
Dim strAttachCopy As String

DoCmd.Echo False
DoCmd.SetWarnings False

strOutputToTemp = "C:\Windows\Temp\Audit_Completed" & "_" & InquiryNum & ".PDF"

DoCmd.Echo False
DoCmd.SetWarnings False

'''Output to Temp Area (to C Drive)
DoCmd.OutputTo acOutputReport, "rptAudit_Emails_EMP_NoErrorsNoAction", acFormatPDF, strOutputToTemp, False

strOutputToTemp = "C:\Windows\Temp\Audit_Completed" & "_" & InquiryNum & ".PDF"

'Set Attachments (from Temp Dir on C Drive)
strAttachTemp = strOutputToTemp

' Copy to Network
strAttachCopy = "\\Wiw2pwpfle001\data\QA Database\Employee Audit Scorecard System\Audit_Completed\Audit_Completed" & "_" & InquiryNum & ".PDF"

' Variable/Path (to copy from C: Drive to Network Drive Folder for Completed Audits
FileCopy strAttachTemp, strAttachCopy

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

'Create the email message and send
'Set objMessage = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
    .To = varEmpEmail & "; " & "seniorqaebrteam@wp.com"
'    .CC = varAddlEmail
    ' Set the Subject, Body, and Importance of the message.
    .Subject = "Audit Completed With No Errors -- No Action Required as of " & Now() & ""
    .HTMLBody = "Attached you will find a copy of your Quality Audit Scorecard.   If you would like to challenge your audit, your response must be " & _
             "received within 2 business days of the receipt of this message. Challenges received after 2 business days will not be accepted." & _
             "<BR><BR>" & "All challenges must be completed using the proper challenge form found within the QA Audit Challenge Process (QLA02); challenges on the wrong form will not be accepted." & _
             "<BR><BR>" & "Please see your OE for assistance should you have questions on the challenge process.   Do not contact your auditor by phone to challenge an audit." & _
             "<BR><BR>" & "Remember that this audit is a way for us to help you achieve the goals and objectives set by management." & _
             "<BR><BR>" & "Sincerely," & "<BR><BR>" & "Your Programs Quality Audit Team"

    ' Add attachments to the message.
    If Not IsMissing(AttachmentPath) Then
        Set objOutlookAttach = .Attachments.Add(strAttachTemp)
    End If

    ' Resolve each Recipient's name.
    For Each objOutlookRecip In .Recipients
        objOutlookRecip.Resolve
    Next

'        .Save
        .Send

End With

Set objOutlook = Nothing

DoCmd.SetWarnings True
DoCmd.Echo True

''Remove attachments from Temp folder on C drive
Kill strAttachTemp
''Kill strAttach1
''Kill strAttach2
''Kill strAttach3
''Kill strAttach4
''Kill strAttach5

Exit_Handler:
    Exit Function

Err_Handler:
    DoCmd.CancelEvent
    MsgBox Err.Description
    Resume Exit_Handler
End Function

Open in new window



Any suggestions on how to get around this?

Thanks,
gdunn59
0
Comment
Question by:gdunn59
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39781667
Put a breakpoint on line 31, then check if strAttachTemp is valid.
0
 
LVL 84
ID: 39781920
Perhaps the PDF is not fully rendered when the code hits the Attach line in your Outlook code?

Insert this line just before that one:

If Dir(strAttachTemp) = "" Then
  Msgbox "Not Found"
End If

Also, I'd create the PDF and then run your email code, and THEN rename/copy/move that PDF. These operations can take time, and if something hiccups during the copy/move operation, your email code will fail. If you go ahead and send the email and THEN copy/move, you have at least accomplished the main goal of the process, and you can then troubleshoot what happened with the copy/move.
0
 
LVL 1

Author Comment

by:gdunn59
ID: 39782777
The same exact code that am I using now, I was using before the upgrade to Windows 7 and Office 2010, and it worked fine prior to these upgrades.  So I don't think the issue is that it is not creating the attachment.  It has something to do with Outlook not liking the email having an attachment.

Thanks,
gdunn59
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 1

Author Comment

by:gdunn59
ID: 39782783
As far as putting a breakpoint on line 31, I've already put a breakpoint at the beginning of the code and did F8 through the entire code, and as soon as it process line 67 (.SEND), then I get the error.

Thanks,
gdunn59
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39782812
As far as putting a breakpoint on line 31, I've already put a breakpoint at the beginning of the code and did F8 through the entire code, and as soon as it process line 67 (.SEND), then I get the error.
Did you verify that strAttachTemp is indeed a valid file name path after line 28?
0
 
LVL 1

Author Comment

by:gdunn59
ID: 39782933
MacroShadow:

Yes, strAttachTemp is valid.  As I mentioned this same code worked prior to upgrading to Windows 7 and Office 2010.

Any other suggestions?

Thanks,

gdunn59
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39783096
Perhaps it's a Windows 7 permission hiccup. Do all users including local Admins have the problem?

Try saving the temp file to a custom folder (not one of windows) and see if it makes a difference.
0
 
LVL 84
ID: 39783154
When you say "valid", do you mean it seems to be a valid path, or that you have confirmed that at the time of execution the File exists, and that you have permissions to use that path/directory?

Don't mean to harp on this, but it looks/feels/smells like a permissions issue, as MacroShadow pointed out earlier - especially since you wrote that this was fine prior to the upgrade.
0
 
LVL 1

Author Comment

by:gdunn59
ID: 39784108
I mean the code creates the PDF and places it in the appropriate location.  As I mentioned before, this all work prior to upgrading to Windows 7 and Office 2010.  Nothing has changed in the database/code.  So it has to be something related to the upgrades.

Thanks,
gdunn59
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39784843
That is what I suggested, did you try it?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39785191
I'm not sure your code is correct here:
   If Not IsMissing(AttachmentPath) Then
        Set objOutlookAttach = .Attachments.Add(strAttachTemp)
    End If
IsMissing is used to determine if a procedure argument is included, and it's not valid for String types (since those types don't include the necessary flags to define them as "missing").

Essentially, you're asking if AttachmentPath is included in the set of "arguments" included with your Procedure:

Public Function SendEmailEmpCompletedRptNoErrorsNoAction(ByVal varEmpEmail, varAddlEmail As String)

The only two Arguments you have included here are varEmpEmail and varAddlEmail, so the call to IsMissing will always be False.

However you mention that you've set a breakpoint:
did F8 through the entire code, and as soon as it process line 67 (.SEND), then I get the error.
When you did that and stepped through the code as MacroShadow suggested, did that code block execute correctly?

Regardless, the use of IsMissing is not correct and you should change it. If your intent is to confirm that strAttachTemp contains a valid path to an existing document, then just use Dir:

If Dir(strAttachTemp) <> "" Then
   .Attachments.Add(strAttachTemp)
End If

Regarding the fact that it worked before upgrading - we have found many instances where working but invalid code broke when upgrading to newer versions of Access. The OpenReport action was a prime example of that, and bit many, many people who had been using it incorrectly for years (myself included).
0
 
LVL 1

Author Closing Comment

by:gdunn59
ID: 39800638
Scott,

Sorry just now getting back to this, I had to go out of town last week.

Anyway, as you suggested, I changed the attachment code from:

 If Not IsMissing(AttachmentPath) Then
        Set objOutlookAttach = .Attachments.Add(strAttachTemp)
    End If


To:

If Dir(strAttachTemp) <> "" Then
   .Attachments.Add(strAttachTemp)
End If


Worked like a charm.  

Thanks much,

gdunn59
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you have clients or friends from around the world, it becomes a challenge to arrange a meeting or effectively manage your time. This is where Outlook's capability to show 2 time zones in one calendar comes in handy.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question